List Box Not Updating

[GC2010]

I have a form that has a combo box and a list box on it. From the GUI of the form, I have set the list box control source = to a query. From the combobox exit event I edit the query to add in a where param.

Now my issue is that when the form loads the first time it displays the information in the list box as it should. However, when it loads a subsequent time the listbox displays the information from the previous query, not the most up to date. Do I need to "repaint" or "refresh" my form somehow so that the listbox is updated to the query results that I want? If I open the query it is showing the data I want it to, just the form is not updating.

My VBA looks like this:

Private Sub cboOne_Exit(Cancel As Integer)

List13.ForeColor - vbBlack

Dim dry As String
Dim qdf As QueryDef

On Error Resume Next
DoCmd.DeleteObject acQuery, "ListBoxQuery"

dry = "Query SQL Here"

Set pdf = CurrentDb.CreateQueryDef("ListBoxQuery", dry)
DoCmd.OpenQuery qdf.Name
qdf.Close
Set pdf = Nothing
End Sub

Private Sub Form_Load()
  List13.ForeColor = vbWhite
End Sub

 

[theDBguy]

Hi. Try running the same code you have in the Exit event of the Combo from the Open or Load event of your Form.

 

[GC2010]

I don't want the code to run in the open event of my form because I need to pass the selection from the combo box to the where clause of the query in order to display the relevant data in the list box...

 

[theDBguy]

I don't want the code to run in the open event of my form because I need to pass the selection from the combo box to the where clause of the query in order to display the relevant data in the list box...

You will have to run some code to "clear" it. If you run the same code from the Combo and you're getting an error, try handling it or modify the code slightly to account for no selection yet.

 

[GC2010]

You will have to run some code to "clear" it. If you run the same code from the Combo and you're getting an error, try handling it or modify the code slightly to account for no selection yet.

AH - so I should add some code in the form_load() event to "clear" the list box?
Easiest thing that comes to my mind is to delete the query so the source will be null

 

[theDBguy]

AH - so I should add some code in the form_load() event to "clear" the list box?
Easiest thing that comes to my mind is to delete the query so the source will be null

I'd say try it out and let us know if it doesn't work. Cheers!

 

[GC2010]

I'd say try it out and let us know if it doesn't work. Cheers!

Adding that line to my Form_Load() event does not change the listbox data.

 

[theDBguy]

Adding that line to my Form_Load() event does not change the listbox data.

Hmm, can you post the "exact" code you added? Thanks.

 

[GC2010]

Hmm, can you post the "exact" code you added? Thanks.

Private Sub Form_Load()
    List13.ForeColor = vbWhite
    One Error Resume Next
    DoCmd.DeleteQuery acQuery, "ListBoxQuery"
End Sub

 

[theDBguy]

Private Sub Form_Load()
    List13.ForeColor = vbWhite
    One Error Resume Next
    DoCmd.DeleteQuery acQuery, "ListBoxQuery"
    [COLOR=red]Me.ListboxName.Requery[/COLOR]
 End Sub

I see. Try adding the red line, just in case.


Edit: Also, can you verify the query was deleted? Thanks.

 

[GC2010]

Yes, I verified the query was deleted.

If I add in the red line you suggest nothing is displayed in the list box after the comboboxOne_Exit() event fires. Query holds accurate information.

 

[theDBguy]

Yes, I verified the query was deleted.

If I add in the red line you suggest nothing is displayed in the list box after the comboboxOne_Exit() event fires. Query holds accurate information.

Okay, I'm not sure if you fixed the problem or not yet. You said, originally, the listbox displays the old value when you open it. So, if you deleted the query when the form loads and the listbox now displays empty, then is it fixed? If not, what should it display instead?

 

[GC2010]

Okay, I'm not sure if you fixed the problem or not yet. You said, originally, the listbox displays the old value when you open it. So, if you deleted the query when the form loads and the listbox now displays empty, then is it fixed? If not, what should it display instead?

Sorry, I did not properly explain. Yes my original issue is resolved that the list box loads empty when he form loads. However, a new issue has been created in the fact that on the combobox Exit event, the list box should be populated with the query results and this is no longer occurring.

 

[theDBguy]

Sorry, I did not properly explain. Yes my original issue is resolved that the list box loads empty when he form loads. However, a new issue has been created in the fact that on the combobox Exit event, the list box should be populated with the query results and this is no longer occurring.

LOL. So typical. If it's not one thing, it's another. You fix one bug, another one shows up. Can you step through the Exit event of the combobox and see what's wrong? Or, if you can post a sample (demo) copy of your db, we could help you trace the problem.

 

[GC2010]

LOL. So typical. If it's not one thing, it's another. You fix one bug, another one shows up. Can you step through the Exit event of the combobox and see what's wrong? Or, if you can post a sample (demo) copy of your db, we could help you trace the problem.

Sure - it will be late afternoon/early evening before I get a moment to de-sensitize data to upload a sample but I will.

Thank you so much for the assistance here.

 

[GC2010]

LOL. So typical. If it's not one thing, it's another. You fix one bug, another one shows up. Can you step through the Exit event of the combobox and see what's wrong? Or, if you can post a sample (demo) copy of your db, we could help you trace the problem.

Okay - attached is a database containing non-sensitive data that illustrates my issue. I want the form to load with a blank list box, and on the exit event of the combo box
1) If a name is selected show the CVN & SVN from the query for that name
2) If a name is not selected notify user that they must select a name (code is not there for this, but that is desired outcome)

Please let me know what I have done incorrectly

 

[June7]

What I did:

1. listbox properties
RowSource: SELECT * FROM AllData WHERE [Name] = Combo2;
ColumnCount: 4
ColumnWidths: 0";0.5";1.0";0.5"

2. replace all the VBA with:
Private Sub Combo2_AfterUpdate()
Me.List11.Requery
End Sub

3. delete the query object

 

[GC2010]

Hi - thank you kindly for the response.

I need the list box to show a concatenated version of CVN & SVN like in my sample DB. Just one column that shows the concatenated version, which is why I was going with the query. Is it possible to show the concatenated version by editing the row source as you suggest?

 

[theDBguy]

Hi - thank you kindly for the response.

I need the list box to show a concatenated version of CVN & SVN like in my sample DB. Just one column that shows the concatenated version, which is why I was going with the query. Is it possible to show the concatenated version by editing the row source as you suggest?

Hi. I didn't get a chance to download your demo but in your original code, you had this:

dry = "Query SQL Here

What does your original SQL looks like? You could try using it instead of what June7 used.

 

[GC2010]

What I did:

1. listbox properties
RowSource: SELECT * FROM AllData WHERE [Name] = Combo2;
ColumnCount: 4
ColumnWidths: 0";0.5";1.0";0.5"

2. replace all the VBA with:
Private Sub Combo2_AfterUpdate()
Me.List11.Requery
End Sub

3. delete the query object

I have deleted the query and removed all vba from my project except this line

Option Compare Database
Private Sub Combo2_AfterUpdate()
Me.List11.Requery
End Sub

And I updated the row source of my listbox to be

SELECT * FROM AllData WHERE [Name] = Combo2;

Now when I select a name from the combo box the list box is never populated.