Combo Boxes

Cari Cook
Cari Cook used Ask the Experts™
on
I have a form with a combo box that I select a name from.  Once I select the name, it automatically populates their address into the form.  This works until i use the form as a sub-form.  I can still choose a name from the combo box, but it will not populate the addresses for me. Any help is appreciated.  Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
How are you populating the form after you select the name from the combobox?

Author

Commented:
By using the combo box wizard and selecting the third optio that says to find a record based on the value I select in my combo box.
MIS Liason
Most Valuable Expert 2012
Commented:
^Then that is the issue.
The wizard code does not actually "populates their address", it simply moves the form to that record.
Thus the address is "visible", (Not really "Populated")
This code works on the premise that the form that the combobox is in, will control the records.

However as a subform, the "main" form controls the subform records.
For example, if you just had an Orders form, you could create a similar combobox to "find" an Order.
But, when using this same Orders form as a subform of the Customers form, you can only view the Orders associated with the selected Main form Customer.
Make sense?

If you want something like this you will have to understand that the combobox will be limited to the "associated" main form record. (you will only be able to select Orders for the selected Customer customer.)

First modify the combobox RowSource to take the current mainform record into consideration.
So instead of something like this:
SELECT tblOrders.OrderID FROM tblOrders
... it will end up being something like this:
SELECT tblOrders.OrderID, tblOrders.CustomerID FROM tblOrders
WHERE tblOrders.CustomerID=Forms!frmCustomersMain!CustomerID

Then you need to add code like this to the "MainForm" Current Event:
    [YourSubform].Form![YourComboBox].Requery
(to requery the combobox.)

Finally, use code like this on the "SubForm" current event:
    Me.Combo14 = Me.OrderID
(to synchronize the combobox to the current OrderID)

Here is a sample.

I am sure you will be able to modify this to work in your database.
;-)

JeffCoachman
AccessEEQ24915666CustomersOrders.mdb

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial