Search Form

Hi I tried to adapt an example of a search form and Im getting a Syntax error. Ive attached the file so that hopefully someone can point me in the right direction. To get to the error open the file select 'SEARCH' write any letter in the 'EXERCISE NAME' field. then press search then it gives a Syntax error. All help gratfully received. I realise I need to set it up to search all the other criteria but I was trying to get one working first. Thanks!

Could really use some help on this, anyone, pleaze!!!!

this is where your error is:
Me.Results.Form.RecordSource = "SELECT * FROM qryResults " & BuildFilter

so something is wrong with your query..i've changed it to:

'cmdSearch was previously command53
'TxtExerciseName was previously ExerciseName
'Label your command buttons and controls before you do any coding, it will
'save you lot of trouble during testing and troubleshooting
'note: qryResults and function BuildFilter not used

Private Sub cmdSearch_Click()
Dim strSearch As String
quot = """"
'strSearch is the string that contains your query, it also contains the WHERE clause
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.Exercise_Name FROM tblTechnicalIncidentReport " & _
"WHERE Exercise_Name = " & quot & Me.TxtExerciseName.Value & quot & ";" 'WHERE clause here captures the user input in the textbox TxtExerciseName
' Update the record source
Me.Results.Form.RecordSource = strSearch

' Requery the subform
Me.Results.Requery

End Sub

sorry i'm working from office and i don't have a zip utility so can't upload the updated file. and abt ur previous code..i believe because u declared varWhere and BuildFilter as Variants and sql statement are in String. Furthermore i doubt that is the correct way to to make an SQL statement hence the error because its not a valid SQL statement. I used the above method in my application and its working fine. For the rest of your criteria, you have to keep writing strSearch and add on to the main statement. e.g.

' not isnull checks whether user has keyed in input, if no input that part of the statement is not included
If not isnull(me.controlname1) Then
quot = """"
strSearch = strSearch & "AND columnname1 = " & quot & me.controlname1.value & quot

end if

If not isnull(me.controlname2) Then
quot = """"
strSearch = strSearch & "AND columnname2 = " & quot & me.controlname2.value & quot

end if

and so on..

you can also take a look at this site: http://jfconsult.home.comcast.net/tips/
it has a sample database that uses a search function. you can open it up and take a look at the codes.

Cheers
Eric

Thanks for your reply, ive applied your suggestion, (did a copy and paste) but unfortunatly its not calling any results. I'm clearly missing something. Any suggestions?

Hi ddrew,

1st what did u enter in the textbox? did u enter a partial name or did u enter a full name? if its the former than it won't work because i've set it up assuming that the user knows the full name which perhaps is an oversight on my part, because i just realized you have lots of different values for "Excercise_Name".

It will be good also if u could tell us what are ur targetted users so it will be easier to design an interface to suit them

and here is the code which is better, it caters for both partial and full matches, i initially had trouble because SQL uses % for the like operator but access uses *, only found out from the help file do look through the help file good luck for ur project

Private Sub cmdSearch_Click()
Dim strSearch As String

'strSearch is the string that contains your query, it also contains the WHERE clause
If IsNull(Me.txtExerciseName) Then 'this is to ensure user keys/select at least 1 criteria; add on rest of the controlnames as desired
MsgBox "Please enter at least one criteria"

Else
quot = """"
If Not IsNull(Me.txtExerciseName) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.Exercise_Name FROM tblTechnicalIncidentReport " & _
"WHERE Exercise_Name LIKE " & quot & Me.txtExerciseName.Value & "*" & quot & ";" 'WHERE clause here captures the user input in the textbox TxtExerciseName
End If
'add on more if & end if loops for each controlname(criteria) that you have
Me.Results.Form.RecordSource = strSearch ' Update the record source, this line is to be placed at the end of all your criteria's if end if codes

' Requery the subform
Me.Results.Requery
End If
End Sub

Still having problems with this, I put in a new If statement as you suggested and it produces an error. This is my code as it stands now: In Red is where the error comes. It says "Characters found after teh end of SQL statement"

Private Sub cmdSearch_Click()
Dim strSearch As String

'strSearch is the string that contains your query, it also contains the WHERE clause
If IsNull(Me.txtExerciseName) Then 'this is to ensure user keys/select at least 1 criteria; add on rest of the controlnames as desired
MsgBox "Please enter at least one criteria"

