Might be asking the impossible...

[billythedj66]

Here is what I would like to do; While in a form (Form A), I want to create a macro for a button that when clicked, will close (Form A), and open (Form B) to enter data. Then when closed with another button, closes (Form B) then opens (Form A) at the original record. I am not sure if that makes any sense at all - if more information is needed please ask.

Bill

 

[Corticus]

"Is this because I have a space in the control source of the text box?"
Should not be. You are getting a compile error which means something is wrong in your code (though for future reference try not to name anything with spaces in it, fields, controls, whatever)

This line:
call lastRec(txtStudentID)
makes a reference to the name of the CONTROL holding the primary key (ie textbox), NOT the primary key of the form's bound table.

You'll need to post your code in order for me to determine why your getting a compile error. You can select Debug|Compile and Access will point you to your problem, though sometimes the description is not very helpful if your not familiar with error codes.

 

[billythedj66]

Here is my code:

Private Sub Command467_Click()

Call lastRec(JobID)

DoCmd.OpenForm "Contacts"
Forms("Contacts").SetFocus
DoCmd.Close acForm, "Project Form"


End Sub

The primary key is called "JobID" - the text box is called "Text642:"

I really do apologize if I am being idiotic.

Bill

 

[Corticus]

Call lastRec(JobID)
should be
Call lastRec(Text642)
Remember, this is a reference to the CONTROL holding the primary key, not the FIELD itself.

You also might want to work on naming your controls something more identifiable, those textxxx names will eventually get awfully confusing.

 

[billythedj66]

If this is getting too insane, please let me know, I can stop.

FYI: The original form has a tab control with three pages. If I click the button without going to the third tab page, which is the page where the combo box "Contacts" is located, it works fine. But if I go to the third page, or if I try to click button again I get the following error:

Run-tiime error '3008':

The table 'Contacts' is already opened exclusively by another user, or it is already open through the user interfaceand cannot be manipulated programmitcally.

I am the only person working this database. Any ideas?

 

[mdmilner]

Just to confuse everyone, here's a related topic. I can't remember where I found this code one day -- but I'm pretty sure I picked it off a website. I definately modified it, but couldn't swear how much.

Although I liked Corticus' approach, this will open a new form and then HIDE the old while writing the object reference to the first into the .TAG property of the 2nd form. When closing, it references that to close the 2nd form and unhide the first.

You will need to disable the close "X" and setup a custom button that calls these functions.

Public Function OpenHide(ByVal frmName As String)
Dim strHide As String
DoCmd.Echo False           ' Turns off Echo of new form

  strHide = Screen.ActiveForm.Name
  Screen.ActiveForm.Visible = False
  DoCmd.OpenForm frmName
  Screen.ActiveForm.Tag = strHide
  Screen.ActiveForm.Refresh

DoCmd.Echo True

End Function

Public Function CloseUnhide()
Dim strUnhide As String
DoCmd.Echo False

  If IsNull(Screen.ActiveForm.Tag) Then
    DoCmd.Close
  Else
    strUnhide = Screen.ActiveForm.Tag
    DoCmd.Close
    DoCmd.SelectObject acForm, strUnhide
    If Screen.ActiveForm.Name = "frmEntry" Then Forms!frmEntry.sfmContact.Requery
  End If

DoCmd.Echo True

End Function

 

[Corticus]

If this is getting too insane, please let me know, I can stop.

FYI: The original form has a tab control with three pages. If I click the button without going to the third tab page, which is the page where the combo box "Contacts" is located, it works fine. But if I go to the third page, or if I try to click button again I get the following error:

Run-tiime error '3008':

The table 'Contacts' is already opened exclusively by another user, or it is already open through the user interfaceand cannot be manipulated programmitcally.

I am the only person working this database. Any ideas?

Sounds like your trying to open a table that is locked because a form that is bound to it is open. I'm not sure how this relates to the original question, I recall your were trying to open a form, not a table.

What is the offending code... the code to open the second form?

 

[billythedj66]

My mistake!!! The second form Contacts had the Record Locks set to All Records.

Thank You so very much for all the help and your patience - you are truly a fantastic person.

 

[Corticus]

So... did you get everything figured out?

 

[Tanis]

Try putting [] brackets round Job ID

 

[SydneyGeek]

Bill,

if the other posts haven't already sorted you out, you can make Form B a pop-up so it comes to the front. Finish entering the data for the new contact, close the form, and you're back on the original record on Form A. You can set it so that when you close Form B, you requery the combo on Form A so the name is in the list.

Denis