J
jfarrell
I have a table called "skills" and I'd like to have a textbox where I can
input my search criteria string and then execute a search button that will
query all columns in my table for that specific search criteria using
wildcards at each end of the string (%txtSearch%). I'd like to send the
results to another form or report
I'm pretty new to access programming so If you have some code examples that
would be greatly appreciated.
Here is what I was trying but so far it is just opening another msgBox that
wants me to "Enter parameter value", not sure why I'm getting that message
box popup:
Private Sub cmdSearch_Click()
On Error GoTo cmdSearch_Click_err
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSearch As String
Dim strSQL As String
Set db = CurrentDb
If Not QueryExists("qrySearchQuery") Then
Set qdf = db.CreateQueryDef("qrySearchQuery")
Else
Set qdf = db.QueryDefs("qrySearchQuery")
End If
If IsNull(Me.tboSearch.Value) Then
strSearch = " LIKE '*' "
Else
strSearch = " LIKE chr(34)*" & Me.tboSearch.Value & "*chr(34)"
End If
strSQL = "SELECT skills.* " & _
"FROM skills " & _
"WHERE skills.[Operating Systems]" & strSearch & _
" OR skills.[Wireless]" & strSearch & _
" OR skills.[Other]" & strSearch & _
" ORDER BY skills.[Name];"
qdf.SQL = strSQL
DoCmd.Echo False
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qrySearchQuery")
= acObjStateOpen Then
DoCmd.Close acQuery, "qrySearchQuery"
End If
DoCmd.OpenQuery "qrySearchQuery"
cmdSearch_Click_exit:
DoCmd.Echo True
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdSearch_Click_err:
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdSearch_Click_exit
MsgBox strSQL
End Sub
input my search criteria string and then execute a search button that will
query all columns in my table for that specific search criteria using
wildcards at each end of the string (%txtSearch%). I'd like to send the
results to another form or report
I'm pretty new to access programming so If you have some code examples that
would be greatly appreciated.
Here is what I was trying but so far it is just opening another msgBox that
wants me to "Enter parameter value", not sure why I'm getting that message
box popup:
Private Sub cmdSearch_Click()
On Error GoTo cmdSearch_Click_err
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSearch As String
Dim strSQL As String
Set db = CurrentDb
If Not QueryExists("qrySearchQuery") Then
Set qdf = db.CreateQueryDef("qrySearchQuery")
Else
Set qdf = db.QueryDefs("qrySearchQuery")
End If
If IsNull(Me.tboSearch.Value) Then
strSearch = " LIKE '*' "
Else
strSearch = " LIKE chr(34)*" & Me.tboSearch.Value & "*chr(34)"
End If
strSQL = "SELECT skills.* " & _
"FROM skills " & _
"WHERE skills.[Operating Systems]" & strSearch & _
" OR skills.[Wireless]" & strSearch & _
" OR skills.[Other]" & strSearch & _
" ORDER BY skills.[Name];"
qdf.SQL = strSQL
DoCmd.Echo False
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qrySearchQuery")
= acObjStateOpen Then
DoCmd.Close acQuery, "qrySearchQuery"
End If
DoCmd.OpenQuery "qrySearchQuery"
cmdSearch_Click_exit:
DoCmd.Echo True
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdSearch_Click_err:
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdSearch_Click_exit
MsgBox strSQL
End Sub