Else
quot = """"
If Not IsNull(Me.txtExerciseName) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.Exercise_Name FROM tblTechnicalIncidentReport " & _
"WHERE Exercise_Name LIKE " & quot & Me.txtExerciseName.Value & "*" & quot & ";" 'WHERE clause here captures the user input in the textbox TxtExerciseName
End If
'add on more if & end if loops for each controlname(criteria) that you have
If Not IsNull(Me.txtBoxNo) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.BoxNo FROM tblTechnicalIncidentReport " & _
"WHERE BoxNo LIKE " & quot & Me.txtBoxNo.Value & "*" & quot & ";" 'WHERE clause here captures the user input in the textbox TxtBoxNo
End If

Me.Results.Form.RecordSource = strSearch ' Update the record source, this line is to be placed at the end of all your criteria's if end if codesRequery the subform
Me.Results.Requery
End If

End Sub

ddrew said:

Still having problems with this, I put in a new If statement as you suggested and it produces an error. This is my code as it stands now: In Red is where the error comes. It says "Characters found after teh end of SQL statement"




quot = """"
If Not IsNull(Me.txtExerciseName) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.Exercise_Name FROM tblTechnicalIncidentReport " & _
"WHERE Exercise_Name LIKE " & quot & Me.txtExerciseName.Value & "*" & quot & ";" 'WHERE clause here captures the user input in the textbox TxtExerciseName
End If
'add on more if & end if loops for each controlname(criteria) that you have
If Not IsNull(Me.txtBoxNo) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.BoxNo FROM tblTechnicalIncidentReport " & _
"WHERE BoxNo LIKE " & quot & Me.txtBoxNo.Value & "*" & quot & ";" 'WHERE clause here captures the user input in the textbox TxtBoxNo
End If

Me.Results.Form.RecordSource = strSearch ' Update the record source, this line is to be placed at the end of all your criteria's if end if codesRequery the subform
Me.Results.Requery
End If

End Sub

Click to expand...

ok lets focus on ur search filter..which is actually the key element to any search form.

1st: why are 2 instances of WHERE? do u know how does SQL statements function? in the WHERE clause, the 1st criteria is the only one that has WHERE, subsequent criteria uses AND
e.g.
SELECT .....
FROM ....
WHERE tableA.column1 = "X" AND tableA.column2 = "X" AND tableB.column3 = "Z"

in the 2nd piece of code i posted, i again omitted something(my bad), i assumed the excercise name is a mandatory field so i used a "WHERE" statement in it while the rest should be using "AND" since the user should be able to key in multiple criteria

which secondly brings me to yet another question:
2nd: how would u expect your user to enter/select criteria?
would they be selecting/entering ONLY ONE criteria for every search?
or would they be able to enter ONE OR MORE criteria and then search, i.e. multiple criteria search?
if its the former than i think it would be better using the method of case & select. if its the latter then the method i stated would work.

what the method i listed out does is:
For every user input, the "If not isnull" condition is met, thus VBA executes the statement and adds the corresponding strSearch to the original strSearch. it continues to add on as long the condition is met, if it isn't, it skips and moves on to the next "if end if" statement until the last.

so in your case(assuming user can enter 1 or more criteria for each search), you have 8 criteria for the user to enter with:

'this is assuming that columnA is the mandatory column to show regardless of what user input, it could be "Analyst_Name"
strSearch = "SELECT columnX FROM tblTechnicalIncidentReport WHERE tblTechnicalIncidentReport.Analyst_Name Is Not Null"

If IsNull(Me.txtExerciseName) Then 'this is to ensure user keys/select at least 1 criteria; add on rest of the controlnames as desired
MsgBox "Please enter at least one criteria"

Else
quot = """"

If Not IsNull(Me.txtExerciseName) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.Exercise_Name FROM tblTechnicalIncidentReport " & _
"AND Exercise_Name LIKE " & quot & Me.txtExerciseName.Value & "*" & quot & ";" 'AND clause here captures the user input in the textbox TxtExerciseName
End If

'add on more if & end if loops for each controlname(criteria) that you have, with AND in front of them
If Not IsNull(Me.txtBoxNo) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.BoxNo FROM tblTechnicalIncidentReport " & _
"AND BoxNo LIKE " & quot & Me.txtBoxNo.Value & "*" & quot & ";" 'AND clause here captures the user input in the textbox TxtBoxNo
End If

'rest of ur controls, each with its own If end if
'rest of the code..

ok so how does it work? if controlname1 is not null, the AND clause is added to the 1st strSearch(strSearch = "SELECT columnX FROM tblTechnicalIncidentReport WHERE tblTechnicalIncidentReport.Analyst_Name Is Not Null")

if controlname2 is not null, it continues to add to the previous entry of strSearch. so let say ur user enters 3 criteria, controlname1, 2 and 4

so strSearch = "SELECT columnX FROM tblTechnicalIncidentReport WHERE tblTechnicalIncidentReport.Analyst_Name Is Not Null AND columnA = controlname1's value AND columnB = controlname2's value AND columnC = controlname4's value"

i hope this is clear enough...try not to copy code blindly..try to understand how it works 1st.

Cheers

Thanks for being so patient with me. Your being very helpful. trust me when I say that I am trying to understand. This is my code now:


The piesce that I have coloured red brings up a Syntax error. This is the piece that I cant get my head around
Private Sub cmdSearch_Click()
Dim strSearch As String

If IsNull(Me.txtExerciseName) Then 'this is to ensure user keys/select at least 1 criteria; add on rest of the controlnames as desired
MsgBox "Please enter at least one criteria"

Else
quot = """"

