Combo Box navigation/Parent-Child

[AlanM]

Hi All

I'm sure this is simple, I have a mental block on how to do it.

I have two tables in a 1 to many relationship, TableOne and TableTwo. (ie. Company and Locations, linked on CompanyID.)

My main form has a combo box to select a record from TableOne. This works OK, displaying TableOne record data, and a subform displays the first related child record from TableTwo. I want to use a combo box to navigate the other related child records, rather than the built-in navigation. The data source for this combo box is comprised of a field from the child table, specifically the Location Number.

I have a combo box in the header of the subform, but it displays the location numbers for all records in TableOne, not just for the parent record in the main form. (Eg. for a company with 2 locations the combo box would show 1 and 2; for a company with 4 locations, it would show 1, 2, 3 and 4. Making a selection from the combo box would display the matching record from TableTwo, for data entry/data maintenance.

The subform is linked to the mainform on CompanyID.

I'm clearly missing something here.

All assistance much appreciated.

Cheers
AlanM

 

[boblarson]

Your combo box on the subform should have as its recordsource a query and not a table and the criteria should be set to the primary key field on the main form.

 

[AlanM]

Hi Bob

Thanks for that info. I thought that is what I have, but it still lists all child records in the combo:

Combo Box properties_Data tab

RowSourceType: Table/Query

Row Source: SELECT [tblCompanies].[CompanyID], [tblCompanyUnits].[LocationNumber] FROM tblCompanies LEFT JOIN tblCompanyUnits ON [tblCompanies].[CompanyID], [tblCompanyUnits].[CompanyID] ORDER BY [tblCompanies].[CompanyID], [tblCompanyUnits].[LocationNumber];

Bound Column: 1

Do you happen to have an example in a database?

Thanks

AlanM

 

[boblarson]

You don't have a WHERE clause in your SQL -

WHERE [tblCompanies].[CompanyID] = Forms!YourMainFormNameHere.YourCompanyIDTextBoxNameHere

 

[AlanM]

DOH! Thanks Bob, much appreciated.

AlanM