Refreshing a two column list box after updating records in the source table

[Clint Schaefer]

Apologies in advance. I've seen similar questions, but none of the solutions seem to be working for me...

I have a form that allows a user to select mutiple items (payroll deduction codes) in a list box. The values are drawn from a table called PayrollCodes. In the second list box, the user chooses a category that the items from the first list belong to. I have a command button with code attached to the OnClick Event that then records the category selected to the records that match the payroll deduction codes selected. I have a third list box (CodeAndCategory) that shows the values in the table PayrollCodes. There are two columns in this list box- one for the payroll code and one for the category. I would like this CodeAndCategory list box to show the updated information after the command button is pressed and the table updated. As I have it now, I have the following code right before the function ends:

Me.CodeAndCategory.Requery
Me.Repaint

If I scroll the listbox down and back up, the record in CodeAndCategory refreshes. If I manually close and open the form, CodeAndCategory refreshes.
I have tried using code to close and reopen the form, but that did not work.
Will be happy to include all code if need be.

[barry07]

I'm guessing, but try Refresh or Requery - Repaint only reformats the screen it does nothing with data.
You may have to Requery/Refresh each combo box.
If that doesn't work can you post the RowSource for each combo box?

[Clint Schaefer]

Thanks so much for your quick response. I have modified the code as follows:

Me.CodeList.Requery
Me.CatList.Requery
Me.CodeAndCategory.Requery
Me.Refresh

and still the same issue. Here are the Rowsource contents:

For listbox CodeList:

SELECT tblUniqueCodes.Code FROM tblUniqueCodes; 

For listbox CatList:
tblProdCats

For listbox CodeAndCategory:
tblUniqueCodes

Thanks again for your help!!

[Denburt]

I don't see any issues with what you posted but I also don't see the subsequent combo boxes criteria however since your last post I can skip to the point since it sounds like the code requery or what have you is running before the process of updating your table has finished. The solution for that would be to use either "DBEngine.Idle" or try "DoEvents" to see if that helps. Let us know how things go.

[Clint Schaefer]

Thank you for the response. I had almost given up on hearing anything, so I apologize for just now trying your suggestion.

No luck... Here is the new code. I'm not familiar with DBEngine.Idle, so I hope I used it correctly.

CodeListItems = [CodeList].ListCount - 1
For CodeListCounter = 0 To CodeListItems
    If [CodeList].Selected(CodeListCounter) = True Then
    rst.Open "SELECT * FROM tblUniqueCodes WHERE (tblUniqueCodes.Code) = '" & Me![CodeList].Column(0, CodeListCounter) & "'", CurConn, , , adCmdText
    rst![ProdCat] = Me![CatList]
    rst.Update
    rst.Close
    End If
Next CodeListCounter
 
DBEngine.Idle
 
Me.CodeAndCategory.Requery
 
MsgBox "The codes have been associated with the " & Forms![Multi List Test]![CatList] & " category."
 
Exit_Command8_Click:
    Exit Sub
 
Err_Command8_Click:
    MsgBox Err.Description
    Resume Exit_Command8_Click
 
End Sub

The interesting thing is you'll notice I have a confirmation message box pop up for the user. If you click, hold down the mouse button for a second, and release, this works great and the List Box is updated. If you click and release too quickly, no update to the table.