If Not IsNull(Me.txtExerciseName) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.Exercise_Name FROM tblTechnicalIncidentReport " & _
"AND Exercise_Name LIKE " & quot & Me.txtExerciseName.Value & "*" & quot & ";" 'AND clause here captures the user input in the textbox TxtExerciseName
End If

'add on more if & end if loops for each controlname(criteria) that you have, with AND in front of them
If Not IsNull(Me.txtBoxNo) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.BoxNo FROM tblTechnicalIncidentReport " & _
"AND BoxNo LIKE " & quot & Me.txtBoxNo.Value & "*" & quot & ";" 'AND clause here captures the user input in the textbox TxtBoxNo
End If

Me.Results.Form.RecordSource = strSearch ' Update the record source, this line is to be placed at the end of all your criteria's if end if codes


' Requery the subform
Me.Results.Requery
End If


End Sub

Try this:

note: Any SQL statement's criteria MUST start with "WHERE" as i mentioned in my previous post. The sql statement generated by ur code is:

'assuming both Exercise_Name and boxno was filled up by u
SELECT tblTechnicalIncidentReport.Exercise_Name FROM tblTechnicalIncidentReport AND Exercise_Name LIKE "user input*"; SELECT tblTechnicalIncidentReport.BoxNo AND BoxNo LIKE "user input*";

1st syntax error: ur 1st criteria does not use the keyword "WHERE"
2nd syntax error: u have two instances of ";"
";" denotes the end of a sql statement in access, so in every statement it should only appear once, right at the end of everything.
3rd syntax error: You have two "SELECT" keywords being used..i.e. 2 SELECT statements within the same query is wrong...

finally i'm guessing u havent figured this out.. "&" means contencate in VBA, the spelling might be wrong. basically if x=1 and y=2, X&Y=12
so if strSearch = "SELECT X FROM Y"
and later if u declare again strSearch = strSearch & "SELECT A FROM B"

the final strSearch will be: "SELECT X FROM Y SELECT A FROM B"
which is wrong.

hope this helps to understand better

OK this is where I am right now:

Whilst it carries out the search OK, it only shows the the Exercise_Name in the results form. All the other coloums show #Name?

I think I'm only this bit missing!!!

Private Sub cmdSearch_Click()
Dim strSearch As String

If IsNull(Me.txtExerciseName) Then 'this is to ensure user keys/select at least 1 criteria; add on rest of the controlnames as desired
MsgBox "Please enter at least one criteria"

Else
quot = """"

If Not IsNull(Me.txtExerciseName) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.Exercise_Name FROM tblTechnicalIncidentReport " & _
"WHERE Exercise_Name LIKE " & quot & Me.txtExerciseName.Value & "*" & quot & _
"AND BoxNo LIKE " & quot & Me.txtBoxNo.Value & "*" & quot & _
"AND Fault LIKE " & quot & Me.TxtFault.Value & "*" & quot & _
"AND Catagory LIKE " & quot & Me.txtCatagory.Value & "*" & quot & ";"

End If


Me.Results.Form.RecordSource = strSearch ' Update the record source, this line is to be placed at the end of all your criteria's if end if codes


' Requery the subform
Me.Results.Requery
End If


End Sub

SELECT tblTechnicalIncidentReport.Exercise_Name

you only selected ONE field to display from your table, of course it shows only one field!!! you need to add more fields to it. if they are all from the same table then u can use

SELECT fieldname1, fieldname2, fieldname3, fieldname4 FROM tablename

u don't need to affix the tablename to each fieldname if they are all from the same table.

and i suggest u read up on SQL before continuning on your project because its going to get more confusing later if u don't

edit: note that your excercise name starts with a WHERE so your assuming exercise name is the mandatory criteria that the user must key in no matter what.

Cire, just to say a huge thank you for time pataince and understanding. With your help I belive Ive gone a long way to understandinf the SQL, (loads more to learn though) Hopefully now that I have a basic format I can move away from standard querries. Again a huge thanks!!

no problem
if u find VBA a little too steep, there other example databases that don't use VBA but i don't have any links to them atm...