Form Search? (1 Viewer)

[jackie77]

Hi everyone

I am new to the forum and in fact new to access and VBA so I am apologize in advance for my stupidity. I am currently adapting a search example which was very kindly placed on this forum by Gromit called Search Form Example (Posted 12-28-2006) but I am having difficulty getting it to work the error message “Run-time error ‘3131’: Syntax error in FROM clause” the code I am using is:
-------------------------------------------------------------
Option Compare Database
Option Explicit
Private Sub btnClear_Click()
Dim intIndex As Integer
' Clear all search items
Me.txtBooked_Date = ""
Me.cmbEngineer = 0

End Sub

Private Sub btnSearch_Click()
' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM jobsheetquery " & BuildFilter
' Requery the subform
Me.frmsubClients.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 LIKE Booked Date
If Me.txtBooked_Date > "" Then
varWhere = varWhere & "[Booked_Date] LIKE """ & Me.txtBooked_Date & "*"" AND "
End If
' Check for Engineer
If Me.cmbEngineer > 0 Then
varWhere = varWhere & "[Engineer] = " & Me.cmbEngineer & " AND "
End If
BuildFilter = varWhere
End Function
----------------------------------------------------
Any advice would be most appreciated
Thanks
Jackie

 

[TjS]

Jacky,

I am quite new too, but can you explain to me where the 'cursor' went after you clicked [ok] upon the error? Usually access visualize in the code (red) what part of the code is wrong.

In most cases a sample code need to be rearrange before another person can use it

 

[jackie77]

Hi TjS

Thanks for reply,

sorry I should have added the error has highlighted the following code:

Me.frmsubClients.Form.RecordSource = "SELECT * FROM jobsheetquery " & BuildFilter

hope this helps

Jackie

 

[jackie77]

Can anyone out there give me any advice please

 

[Sleekmac]

Jackie - Try this instead:

Me.frmsubClients.Form.RecordSource = "SELECT * FROM jobsheetquery '" & BuildFilter & "'"

Note - you need to put single quotes "around" the variable you are concatenating with your string, in order for the result to be a string.

To see what I mean, try this:

dim x as string
x="SELECT * FROM jobsheetquery " & BuildFilter
debug.print x

You'll see that x is not a properly formatted SQL query string.

Hope this helps,
Sleekmac

 

[jackie77]

Hi there

Thanks for the reply, unfortunately I still can't manage to get it to work I have tinkered with it but I think I am making it worse the message I am getting now is ' Run-time Error '13' Type mismatch '

The code I have now is:

--------------

Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me.txtBooked_Date = ""
Me.cmbEngineer = 0


End Sub

Private Sub btnSearch_Click()

' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM jobsheetquery '" & BuildFilter & "'"
' Requery the subform

Me.frmsubClients.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 LIKE Booked Date
If Me.txtBooked_Date > "" Then
varWhere = varWhere & "Booked_Date" Like """ & Me.txtBooked_Date & " * "" And ""
End If


' Check for Engineer
If Me.cmbEngineer > 0 Then
varWhere = varWhere & "Engineer" = " & Me.cmbEngineer & " And """"""
End If

BuildFilter = varWhere

End Function

-----------------

Jackie

 

[TjS]

varWhere = varWhere & "Booked_Date" Like "[COLOR="Red"]"[/COLOR]" & Me.txtBooked_Date & " * "" And ""

I think the quotes (") in red are too much