Lists/rowsource/primary key

[badboo]

Hi,

I have two tables and two lists : CustomerTable, InvoicesTable, CustomerList and InvoicesList. CustomerList displays my Customers and when I click on a row I want in InvoicesList all the invoices related to the customers I clicked on.

I have the following vba code

SQL = "SELECT [CustomerID] FROM [CustomerTable] Where
[CustomerTable]![CustomerID] <> 0 "
SQL = SQL & "And [CustomerTable]![customerID] like '" & Me.CustomerList & "' "
SQL = SQL & ";"
Me.InvoicesList.RowSource = SQL

This code is executed on the event "Click" of "CustomerList". I get the right SQL code and it works if CustomerID is the primary key of the table the second list is queried to. But in that case where customerID is not the primary key of InvoicesTable I do not get anything.

Can someone enlighten me on this topic ?

 

[hyperpau]

Your problem is a simple one-to-many relationship.
The thing the confuses me in your quetion is that you are talking about tables and not forms. so what click events are referring to? I guess you mean that your forms are linke to these two tables?
And when you say select from the List, is this a combo box or a list box?
I would suggest a combo box.

If this is correct, all you have to do is put the InvoicesTable as a Subform linking the Child-Parent to the CustomerList fields.

Everytime a user selects a customer from the combo box, the subform will automatically show all entries only relating to that Customer selected from the list.

Hi,

I have two tables and two lists : CustomerTable, InvoicesTable, CustomerList and InvoicesList. CustomerList displays my Customers and when I click on a row I want in InvoicesList all the invoices related to the customers I clicked on.

I have the following vba code

SQL = "SELECT [CustomerID] FROM [CustomerTable] Where
[CustomerTable]![CustomerID] <> 0 "
SQL = SQL & "And [CustomerTable]![customerID] like '" & Me.CustomerList & "' "
SQL = SQL & ";"
Me.InvoicesList.RowSource = SQL

This code is executed on the event "Click" of "CustomerList". I get the right SQL code and it works if CustomerID is the primary key of the table the second list is queried to. But in that case where customerID is not the primary key of InvoicesTable I do not get anything.

Can someone enlighten me on this topic ?

 

[badboo]

You are right, thanks.