I am working on an access database, writing a dynamic search form.
Code that builds that dynamic search query...
*.........................................................*
Private Sub cmdSearch_Click()
Dim db As Database
Dim rst As Recordset
gstrWhereRec = ""
If IsNothing(Me!searchField) Then
MsgBox "No criteria specified.", vbExclamation
Forms!Search_Form!searchField.SetFocus
Exit Sub
ElseIf IsNothing(Me!searchTerm) Then
MsgBox "Please enter a search term.", vbExclamation
Forms!Search_Form!searchTerm.SetFocus
Exit Sub
ElseIf Me!searchField = "XRD Sample Name:" Then
gstrWhereRec = "XRD_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Field Sample Name:" Then
gstrWhereRec = "Field_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Maximum CRS:" Then
gstrWhereRec = "Max_CRS LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Dominant Mineral(s):" Then
gstrWhereRec = "Mineral_Dominant LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Moderate Mineral(s):" Then
gstrWhereRec = "Mineral_Moderate LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Trace Mineral(s):" Then
gstrWhereRec = "Mineral_Trace LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Mineral(s):" Then
gstrWhereRec = "Mineral_Possible LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Low-Angle Mineral(s):" Then
gstrWhereRec = "Mineral_Possible_Low_Angle LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Computer Search Match Possibilities:" Then
gstrWhereRec = "Mineral_Match_Possibilities LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
End If
If Me!searchType = 2 And Not IsNothing(Me!searchTerm2) Then
If Me!SearchField2 = "XRD Sample Name:" Then
gstrWhereRec = gstrWhereRec & " AND XRD_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Field Sample Name:" Then
gstrWhereRec = gstrWhereRec & " AND Field_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Maximum CRS:" Then
gstrWhereRec = gstrWhereRec & " AND Max_CRS LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Dominant Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Dominant LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Moderate Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Moderate LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Trace Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Trace LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Possible LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Low-Angle Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Possible_Low_Angle LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Computer Search Match Possibilities:" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Match_Possibilities LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
End If
ElseIf Me!searchType = 3 And Not IsNothing(Me!searchTerm2) Then
If Me!SearchField2 = "XRD Sample Name:" Then
gstrWhereRec = gstrWhereRec & " OR XRD_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Field Sample Name:" Then
gstrWhereRec = gstrWhereRec & " OR Field_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Maximum CRS:" Then
gstrWhereRec = gstrWhereRec & " OR Max_CRS LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Dominant Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Dominant LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Moderate Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Moderate LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Trace Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Trace LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Possible LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Low-Angle Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Possible_Low_Angle LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Computer Search Match Possibilities:" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Match_Possibilities LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
End If
ElseIf Not IsNothing(Me!SearchField2) And IsNothing(Me!searchTerm2) Then
MsgBox "Please enter a search term.", vbExclamation
Forms!Search_Form!searchTerm2.SetFocus
Exit Sub
End If
'Turn on Hourglass
Me.Visible = False
DoCmd.Hourglass True
Dim SearchString As String
SearchString = "SELECT * FROM XRD_Sands_All_Upgradeable WHERE XRD_Sample LIKE " & gstrWhereRec & ";"
Set db = CurrentDb
Set rst = db.OpenRecordset(SearchString)
DoCmd.Hourglass False
'If none found, then inform user and make form visible to try again
If rst.RecordCount = 0 Then
MsgBox "No publications meet your criteria.", vbInformation
gstrWhereRec = ""
rst.Close
Me.Visible = True
Exit Sub
End If
DoCmd.OpenReport ReportName:="XRD_Search_Results", View:=acViewPreview, WhereCondition:=gstrWhereRec
Me!searchField = Null
Me!searchTerm = Null
Me!SearchField2 = Null
Me!searchTerm2 = Null
End Sub
*---------------------------------------------------------*
I get my error with this piece of code...
Dim SearchString As String
SearchString = "SELECT * FROM XRD_Sands_All_Upgradeable WHERE XRD_Sample LIKE " & gstrWhereRec & ";"
Set db = CurrentDb
Set rst = db.OpenRecordset(SearchString)
Specifically the "Set rst..."
The recordset and the database object are initialized at the begining of the routine...
gstrWhereRec is initialized as a global string using a module...
I keep getting an error that says I have a datatype mismatch when I try to search for a record...All the fields in my table are text fields....
Can anyone spot the error, I've been looking at it for three days....
Thanks,
Pesteo....
Code that builds that dynamic search query...
*.........................................................*
Private Sub cmdSearch_Click()
Dim db As Database
Dim rst As Recordset
gstrWhereRec = ""
If IsNothing(Me!searchField) Then
MsgBox "No criteria specified.", vbExclamation
Forms!Search_Form!searchField.SetFocus
Exit Sub
ElseIf IsNothing(Me!searchTerm) Then
MsgBox "Please enter a search term.", vbExclamation
Forms!Search_Form!searchTerm.SetFocus
Exit Sub
ElseIf Me!searchField = "XRD Sample Name:" Then
gstrWhereRec = "XRD_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Field Sample Name:" Then
gstrWhereRec = "Field_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Maximum CRS:" Then
gstrWhereRec = "Max_CRS LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Dominant Mineral(s):" Then
gstrWhereRec = "Mineral_Dominant LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Moderate Mineral(s):" Then
gstrWhereRec = "Mineral_Moderate LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Trace Mineral(s):" Then
gstrWhereRec = "Mineral_Trace LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Mineral(s):" Then
gstrWhereRec = "Mineral_Possible LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Low-Angle Mineral(s):" Then
gstrWhereRec = "Mineral_Possible_Low_Angle LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
ElseIf Me!searchField = "Computer Search Match Possibilities:" Then
gstrWhereRec = "Mineral_Match_Possibilities LIKE " & Chr$(34) & "*" & Me!searchTerm & "*" & Chr$(34)
End If
If Me!searchType = 2 And Not IsNothing(Me!searchTerm2) Then
If Me!SearchField2 = "XRD Sample Name:" Then
gstrWhereRec = gstrWhereRec & " AND XRD_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Field Sample Name:" Then
gstrWhereRec = gstrWhereRec & " AND Field_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Maximum CRS:" Then
gstrWhereRec = gstrWhereRec & " AND Max_CRS LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Dominant Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Dominant LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Moderate Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Moderate LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Trace Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Trace LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Possible LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Low-Angle Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Possible_Low_Angle LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Computer Search Match Possibilities:" Then
gstrWhereRec = gstrWhereRec & " AND Mineral_Match_Possibilities LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
End If
ElseIf Me!searchType = 3 And Not IsNothing(Me!searchTerm2) Then
If Me!SearchField2 = "XRD Sample Name:" Then
gstrWhereRec = gstrWhereRec & " OR XRD_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Field Sample Name:" Then
gstrWhereRec = gstrWhereRec & " OR Field_Sample LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Maximum CRS:" Then
gstrWhereRec = gstrWhereRec & " OR Max_CRS LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Dominant Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Dominant LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Moderate Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Moderate LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Trace Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Trace LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Possible LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Possible Low-Angle Mineral(s):" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Possible_Low_Angle LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
ElseIf Me!searchField = "Computer Search Match Possibilities:" Then
gstrWhereRec = gstrWhereRec & " OR Mineral_Match_Possibilities LIKE " & Chr$(34) & "*" & Me!searchTerm2 & "*" & Chr$(34)
End If
ElseIf Not IsNothing(Me!SearchField2) And IsNothing(Me!searchTerm2) Then
MsgBox "Please enter a search term.", vbExclamation
Forms!Search_Form!searchTerm2.SetFocus
Exit Sub
End If
'Turn on Hourglass
Me.Visible = False
DoCmd.Hourglass True
Dim SearchString As String
SearchString = "SELECT * FROM XRD_Sands_All_Upgradeable WHERE XRD_Sample LIKE " & gstrWhereRec & ";"
Set db = CurrentDb
Set rst = db.OpenRecordset(SearchString)
DoCmd.Hourglass False
'If none found, then inform user and make form visible to try again
If rst.RecordCount = 0 Then
MsgBox "No publications meet your criteria.", vbInformation
gstrWhereRec = ""
rst.Close
Me.Visible = True
Exit Sub
End If
DoCmd.OpenReport ReportName:="XRD_Search_Results", View:=acViewPreview, WhereCondition:=gstrWhereRec
Me!searchField = Null
Me!searchTerm = Null
Me!SearchField2 = Null
Me!searchTerm2 = Null
End Sub
*---------------------------------------------------------*
I get my error with this piece of code...
Dim SearchString As String
SearchString = "SELECT * FROM XRD_Sands_All_Upgradeable WHERE XRD_Sample LIKE " & gstrWhereRec & ";"
Set db = CurrentDb
Set rst = db.OpenRecordset(SearchString)
Specifically the "Set rst..."
The recordset and the database object are initialized at the begining of the routine...
gstrWhereRec is initialized as a global string using a module...
I keep getting an error that says I have a datatype mismatch when I try to search for a record...All the fields in my table are text fields....
Can anyone spot the error, I've been looking at it for three days....
Thanks,
Pesteo....