Populate List Box using SQL

[bconner]

I am trying to populate a List Box with a SQL Statement when a Combo Box has changed. Below is the code I am using but when I click in the Combo Box and select a value nothing happens. Any help is greatly appreciated....

Private Sub All_Cmb_Group_Change()
 
All_List_Not_Worked.RowSource = "SELECT [Self Pay AP].[MEDICAL RECORD], Sum([Self Pay AP].[Current Inv Balance]) AS [SumOfCurrent Inv Balance]" & _
"FROM [Self Pay AP]" & _
"WHERE ((([Self Pay AP].[MEDICAL RECORD]) = '" & [Forms]![FrmMain]![All_txt_Mrn_Search] & "'" & ") AND" & " (([Self Pay AP].GROUP) = '" & [Forms]![FrmMain]![All_Cmb_Group] & "'" & ")) OR" & " ((([Self Pay AP].GROUP) = '" & [Forms]![FrmMain]![All_Cmb_Group] & "'" & "))" & _
"GROUP BY [Self Pay AP].[MEDICAL RECORD], [Self Pay AP].[Date Worked]" & _
"HAVING ((([Self Pay AP].[Date Worked]) Is Null Or ([Self Pay AP].[Date Worked]) Is Null))" & _
"ORDER BY Sum([Self Pay AP].[Current Inv Balance]) DESC;"
 
 
End Sub

 

[boblarson]

Don't use the change event of the combo box, use the AFTER UPDATE event.

You also are missing at least one space between things.

To troubleshoot, declare a string variable (strSQL) and then assign the SQL to that instead and then use the strSQL for the row source and also use

Debug.Print strSQL

so you can see what the actual resulting SQL string is and it should become obvious what is happening.