Sync Main Form with Subform after Combo Selection



Form/subform = Customers/Contacts.

On the Contacts Subform I put a combo box to search the contacts by name.
When I select the name from the combo box, the main form never changes to the
Customer Name that is associated with the contact selected.

How can I sync the main form (customers) after this?

Damon Heron

I would put the combobox on the main form. If you have a client table with
a FK of CustomerID, then the source of the combobox should be similar to

SELECT Table1.CustomerID, Table1.ContactName
FROM Table1
ORDER BY Table1.ContactName;

In the Afterupdate event of the combo,

Private Sub YourComboName_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & str(Nz(Me![YourComboName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The main form will go to the correct customer.



sounds like you're going at this kind of backward. normally, i'd expect that
you'd have a table of customers, linked to a child table listing all of the
contacts for each customer, as

CustomerID (primary key)
CustomerName (assuming the customer is a company, not a person)
<other fields that describe a customer>

ContactID (primary key)
CustomerID (foreign key from tblCustomers)
<other fields that describe a contact>

so one customer record may have many contact records (linked by the key
field CustomerID), but each contact record is linked to only one customer
record; a standard one-to-many relationship.

if that's how your tables are set up, then the standard forms setup would be
mainform bound to tblCustomers, and subform bound to tblCustomerContacts. as
you move from one customer record to another in the mainform, the subform
will display all the contacts for the currently displayed customer in the

so the above is a standard setup. if your tables/relationships setup is
different, please describe it, and what you're trying to accomplish - not
*how* you're trying to do it.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question