By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Filtering a ListBox based on two tables.

P: 2
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.
Jul 6 '17 #1
Share this Question
Share on Google+
4 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Jul 6 '17 #2

P: 2
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.
Jul 6 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
Sorry, that is not a valid Rowsource. Please check it carefully

It should be more like
Expand|Select|Wrap|Line Numbers
  1. SELECT CustomerOrderT.* FROM CustomerOrderT
  2. WHERE [CRM ID] = Forms![YourFormName]![CRM ID]
  3. ORDER BY CustomerOrderDate DESC;
  4.  
You'll obviously will have to use the correct name of ypour form and the order date

Phil
Jul 6 '17 #4

NeoPa
Expert Mod 15k+
P: 31,419
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.
Jul 6 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.