Combo Lists

[Guest]

I have a form for contacts that uses a combo list to lookup up a table of
companies. If the company isn't in the list, the user double clicks on the
combo list to open another form so they can create a new company. When the
user closes the company form I want the new entry to be entered automatically
into the company combo list on the contacts form.

I open the new form like this;
DoCmd.OpenForm "frmCompaniesNew", , , , , acDialog, "GotoNew"

I just don't know how to save the new company ID from the company form into
the contacts form automatically.

Any help appreciated.
Glenn

 

[Sandra Daigle]

Hi Glenn,
You can either put code in the first form which pulls the value from the
second form before it closes or put the code in the second form to 'push'
the value to the first form (again) before closing.


Often I will go with the first method - first I open the second form in
'dialog' mode using the acDialog option of the WindowMode paremeter of
docmd.Openform. When used, the code in the first form will pause until the
second form (the one being opened) is either hidden or closed. With this in
mind, on the second form put an 'OK' button which the user clicks to
indicate that a selection has been made (you could do the same thing with
the double click event on the product control). The code behind the click
event of the OK button should hide the form (rather than close it). Once the
second form is hidden, the code in original form will resume and can use the
value in the control on the now hidden form to set the value of the combo.
Then the second form is closed by the first form. Here's some sample code:


Code on first Form:


Private Sub Command19_Click()
DoCmd.OpenForm "form2", , , , , acDialog
Me.companyid = Forms!form2.CompanyID
DoCmd.Close acForm, "form2"
End Sub


Code on second form:


Private Sub cmdOK_Click()
Me.Visible = False
End Sub