0

On a form called opportunity I have a combobox called cmbCompanyName to select a company name from a table called Company. This is the first part of my cascade.

The cmbCompanyName has three columns.

Col 0 is the CompanyID, which is hidden.
Col 1 is the CompanayName, which is populated to the form
Col 2 is the CompanyCity, which is populated using a different part of the form using the control =[cmbCompanyName].[Column](2)

All of this works correctly.

The second part of my cascade cmbPoCLastName looks at another table called Contacts and should take the CompanyName from cmbCompanyName to filter my choices.

cmbPoCLastName uses the following query

SELECT Contacts.Company, Contacts.[Last Name], Contacts.[First Name], Contacts.[E-mail Address], Contacts.[Business Phone]
FROM Contacts
WHERE (((Contacts.Company)=[Forms]![Opportunity]![cmbCompanyName]));

To support my cascasde, cmbCompanyName requeries cmbPoCLastName upon update.

Private Sub cmbCompanyName_AfterUpdate()

cmbPoCLastName.Requery

End Sub

All of this result in nothing showing up in the second part of my cascade.

I believe the issue is cmbPoCLastName is filtered by the hidden CompanyID. I cannot figure out how to force this query to look at the company name vs. the ID number.

Thanks.

Exiled_In_CA
  • 43
  • 5
  • Why are you saving company name into Contacts instead of company ID? Query object cannot see columns of combobox. If you want company name then have a textbox that pulls company name from combobox column then query object references textbox. If you don't use CompanyID as filter criteria then why bother having in combobox column? – June7 Feb 10 '21 at 03:32
  • Or use VBA to set combobox RowSource with SQL statement. Or eliminate ID column and company name is value of combobox. – June7 Feb 10 '21 at 04:01
  • Does this answer your question? [Cascading combo boxes](https://stackoverflow.com/questions/22645191/cascading-combo-boxes) – June7 Feb 10 '21 at 04:41
  • @June7, your comment about the `CompanyID` did the trick. I've created a 1 to many relationship between `CompanyID` on both `Contacts` and `Company`. Thanks. – Exiled_In_CA Feb 10 '21 at 20:59

1 Answers1

0

Based on the first comment made by @June7, I reworked my tables to use CompanyID to related them to one another. I had previously used CompanyID in some tables and CompanyName in others.

June7
  • 19,874
  • 8
  • 24
  • 34
Exiled_In_CA
  • 43
  • 5