Run TIme Error 3131 - Syntax error in From Clause

[manix]

Hi All,

First off just let me thank gromit for posting the search form example in the sample database forum. I have used this to try and create my own little search form so I can query a table of data.

But I keep getting the run time error 3131. There is an issue with my FROM clause. I am using the following code:

Private Sub btnSearch_Click()
    
    ' Update the record source
    Me.frmsubToolData.Form.RecordSource = "SELECT * FROM qryTooldata2 " & BuildFilter
    
    ' Requery the subform
    Me.frmsubToolData.Requery
End Sub


Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
    
    ' Check for ST Number
    If Me.cmbSTno > "" Then
        varWhere = varWhere & "[ST Order Number] = " & Me.cmbSTno & " AND "
    End If
    
    ' Check for Project Number
    If Me.TextProject > 0 Then
        varWhere = varWhere & "[Related to Project Number] = " & Me.TextProject & " AND "
    End If
    
    ' Check for Tool Status
    If Me.cmbstatus > "" Then
        varWhere = varWhere & "[Tool Status] = " & Me.cmbstatus & " AND "
    End If
    
    ' Check for Tool Type
    If Me.cmbtype > 0 Then
        varWhere = varWhere & "[Tool Type] = " & Me.cmbtype & " AND "
    End If
    
    ' Check for Tool Location
    If Me.cmblocation > 0 Then
        varWhere = varWhere & "[Tool Location] = " & Me.cmblocation & " AND "
    End If
    
    ' Check for LIKE Country
    If Me.Textcountry > "" Then
        varWhere = varWhere & "[FirstName] LIKE """ & Me.Textcountry & "*"" AND "
    End If
    
    BuildFilter = varWhere
    
End Function

Can anyone see where things might be going wrong?

As always, thanks to anyone who can help!

 

[MarkK]

I don't see that the string "WHERE " is ever added to your SQL. Also, if your BuildFilter() returns anything it will always leave the string " AND " at the end of your SQL.
Consider...

Private Sub btnSearch_Click()
  ' Update the record source
  Me.frmsubToolData.Form.RecordSource = _
    "SELECT * FROM qryTooldata2 WHERE True " & BuildFilter
  ' Requery the subform (this may occur automatically on r-source change)
  Me.frmsubToolData.Requery
End Sub

And then PREFIX each of the these with the " AND " in BuildFilter()

varWhere = varWhere & " AND [ST Order Number] = " & Me.cmbSTno & " "

 

[manix]

I don't see that the string "WHERE " is ever added to your SQL. Also, if your BuildFilter() returns anything it will always leave the string " AND " at the end of your SQL.
Consider...

Private Sub btnSearch_Click()
  ' Update the record source
  Me.frmsubToolData.Form.RecordSource = _
    "SELECT * FROM qryTooldata2 WHERE True " & BuildFilter
  ' Requery the subform (this may occur automatically on r-source change)
  Me.frmsubToolData.Requery
End Sub

And then PREFIX each of the these with the " AND " in BuildFilter()

varWhere = varWhere & " AND [ST Order Number] = " & Me.cmbSTno & " "

Thanks Lagbolt, but if I make the cnages you have suggested I get a new error:

"Run Time Error 3075" Syntax error (missing operator) in query expression 'True AND [Related to Project Number]='.

My code no looks like this:

Private Sub btnSearch_Click()
    
    ' Update the record source
    Me.frmsubToolData.Form.RecordSource = "SELECT * FROM qryTooldata2 WHERE True " & BuildFilter
    
    ' Requery the subform
    Me.frmsubToolData.Requery
