Updating a combo box from another form

[Scoop]

I have an order entry application. On the main order form there is one
combo box bound to "BillToCustomerID" and another bound to
"ShipToCustomerID". There are also subforms linked to each for the
Billing and Shipping Addresses.

When a user selects either customer from the combo box, I am running an
AfterUpdate event to requery the Address subforms. This works great
but...

I also have a Search form that can be opened by a button next to each
combo box. It allows the user to search for a Customer. I would like
to pass the "CustomerID" back to the Order form based on the results of
the search. I have been trying to use code like the following:

Forms!frm_Order!BillToCustomerID =
Forms!frm_CustomerSearch!frm_CustomerSearchSUB!CustomerID

I have tried both updating the field and the control (combo box) bound
to the field and niether seems to trigger the AfterUpdate event that I
need to fill in the address sub form. The new Customer displays in the
combo box but it appears as if the field in the record has not been
updated because even when I manually requery the Address sub form, the
addresse(s) associated with the new Customer do not appear.

Any help is greatly appreciated!

 

[Graham R Seach]

See if the following approach works for you:
http://www.pacificdb.com.au/MVP/Code/MyDialog.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Scoop]

Thanks, Graham

I tried it and it certainly helps make my search form more reusable but
it still doesn't quite do what I need. When the control on the main
form is updated from the dialog, the AfterUpdate event doesn't seem to
be triggered as it is if you manually update the control. Do you know
of a way to trigger this event after passing the value back to the main
form?

Regards,

Scott

 

[Graham R Seach]

If the calling form declares the swearch form using the WithEvents keyword,
you can implement a public event (which is declared in the search form).
When fired (in the calling form), you can use this event to update the
control's value. You can also use this same event to update the subform(s).

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Scoop]

Do you have an example of how to do this?

I've got things working by repeating the code I have in the AfterUpdate
of the main form but I know it's not the best way to do it.

Thanks,

Scott

 

[Graham R Seach]

Examine the code in the website example.
...in the main form
Private Sub someobject_somevent(strReturnValue As String)
Me!somecontrol = strReturnValue
Me!subformcontrol.Form.RecordSource = strSQL
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia