Select records in one control based on another.

[321jimk]

I think this is probably a waste of time anyway because as I understand it all the data for each of the controls on a subform is loaded even before the data for the main form but here goes anyway.

I have four tables - 1 is a list of users, 2 a list of manufacturers and 3 a list of products the manufacturers are selling. The third table has three fields - the ProductID, the manufacturer and the product.
The fourth table is a list of the products purchasers have made showing the manufacturer, the product and various other descriptions relating to the product eg colour etc.

In my database I have a main form giving details about the user and a subform showing the products he has bought. The subform is based on the fourth table.

I would like the form user to be able to select the manufacturer on the subform then be shown the products that manufacturer makes.

Is it possible on the subform when adding a new record for the user to be shown in a second control just the products a manufacturer makes based on the users choice of manufacturer in the first control? (if there are two controls on the subform and the first selects the manufacturer can you determine the products which can be shown in the second control, based on the users choice in the first control?).

[Bob Fitz]

Hi

I think it should be possible to do this.
If I have understood your needs correctly, you would need two combo boxes on the sub form. Lets call the first one cmbManu. It should be based on the table of manufacturers. The second, lets call this one cmbProds, should be based on your third table with criteria set to restrict the records returned to only those that are the same as the value in cmbManu. In the "Got Focus" event of cmbProds you will need this line of code:

 
Me.ActiveControl.Requery