Sync Main Form with Subform after Combo Selection

[Confused]

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
this:

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.

Damon

 

[tina]

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

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

tblCustomerContacts
ContactID (primary key)
CustomerID (foreign key from tblCustomers)
ContactFirstName
ContactLastName
<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
mainform.

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.

hth