Synchonising combos on different forms

[Weebleman]

Hi,
This is a Nooby question that's probably been answered before but I can't find reference to it using the search facility, so I apologize if this is a FAQ.

I have two forms (Customers and Contacts). On frmCustomer I have a button (btnManageContacts) which I want to open frmCustContacts and show the contact records for the particular customer.

Private Sub btnManageContacts_Click()
DoCmd.OpenForm "frmCustContacts", , , "CustID ='" & Me!CustID & "'"
End Sub

This works OK and the Contacts form contains only the filtered records.

The problem is that on the Contacts form I have a combo that I want to use to select a contact record, but the combo lists ALL the contacts and not just the filtered ones. I have spent all afternoon piddling around with SQL to bring the combo into line with no success. So I wonder if any of you can put me n the right track, or point me in the direction of some suitable tutorials.

I know this might be easier to execute using a sub-form but the reason for doing it this way is that the same Contacts form will be accessed from other areas.

Thanks

 

[vbaInet]

Is this combo box unbound?

 

[Weebleman]

I've messed about with bound/unbound, manual/wizard and to be honest I got a bit lost!

What I want to achieve is a combo on the Contacts form which displays the 'FirstName', 'LastName' fields for the contacts associated with the selected customer, which then allows a particular contact record to be selected.

The 'CustID' value is passed from the Customer form (PK) to the Contacts form (FK) when it is opened (see previous post). I'm now wondering if it would be easier not to filter the recordset on opening the Contacts form, but instead just rely on applying the correct SQL statement to the Row Source of the Contacts combo, by somehow/somewhere including the "WHERE something = CustID" condition.
Hope I'm using the correct terminology to make sense here

 

[vbaInet]

So looking at the CONTROL SOURCE property of the combo box, is it bound or unbound? If there's nothing in there, it is unbound, if there's a field name in there then it is bound.

 

[Weebleman]

So looking at the CONTROL SOURCE property of the combo box, is it bound or unbound? If there's nothing in there, it is unbound, if there's a field name in there then it is bound.

Hi,
I've almost got it working (more by luck than knowledge!), just one fairly minor problem to sort out.

The combo on the Contacts form (cboContactSelect) is UNBOUND. I've added the following SQL to the combo's ROW SOURCE:

------------------------------------
SELECT tblCustContacts.CustContactID, tblCustContacts.[Last Name], tblCustContacts.[First Name]
FROM tblCustContacts
WHERE (((tblCustContacts.CustID)=[Forms]![frmCustManage].[CustID]))
ORDER BY tblCustContacts.[Last Name], tblCustContacts.[First Name];
------------------------------------

This seems to work provided I still filter the records on the Contact form when I open it from the Contacts button on the Customers form -- i.e:

------------------------------------
Private Sub btnManageContacts_Click()
DoCmd.OpenForm "frmCustContacts", , , "CustID ='" & Me!CustID & "'"
End Sub
-------------------------------------

So, although it needs a bit of tidying up it's basically working.

PROBLEM:
The current problem is that I want to display LAST NAME | FIRST NAME fields in the contacts combo, and although both fields appear (within the width of the combo) when making the selection, only the LAST NAME appears in the combo once the selection has been made. I don't think it's a column width problem as I have both set to 4 cm, which is ample.
Any suggestions to overcome this would be much appreciated.

Thanks....

 

[vbaInet]

Only one value can be displayed with a combo box. Unless you want to look into using a List box.