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

 

[Andrew Fergus]

Hi Bill.

Yes this is possible.

If you create a new macro, set the first action to Close (select form, form A & No in the action arguments in the bottom part of the screen), set the second action to OpenForm (Form B, leave the rest alone) and set the third action to stop macro. Save the macro.

Go back into the design of form A, create (or edit) a button and set the OnClick property of the button to the name of your new macro. Save the form and test it.

From there you should be able to work out what to do to get from Form B to Form A.

HTH, Andrew.

[This section was added later : I have just seen your bit about returning to where you were on form A, in that case, don't close form A in your macro, just open form B on top of form A. You might need to change the settings of the "pop up" and "modal" properties of form B to "yes" (you do this in the form design) to keep it on top of form A, but keep these same properties set to "no" for form A. Unfortunalately once you click the button the focus changes to that part of the form so you lose where you are in form A, unless you put the button into the detail section, rather than the header or footer. <- This is a bit more tricky than I thought and one of the VB experts here should be able to come up with a better solution.]

 

[Corticus]

Just for the sake of an example, we'll call the first form form1, and the second form form2

Right click on the button on the first form and enter this onclick event

docmd.openform "form2"
form2.setfocus
docmd.Close acForm, "form1"

Enter this code for the onclick event of the button on the second form:

docmd.openform "form1"
form1.setfocus
docmd.close acForm, "form2"

 

[Andrew Fergus]

Hi Corticus, I suggested something very similar but via a macro instead - how would the OP get back to the record they were on in the first form? There is a GoTo command that you can use but I'm not sure how to work out what record number had the focus before the button click.

Andrew

 

[billythedj66]

Let me elaborate on what I am trying to do, maybe I am going about this the wrong way. In my main form (Form A) there is a combo box taken from a different table, called Point of Contact. If the user wants to add a new name not already in the table, they can click a button "Add New Contact" that will go to the POC form (Form B) allow them to enter the data (name, phone number, etc.). After the user closes the second form, they can then find the name in the combo box. I thought I had it working, but I keep getting a Table Open By Another User error. Does this make sense? I think I am getting more confused as I go.

Bill

 

[Corticus]

Thanks for actually reading the OP for me andrew,

For this example I used frmStudent as form1 and frmTeacher as form2
This works:

First make a module, and insert code like this:

Global gbLastRec As Integer

Sub lastRec(intLastRec As Integer)

    gbLastRec = intLastRec

End Sub

Now, for form 1 use code like this:

Private Sub cmdForm2_Click()

    Call lastRec(txtStudentID)
        
    DoCmd.OpenForm "frmTeacher"
    Forms("frmTeacher").SetFocus
    DoCmd.Close acForm, "frmStudent"

End Sub

In this form, txtStudentID hold the primary key for the table to which the form is bound. The global variable gbLastRec will get assigned this ID value for later reference.

Now in form2, use code like this:

Private Sub cmdForm1_Click()

    DoCmd.OpenForm "frmStudent"
    Forms("frmStudent").SetFocus
    DoCmd.Close acForm, "frmTeacher"
    DoCmd.GoToRecord acDataForm, "frmStudent", acGoTo, gbLastRec

End Sub

So, when you click the button in form 2, it is closed, form 1 is opened, and then it goes to the record you saved as gbLastRec.

Edit:
The OP managed to squeeze in a post while I was writing this, I'm looking over it now. As is, I guess this reply is to andrew regarding how to go the last record you were on, on a form.

 

[Corticus]

billthedj,

There is actually a little better way to do what your trying to do. Bottom line is you want to be able to add something to a combo that is not in the list.

The best way to do that is to use the not_in_list event.

If you right click on the combo, and go to the code builder, you can select (from the top right list of events) the not_in_list event. This will fire whenever the user tries to type something into the combo that's not in the list.

At this point, you can use a little piece of dynamic SQL to add the value to the table to which this combo looks.

The basic idea is this:

Private Sub cmbContact_NotInList(NewData As String, Response As Integer)

    Select Case MsgBox("Add new contact?", vbYesNo)
        Case vbYes
            insSQL = "INSERT INTO tblContact( contact ) " & _
                     "VALUES ( """ & NewData & """ );"
            DoCmd.SetWarnings False
            DoCmd.RunSQL (insSQL)
            DoCmd.SetWarnings True
            Response = acDataErrAdded
        Case vbNo
            Me.Undo
            Response = acDataErrContinue
    End Select

End Sub

Your combo will have a different name, most likely. Also, in the SQL statement:
INSERT INTO tblContact( contact )
'contact' is the name of the field in your table to which you will be adding the new data you've just tried to type into the combo.

I think this is the best way to deal with adding items to a list as it doesn't involve any additional forms, and is very quick for the user.

 

[billythedj66]

Corticus & Andrew,

First of all, thanks to both of you for all the help so far. Now comes my issues. I want to use your previous solution since the form "Contacts" has more fields that need to be filled in other than just the name. I have a couple of questions to ask:

1. Where and how do I create the Global module? Does it go with the form?

2. Where you have "txtStudentID" should I change that to the primary key I have for Form 1? If so, my primary key is "Job ID" - when I changed that I received an error.

3. Finally, where you have "cmdForm2_Click()" should it be "Command467_Click()" which is the button I have on Form 1 to open Form 2?

I do hope I am not being too much of a pain. I am relatively an Access virgin.

Bill

 

[Corticus]

1. Where and how do I create the Global module? Does it go with the form?
Select Tools|Visual Basic Editor
Select Insert|Module
You now have a module, the declaration of the global variable and related code go here.

2. Where you have "txtStudentID" should I change that to the primary key I have for Form 1? If so, my primary key is "Job ID" - when I changed that I received an error.
txtStudentID is a reference to the control (a text box) that is bound to the primary key. You will need the same, some control that displays the primary from the table to which the form is bound.

3. Finally, where you have "cmdForm2_Click()" should it be "Command467_Click()" which is the button I have on Form 1 to open Form 2?
Yes.

 

[billythedj66]

I regards to Question No. 2: I am getting a Compile error: Syntax error. Is this because I have a space in the control source of the text box? My primary key is "Job ID".