Search Function

  • Thread starter Thread starter AccessVBANewbie via AccessMonster.com
  • Start date Start date
A

AccessVBANewbie via AccessMonster.com

I have tried to create a search function which allows users to search for
information regarding a particular case if he/she types in the case number.
My codings are something like

Dim sqlStr As String
sqlStr = "SELECT * From GeneralSummary WHERE PRT_Number Like " & "'*" &
txtSearchPartNo & "'"
DoCmd.RunSQL sqlStr

but an error message occurred saying that the RunSQL action should consist of
an argument using a SQL statement. How should i solve this problem?
 
AccessVBANewbie said:
I have tried to create a search function which allows users to search
for information regarding a particular case if he/she types in the
case number. My codings are something like

Dim sqlStr As String
sqlStr = "SELECT * From GeneralSummary WHERE PRT_Number Like " & "'*"
& txtSearchPartNo & "'"
DoCmd.RunSQL sqlStr

but an error message occurred saying that the RunSQL action should
consist of an argument using a SQL statement. How should i solve this
problem?
Click to expand...

RunSQL is only for "action" queries that either append, update, or delete
records. What would you want to do after perfoming the search? You could just
apply a filter to a form, do a search in the form, or use a Recordset in code,
but what do you want to show the user after the search is performed?
 
oh i see, thanx rick for enlightening me on that, I totally misunderstood the
RunSQL action. What i wanna do is to show the results of wat a user has
searched for in a listbox. So i could just use a filter instead? How could i
go about doing that?

Rick said:
I have tried to create a search function which allows users to search
for information regarding a particular case if he/she types in the
Click to expand...
[quoted text clipped - 8 lines]
consist of an argument using a SQL statement. How should i solve this
problem?
Click to expand...

RunSQL is only for "action" queries that either append, update, or delete
records. What would you want to do after perfoming the search? You could just
apply a filter to a form, do a search in the form, or use a Recordset in code,
but what do you want to show the user after the search is performed?
Click to expand...
 
AccessVBANewbie said:
oh i see, thanx rick for enlightening me on that, I totally
misunderstood the RunSQL action. What i wanna do is to show the
results of wat a user has searched for in a listbox. So i could just
use a filter instead? How could i go about doing that?
Click to expand...

For that I would just change the RowSource SQL statement of the ListBox.

You would have a string variable that is the basic SELECT for the ListBox...

strSQL = "SELECT * FROM TableName"

If the user wants a search (for example) "City = 'New York'" then you have
code...

Me.ListBox.RowSource = strSQL & " WHERE City = 'New York'"

The ListBox will automatically reflect the new SQL statement after it is
assigned.
 
Back
Top