Code to search all columns in a table for a single search word

G

Guest

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
 
G

Graham Mandeno

The problem is in the syntax of your search string:
strSearch = " LIKE chr(34)*" & Me.tboSearch.Value & "*chr(34)"
should be:
strSearch = " LIKE '*" & Me.tboSearch.Value & "*'"
or perhaps, if you might have apostrophes in your search string:
strSearch = " LIKE ""*" & Me.tboSearch.Value & "*"""

However, there is also a problem with your database design. You need to
search three fields for the same data because your tables are not normalized
and you are actually storing DATA (the name of the skill category) in field
names. What happens later when you want to add two more skill categories?
You will then need to add two more fields and change all your forms and
alter your code to search all five fields.

You should have a table for SkillTypes with two fields: SkillTypeID and
SkillTypename. Then your Skills table forms a many-to-many relationship
between the SkillTypes and the entity that has the skills (say, Employees).
It would need three fields:
EmployeeID } these two fields comprise
SkillTypeID } the primary key
SkillNotes text or memo field

Now you have only one fields to search (SkillNotes), and no future
maintenance problems.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



jfarrell said:
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
 
G

Guest

Thanks Graham for the clarification on the quotes.

As for the database design I wasn't involved in it originally and it was
actually just an import of an excel document at one point in time. So I
didn't really want to redesign the database but I guess I could.

I'll give it a try and let you know if the changes work.

Graham Mandeno said:
The problem is in the syntax of your search string:
strSearch = " LIKE chr(34)*" & Me.tboSearch.Value & "*chr(34)"
should be:
strSearch = " LIKE '*" & Me.tboSearch.Value & "*'"
or perhaps, if you might have apostrophes in your search string:
strSearch = " LIKE ""*" & Me.tboSearch.Value & "*"""

However, there is also a problem with your database design. You need to
search three fields for the same data because your tables are not normalized
and you are actually storing DATA (the name of the skill category) in field
names. What happens later when you want to add two more skill categories?
You will then need to add two more fields and change all your forms and
alter your code to search all five fields.

You should have a table for SkillTypes with two fields: SkillTypeID and
SkillTypename. Then your Skills table forms a many-to-many relationship
between the SkillTypes and the entity that has the skills (say, Employees).
It would need three fields:
EmployeeID } these two fields comprise
SkillTypeID } the primary key
SkillNotes text or memo field

Now you have only one fields to search (SkillNotes), and no future
maintenance problems.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



jfarrell said:
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
 
G

Guest

I changed the "strSearch" as you recommended:

strSearch = " LIKE ""*" & Me.tboSearch.Value & "*"""

Now I get a msgbox that comes up with the following:

An unexpected error has occurred.
Please note of the following details:
Error Number: 3008
Description: the table 'skills' is already opened exclusively by another
user, or it is already open through the user interface and cannot be
manipulated programmatically.

Not sure why I'm getting this error.
 
G

Graham Mandeno

The only line that could be raising that error is:
DoCmd.OpenQuery "qrySearchQuery"

Have you tried opening qrySearchQuery manually from the database window?
That might give a clue as to the nature of the problem.

The "another user" part of the error message is misleading. Nine times out
of ten *you* are the other user. Do you have an open form which is based on
the Skills table, which perhaps has its RecordLocks property set to "All
Records", or maybe that form has a dirty record which has not yet been
saved.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top