Access queries: passing parameters to a query like a where clause 1

[pankajdaga]

Hi everyone,


I am an Access n00b.


I have been trying this for hours and to no avail. I do not know why
this should be so difficult!


I have a query in Access say something simple like:


Select * from Employees


All I want to do is pass it a parameter like:


Select * from Employee where Name = "My name"


How can I achieve something so trivial from VBA? I was hoping that a
runSQL command with the query name and the WHERE clause would do it,
but apparantly not.


Thanks!
xarg

 

[jadams0173]

Here is one way to open the recordset

Function openrs()

Dim rs  As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

Set cn = CurrentProject.Connection

Set rs = New ADODB.Recordset

    With rs
        .Open "Select * from tblTransactions where customer='Sun'", cn, adOpenStatic, adLockReadOnly, adCmdText
    End With
    
Debug.Print rs.RecordCount
rs.close
set rs = nothing
End Function

You will need to make a reference to your Microsoft ActiveX Data Object libraries in you references.

 

[pankajdaga]

Hi,

Thanks for the reply.

However, I need to use it on an Access query. The SQL statement was just an exmaple. So, I have a complex query with some joins and I want to filter it based on a certain column(s).

So, what I need to do is pass a parameter to an Access query that will filter the resultset on that parameter.

Thanks,
xarg

 

[PHV]

If the bottom line is a form or a report based on the query then have a look at the 4th argument of the OpenForm/OpenReport method of the DoCmd object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

Like PHV said the easiest way would be to build a form from your query. It could be a datasheet view, and thus it would look like any other query view. Then like he said look at

Docmd.openForm "formName",,,"your criteria"

Your criteria looks something like a where statement without the word "where" ex
"system ID = 10"
or with variables
"systemID = " & intSystemNumber
or if it is a string variable you have to include single quotes
"systemName = 'Machine One'"
or with variables
"systemName = '" & strSystemName & "'