List box updating via query

I have three list boxes, the first of which finds a record from a table based on the selection, the selection supplies the data needed for the criteria of the second list, the second list uses a query as row source, the SQL for this is:
SELECT Quotes.QuoteID, Quotes.EsitmatedCloseDate, Quotes.QuoteStatus, Quotes.QuoteStage
FROM Quotes
WHERE (((Quotes.QuoteStage)<>"Complete") AND ((Quotes.ContactID)=[forms]![Contacts].[ContactID]));
I use a requery once a change is made from the first list box (as all 3 lists are on the same form) simply using:
Private Sub lstContacts_Click()
[lstQuotes].Requery
End Sub
The final list box uses a query as row source that gets its data for the criteria from the second list box:
SELECT qryQuoteItems.ProductName, qryQuoteItems.Description, qryQuoteItems.Qty, qryQuoteItems.Cost
FROM qryQuoteItems
ORDER BY qryQuoteItems.ProductName;
I use a requery to update this listbox:
Private Sub lstQuotes_AfterUpdate()
[lstQuoteItems].Requery
End Sub
All works well bar one thing, if I have data in the third list box and then select a record from the first list box  which has NO records in the second list box, the 3rd list box does not update properly, it should refresh to display no records,  however, it does not change at all, even if I press the refresh button, the query on its own works fine, in that if no data is entered into the criteria field then no results are displayed, so its almost like its seeing data in the second list box that is not there? Its a simple problem, but an annoying one none the less.

Any help would be great.
Thanks
Dan
Dan89Asked:
Who is Participating?
 
Eric ShermanConnect With a Mentor Accountant/DeveloperCommented:
I would convert the macro to VBA and include it with the after update event of listbox 1.

1.)  Currently your 3rd listbox has a SQL Select statement for RowSource in the properties, correct??  Copy that to the clipboard.

2.)  In the AfterUpdate event of your 1st list box include the following.
me.ListBox3.RowSource = ""

3.) In the AfterUpdate event of your 2nd list box include the following.
me.ListBox3.rowSource ="Select ...........;"   '<------------Paste the select statement you copied earlier here between the double quotes.


ET
0
 
Eric ShermanAccountant/DeveloperCommented:
Since the 3rd list box will only get re-queried using the after-update event of the 2nd list box ...  I would set the value of the 3rd list box to NULL in the after-update event of the 1st list box.

ET
0
 
Dan89Author Commented:
The third list still only updates once an item has been selected from the second list.

Dan
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Eric ShermanAccountant/DeveloperCommented:
Ok, I was thinking of a Combo Box not a List Box ...  Sorry.

Basically, what you want to do is make the 3rd List Box empty when you make a selection in the 1st List Box.

Therefore, I would set the RowSource of the 3rd list box to "" in the after update event of the 1st list box.  Then set it to your "Select..." statement in the after update event of the 2nd list box.

ET
0
 
Dan89Author Commented:
How do i change the row source in VBA and can i add a VBA afterupdate to the 1st listbox because it already has a macro in it that finds a record depending on selection?

Dan
0
 
Dan89Author Commented:
Sorry to be a pain but how do i make a findrecord macro in VBA, am i right by saying to use ADODB?

Dan
0
 
Eric ShermanAccountant/DeveloperCommented:
Here is an example ....

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM YourTableName", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rst.Find "[YourFieldName] ='" & SomeString & "'"
If Not rst.EOF Then   ' The record was found
     'Do Something Here
End If
rst.Close
End Function

ET
0
 
Dan89Author Commented:
In the 'Do something here bit, do i simply use
DoCmd.OpenForm, , , rst?
Im unsure of what is happening here, so firstly a recordset is established, then that is filled with the results of the SQL statement? Then you loop through that until you find the critera and when that record is found it stops on that record? Can you then not just open that record in the correct form?

Many thanks
Dan
0
 
Eric ShermanAccountant/DeveloperCommented:
Dan89 ...

We are getting way off topic from your original question.  In the previous post I gave you a solution that would resolve your original question which is set the row source of list box 3 to "" after you update list box 1 then set the row source to your select statement after you update list box 2.

I even gave you an example of how to use a recordset to find a record because that's what you asked.

This is only a 50 point question.  If the first solution solved your original question then you need to open another question relating to your macro, ok.

Thanks,

ET
0
All Courses

From novice to tech pro — start learning today.