Can Anyone Tell Me Whats Wrong with FROM Clause?

[tfayer]

I am trying to create a filter based on tblStoredStudies.site. I have created an unbound combo with the site names able to be selected. I have created a Filter button that when click should filter the results in my subform to show only the sites that match the selection in the combo box. When I click the Filter button I am getting "Syntax error in FROM clause". The code for the filter button is as follows

Private Sub Filter_Click()

On Error GoTo Err_Filter_Click

Dim strSQL As String, strWhere As String

'*** create the query based on the information on the form
strSQL = "SELECT tblStoredStudies.protocol_number, tblStoredStudies.therapeutic_area, tblStoredStudies.sponsor, tblStoredStudies.cro, tblStoredStudies.pi, tblStoredStudies.site, tblStoredStudies.storage " _
& " FROM tblStoredStudies "
strWhere = "site=" & Combo0

strSQL = strSQL & strWhere
'MsgBox strSQL

'*** change the recordsource of the subform
Me.sfrmStoredStudiesList.Form.RecordSource = strSQL
Me.sfrmStoredStudiesList.Form.Requery

RemoveFilter.Visible = True
RemoveFilter.SetFocus


Exit_Filter_Click:
Exit Sub

Err_Filter_Click:
MsgBox Err.Description
Resume Exit_Filter_Click

End Sub

Can anyone tell whats wrong?

Thank you

 

[KenReay]

Hi

It would be helpful to know the error message, but

If site is a string then

strWhere = "site= '" & Combo0 & "' "

failing that suggest you put a breakpoint after

strSQL = strSQL & strWhere

in debug.window, put debug.print strsql

copy and paste result into query grid. SQL view, then try to run it, you usually get better diagnostic messages this way
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[tfayer]

I am getting just a msgbox saying "Syntax error in FROM clause." It does not even start the debugger.

Any suggestions?

 

[KenReay]

Hi

Have you tried the suggestions I sent you, if yes, what result Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[tfayer]

I have tried it and now when I click the filter button it goes to vb window highlighting strSQL = strSQL & strWhere in yellow. Am I doing something wrong? I've never done this before.

Thanks

 

[KenReay]

Hi
Could you do as I asked and print the content of strSQl in the debug window, then post it here, and/or copy and paste it into the query builder SQL view Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[Craig0201]

You posted....

strSQL = "SELECT tblStoredStudies.protocol_number, tblStoredStudies.therapeutic_area, tblStoredStudies.sponsor, tblStoredStudies.cro, tblStoredStudies.pi, tblStoredStudies.site, tblStoredStudies.storage " _
& " FROM tblStoredStudies "
strWhere = "site=" & Combo0

strSQL = strSQL & strWhere

You're missing the key word WHERE.....

strWhere = "WHERE site=" & Combo0

Craig

 

[KenReay]

Hi

Craig, spot on why didn't I notice that!!!!!!!!! Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[tfayer]

Thanks cant believe I missed that.