Search Function - Syntax Error 3131

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have created a search function which at present is coming up with the
following error : Syntax Error In From Clause (Runtime Error 3131)

Access VBA is relatively new to me however, I have included the coding below
for someone to see.

Me.SearchDrawingSubF.Form.RecordSource = "SELECT * FROM DrawingSearchQ " &
BuildFilter

Me.SearchDrawingSubF.Requery

I have created a form which contains unbound text and combo boxes which when
activated should display the related information in a subform which is
situated below.

However, at present it is not working and I am puzzled as to why this is the
case.

Many thanks in advance,
Kieron White
 
What is BuildFilter?

If is is a string variable, you could add this line above the one that sets
the RecordSource:
Debug.Print BuildFilter
Then when it fails, press Ctrl+G to open the Immediate window and see what
was printed there. You can mock up a query using any criteria you like,
switch the query to SQL View (View menu), and look at the WHERE clause.
That's what you want.

Don't forget you need the word WHERE in there as well.

The Requery line is not necessary.
 
Hard to say. We don't know what BuildFilter does. From the looks of the
code it does nothing to the SQL statement since you are not returning
anything. I would have expected to see something like the following (note
the addition of "()" on the end).

Me.SearchDrawingSubF.Form.RecordSource =
"SELECT * FROM DrawingSearchQ " & BuildFilter()

Also, if you set the recordsource, that normally (always?) causes a requery
of the object (so, your requery is probably redundant).

The best way to trouble shoot is to build the string and assign it to a
variable, so you can examine it.

Dim StrSQL as String
StrSQL = "SELECT * FROM DrawingSearchQ " & BuildFilter()
Debug.Print StrSQL ' Remove this once the code is running correctly
Me.SearchDrawingSubF.Form.RecordSource = StrSQL
 
Hi,

When the macro is run, it seems to travel through BuildFilter without any
issues. I have however, included BuildFilter below which might help.

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors

' Check for LIKE Installation Number
If Me.txtInstallationtNumber > "" Then
varWhere = varWhere & "[INSTALLATIONNUMBER] LIKE """ &
Me.txtInstallationtNumber & "*"" AND "
End If

BuildFilter = varWhere

End Function

Thanks for your help

Kieron
 
Back
Top