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

Refine a Stored Query for a ListBox based on a selection in a Combo Box

P: 2
Hi,

Just joined and also a fairly new geriatric programmer to the land of MS ACCESS VBA :(

I have a table called 'transactions' and a query called qryTransactions. This query selects all records based on whether or not a flag called 'Sold' is set to 'No'. Working fine.

I have a list box (Multi Columns) which has a RowSource set to qryTransactions.

So far so good, when form loads, I get all 'Not Sold' records in the List Box.

There is a ComboBox fed from a table of customers - basically the CustomerName. When I select a name, I want to limit the qryTransactions just to the customer name selected from the combo box.

QUESTION: Is there a simple way to make use of a clause like 'WHERE Customer_Name = cboBox.value' so that the RowSource of the ListBox reflects this WHERE clause.

P.S. seen loads of complex VBA contortions around this issue, but for this geriatric, surely there must be a simple solution :(

Thanks for having a read - any pointers appreciated

Oliver
Feb 1 '19 #1

✓ answered by LenseOnLife

OK, I've sorted it out

Expand|Select|Wrap|Line Numbers
  1. Dim strSql As String
  2. strSQL = "Select SalesOrderId, TransactionDate, .... 
  3. FROM tblSalesOrders 
  4. WHERE  Customer_Name = '" & lstCustomers & "'"
  5. lstTransactions.RowSource = strSQL
  6. lstTransactions.Requery
And that's it :)

Oliver

Share this Question
Share on Google+
1 Reply


P: 2
OK, I've sorted it out

Expand|Select|Wrap|Line Numbers
  1. Dim strSql As String
  2. strSQL = "Select SalesOrderId, TransactionDate, .... 
  3. FROM tblSalesOrders 
  4. WHERE  Customer_Name = '" & lstCustomers & "'"
  5. lstTransactions.RowSource = strSQL
  6. lstTransactions.Requery
And that's it :)

Oliver
Feb 1 '19 #2

Post your reply

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