Limit choices in a combobox based on the value in another combobox

[dougdrex]

I'm using Access 2010. I've created a form called Enter_Clients with a subform called Enter_Sales_Subform. In the subform, I have 2 comboboxes, CompanyID and ProductID. After entering a value in CompanyID, I'd like to limit the values that show up in the ProductID combobox based on the values in tbl_Companies_Products (including the Active value being "1" or "True").

Anybody know how to do that?

Thanks in advance!


Doug

[June7]

Called cascading or dependent comboboxes. Review: http://www.datapigtechnologies.com/f...combobox2.html

[dougdrex]

June7, you are a wealth of knowledge. I am truly amazed!

It seems like I'm almost there. Now, Access is popping up an "Enter Parameter Value" dialogue box. It says "Forms!Enter_Sales_Subform.cmbCompanyID" under the heading.

I've double and triple checked each setting and don't see anything that jumps out at me. Any ideas?!?

[dougdrex]

Here is an updated version of the database. If anyone else has any ideas, please feel free to share your thoughts!

Thanks!
Doug

[June7]

There is no reason to include tbl_Sales in the RecordSource for Enter_Clients.

Don't include form reference prefix in RowSource for Product combobox:

SELECT tbl_Products.ProductName FROM tbl_Products INNER JOIN (tbl_Companies INNER JOIN tbl_Companies_Products ON tbl_Companies.CompanyID = tbl_Companies_Products.CompanyID) ON tbl_Products.ProductID = tbl_Companies_Products.ProductID WHERE (((tbl_Companies.CompanyID)=[cmbCompanyID])) GROUP BY tbl_Products.ProductName, tbl_Companies_Products.ProductID;

Understand that cascading comboboxes with alias don't work nice in Continuous or Datasheet view form.

[dougdrex]

June7, that worked perfectly. I am so very thankful for your help!!

AWESOME!!!!!

[dougdrex]

Okay, so there is apparently still one small bug. I was testing the database before launching it and discovered an issue.

When entering a new customer with the sales information on the before mentioned form/subform, Access gave an error message saying "The Microsoft Access database engine cannot find a record in the table 'tbl_Products' with key matching field(s) 'CompanyProductID'."

Anyone have any ideas what may be causing the error message?

[June7]

Would have to review the data structure and form and control properties. If you want to provide db, follow instructions at bottom of my post.

[dougdrex]

Here is the zipped file.

Thanks again!

[June7]

To repeat my earlier comment: There is no reason to include tbl_Sales in the RecordSource for Enter_Clients.
Including tblSales with an INNER JOIN actually can cause issue - INNER JOIN requires related records in both tables to already exist.

Similarly, there is no reason to include tbl_Companies and tbl_Products in the RecordSource for the subform. However, if you do include them, cannot be an INNER JOIN (see note above). Change to RIGHT JOIN. If you bind any textboxes to the fields of those two tables, set them Locked Yes and TabStop No to prevent editing, only for display. And since you did not bind any textboxes to those fields, including those tables is totally unnecessary.

Could just reference the tables in the RecordSource, a query is not required.

[dougdrex]

What do you think would be the best way for a user to log clients and sales together in one screen? Or should that be two different forms?

[June7]

The form/subform arrangement is appropriate, just fix each RecordSource.