End Sub


Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
    
    ' Check for ST Number
    If Me.cmbSTno > "" Then
        varWhere = varWhere & " AND [ST Order Number] = " & Me.cmbSTno & " "
    End If
    
    ' Check for Project Number
    If Me.TextProject > 0 Then
        varWhere = varWhere & " AND [Related to Project Number] = " & Me.TextProject & " "
    End If
    
    ' Check for Tool Status
    If Me.cmbstatus > "" Then
        varWhere = varWhere & " AND [Tool Status] = " & Me.cmbstatus & " "
    End If
    
    ' Check for Tool Type
    If Me.cmbtype > 0 Then
        varWhere = varWhere & " AND [Tool Type] = " & Me.cmbtype & " "
    End If
    
    ' Check for Tool Location
    If Me.cmblocation > 0 Then
        varWhere = varWhere & " AND [Tool Location] = " & Me.cmblocation & " "
    End If
    
    ' Check for LIKE Country
    If Me.Textcountry > "" Then
        varWhere = varWhere & " AND [FirstName] LIKE """ & Me.Textcountry & "*"" "
    End If
    
    BuildFilter = varWhere
    
End Function

Being completely honest, I am stuck! I am only just begining to learn VB and Access!

 

[MarkK]

What happens here?

' Check for Project Number
If Me.TextProject > 0 Then
  varWhere = varWhere & " AND [Related to Project Number] = " & Me.TextProject & " "
End If

But first I'm going to bombard you with debugging tips. If you know this stuff, then ignore.

Set a breakpoint? Click on the margin to the left of this line "If Me.TextProject > 0 Then". The line should now be highlighted in red. Run your code. Execution should pause on that same line with a yellow highlight. Now you can mouseover your variables and see their values.
Locals Window? With code paused, goto Menu->View->Locals shows a window where you can view all variables values in the module in which code is running.
Call Stack? goto Menu->View->Call Stack shows the list of subs and functions that were called prior to the current routine, if any. Click one of those and see locals and mouseover variables as they existed at the time that routine was run.
On your toolbar see the little blue arrows and lines? Step into, step over, step out? See what they do when execution is paused? You can execute code one line at a time and watch what changes.
Want to evaluate an expression with code paused? Highlight "Me.TextProject > 0" and mouse over your highlight. Shows the result.
Test more complicated expressions? in Menu->View->Immediate. Type "?", which means "Print" and then an expression. Displays the result or an error or whatever. In immediate also, with code paused, you can reassign values to variables. Type "varWhere = 12" without quotes. Now mouseover varWhere.

With all that in mind... what is the value of "Me.TextProject" at your breakpoint? What's the value of "varWhere"? Using the above tools you should be able to trace step-by-step each little thing your code does.

Lemme know if this helps
Cheers,

 

[manix]

Thanks Lagbolt,

Sorry but as I say I am well new to VB and Access and so all the debugging stuff currently flies over my head! I am pretty stuck with this now, I don't really understand what's going wrong!

Is there an easier way to create a Search form? That a mere mortal like me could do?

My project was coming along really well until this point and my Database is pretty rubbish if I can't query the data using a search form, because I may want to query more than one filed at a time. For example look for all tools related to Project No, located in specific location, that are of a certain status!!!!

 

[MarkK]

You can lead a horse to water, right? Did you even try to set a breakpoint?
Good luck.

 

[manix]

You can lead a horse to water, right? Did you even try to set a breakpoint?
Good luck.

Yeah you can, but if the horse is not familiar with the taste of the water then you can't make him drink it! I did set a breakpoint, you're instructions were spot on. Thing is I don't know much about VB or programming code and so I have no concept of what it was you were talking about!

What the hell does any of that stuff you were talking about mean in plain English. I don't know, so it has not helped me!

I appreciate the help you have given me and I am sorry that it has not worked! I am simply not coversant with VB and therefore my abilities are limited to reviewing what others do and fiddling around with it, learning as I go!

 

[stallyon]

You can lead a horse to water, right?

Yes but a pencil must be lead

or try

Yes but a missile must be guided

 

[manix]

Yes but a pencil must be lead

or try

Yes but a missile must be guided

Yeah thanks for your input, I did laugh a bit when I saw you were called Stallyon and your post talks about:

Lead, Pencils and Missiles!