Filtering a ListBox based on two tables.

[TurnItOffAndOn]

Hi All,

I'm getting used to using access so go easy on me.

The issue I'm having is getting my list box to provide the correct data. Based on two tables information.

I have two tables one called CustomerT and one called CustomerOrderT they both have a field called CRM ID, this is a one to many relationship. I have a CustomerF form that displays all of the data from the CustomerT table based on the CRM ID, I would also like it to display the last orders from CustomerOrderT based on the CRM ID. The ListBox currently shows all orders regardless of the CustomerF CRM ID displayed.

I hope this is enough of an explanation but please ask me for more info if needed.

Thanks.

[PhilOfWalton]

Information is fine.

What is the RowSource of your listbox?

I would add that the more usual way of handling a Customer & Order situation is to use a subform to show all the orders.

If you look at the Northwind database Order Details Form, you will get the idea

Phil

[TurnItOffAndOn]

The Row Source is SelectCustomerOrdersT.

The actual table is a tracker as appose to filling in the full order. So each record is updated at a different stage, which is why I'm using a table to store the info. I'm displaying 3 fields from that table in my ListBox.

[PhilOfWalton]

Sorry, that is not a valid Rowsource. Please check it carefully

It should be more like

SELECT CustomerOrderT.* FROM CustomerOrderT
WHERE [CRM ID] = Forms![YourFormName]![CRM ID]
ORDER BY CustomerOrderDate DESC;
 

You'll obviously will have to use the correct name of ypour form and the order date

Phil

[NeoPa]

I'm guessing that [SelectCustomerOrdersT] is actually the name of a QueryDef.

Perhaps you could post in the SQL used for [SelectCustomerOrdersT] so that we can review it.