Here's the entire sub. Any idead what I'm doing wrong? You can see
the other thing's I tried edited to just notes because they didn't
work. I also changed it a little bit so I could atleast get the query
to open in a datasheet and go from there. It's suppose to have the
combo box as the criteria in the WHERE part of the sql but I just
changed it to the field name so I could try and get it to work.
Private Sub btnSearch_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Dim strMessageBox As String
Dim strTitleBox As String
Dim strSearch As String
Dim strCriteria As String
Dim strSQL As String
strMessageBox = "Enter search information or choose search
criteria" strTitleBox = "Error"
If txtSearch <> "" And cboCriteria <> "" Then
If cboCriteria = "Name" Then
strCriteria = "OBRResources.[FFL Name]"
ElseIf cboCriteria = "Number" Then
strCriteria = "OBRResources.[FFL Number]"
ElseIf cboCriteria = "Roll" Then
strCriteria = "OBRResources.[Roll]"
End If
strSearch = txtSearch
strSQL = "SELECT *" & _
"FROM OBRResources " & _
"WHERE [Name] = '" & Me.txtSearch & "';"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
'DoCmd.RunSQL
'DoCmd.OpenQuery "OBRResources Query", , acReadOnly
'DoCmd.OpenQuery "strSQL", , acReadOnly
'CurrentDb.Execute strSQL
Else
response = MsgBox(strMessageBox, vbOKOnly, strTitleBox)
End If
End Sub
Klatuu said:
sorry, I misread your original post. I missed where you correctly
named the control.
Anyway, I would suggest building the SQL statement in code. Since
you want to display it in datasheet mode, it will need to be a
recordset, so:
Dim strSQL As String
strSQL = "SELECT * FROM sometable WHERE somefield = '" &
Me.txtSearch & _ & "';"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
That is the basics. you can select the fields in the select
statement based on whaty you want to see. Also, the above SQL is
defined for searching a text field. If it is numeric, remove the
single quotes. If it is a date field, replace them with #
:
I just want a user to be able to select one of three rows to search
from the combo box I have set up and enter what the want to search
for in the txtSearch box and when they press the btnSearch button,
have the results pop up in a read only data sheet.
BTW, it is txtSearch, I was just putting the information into a
string variable to be used in the sql code.
:
The RunSQL method is only for Action queries (Append, Update,
Delete, MakeTable).
If you want to use a stored query with your code, there are a
couple of ways you can do this depending on where you are going to
use the query. If you are going to use it as a recordset, you can
do something like this: Set qdf =
dbs.QueryDefs("qselMonsterActiveInactive") qdf.Parameters(0) =
strMactivity Set rst = qdf.OpenRecordset(dbOpenSnapshot,
dbReadOnly)
Or, you can do it like this with a control on a form, but not with
a memory variable:
Put this is the criteria row for the field you want to filter on:
Forms!MyFormName!strSearch
(strSearch is not a good name for a control on a form) str is a
standard prefix for string variables. A text box control normally
is prefaced with txt, so txtSearch would be better.
:
:
It's all in the SQL recognition of data: Change your select
query from
SELECT tablName.*
FROM tblName
WHERE tblFieldA1 = strSearch
to
DoCmd.RunSQL "SELECT tablName.* FROM tblName WHERE tblFieldA1 =
'" & strSearch & "'"
Because it's not clear; the equals is followed by: space, single
quote, double quote, space, &...&, space, double quote, single
quote, double quote.
HTH,
Sam
rc51wv wrote:
I want to be able to run an Access query using variables in the
VBA code I have.
the SQL setup in the query is
SELECT tablName.*
FROM tblName
WHERE tblFieldA1 = strSearch
The for has a text box named strSearch but when I press the
search button on the form and the query runs, a dialog box pops
open asking for the information to search for. How do I get it
to recognize the information in txtSearch (which I convert to a
string, strSearch).
Is there a way to build the query entirely with VBA so it will
pickup the variables? It won't let me do doCmd.RunSQL -sql
statement- because from everything I've read, SELECT isn't a
run able sql action.
--
Sam
Message posted via AccessMonster.com
Here is the VBA code
strSQL = "SELECT OBRResources.*" & _
"FROM OBRResources " & _
"WHERE " & strCriteria & " = '" & strSearch &
"';"
DoCmd.RunSQL strSQL
The original SQL I posted up top was an example of how it was
setup in Access in the actual query where you right click and
view SQL that has none of the information in quotes.
When I try running it with the VBA code instead using
DoCmd.OpenQuery "Query1"
it pops up with the error
Run-time error '2342':
A RunSQL action requires an arguement consisting of an SQL
statement
That's when I put the SQL with a SELECT statement into a string
and then try and run it with a doCmd.RunSQL. I have the syntax
correct I believe, so I doubt it's that unless it's something
else I'm overlooking.
