I need to populate a List box using SQL when a ComboBox is Changed

[bconner]

I have a list box that I want to populate once a ComboBox is changed. Below is the code I am using but it won't work or throw an error message.
Any idea what I may be doing wrong?

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;"

All_List_Not_Worked.Requery

End Sub

 

[pbaldy]

For starters, you're not including spaces between lines, so you'll end up with:

...FROM [Self Pay AP]WHERE...

And Access won't be able to parse the SQL. If you're still stuck, use a string for the SQL so you can debug it easier.

 

[bconner]

I tried the below and it still won't work

Private Sub All_Cmb_Group_Change()
Dim strSQL As String
strSQL = "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;"
All_List_Not_Worked.RowSource = strSQL

All_List_Not_Worked.Requery
All_txt_Mrn_Search = Null

End Sub

 

[pbaldy]

It appears you have the same problem before HAVING. The way to debug is to add:

Debug.Print strSQL

after the string is built, which will print out the finished SQL to the VBA Immediate window. You can examine it there, and copy/paste it to a blank query and try to run it, which will often provide a more descriptive error.