Queries with Parameters

[Weaver]

Supposing I wanted to do something like this:

Set rsB = db.OpenRecordset("qryStuff")

only qryStuff required parameters? How do I pass the parameter values to the query?
Thanks.

[Allen Browne]

You can assign values to the Parameters of the QueryDef before you
OpenRecordset:
Dim qdf As QueryDef

Set qdf = dbEngine(0)(0).QueryDefs("qryStuff")
qdf.Parameters("SomeParameter") = 4
Set rsB = qdf.OpenRecordset()

Another alternative is to just build the SQL statement dynamically instead
of worrying about saving a QueryDef with parameters.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Weaver" <we*****@verizon.net> wrote in message
news:66**************************@posting.google.c om...

Supposing I wanted to do something like this:

Set rsB = db.OpenRecordset("qryStuff")

only qryStuff required parameters? How do I pass the parameter values to
the query?
Thanks.

[Weaver]

Thanks again Allen. But I'm not clear on a couple of things here.

Whats with this part:

dbEngine(0)(0).

I'm not familiar with that syntax.

Also, how do I cause the form to display the recordset that we're
manipulating here? Is it just:

SomeForm.Recordset = rsB
Chris.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...

You can assign values to the Parameters of the QueryDef before you
OpenRecordset:
Dim qdf As QueryDef

Set qdf = dbEngine(0)(0).QueryDefs("qryStuff")
qdf.Parameters("SomeParameter") = 4
Set rsB = qdf.OpenRecordset()

Another alternative is to just build the SQL statement dynamically instead
of worrying about saving a QueryDef with parameters.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Weaver" <we*****@verizon.net> wrote in message
news:66**************************@posting.google.c om...

Supposing I wanted to do something like this:

Set rsB = db.OpenRecordset("qryStuff")

only qryStuff required parameters? How do I pass the parameter values to
the query?
Thanks.

[Allen Browne]

dbEngine is the top of the tree in the DAO object model - the library that
Access is based on internally. Details and examples in:
DAO: Data Access Objects
at:
http://members.iinet.net.au/~allenbrowne/ser-04.html

If this is heading for a form, you could set the RecordSource of the form to
the changed query, but you don't really need the query. Instead, just build
the SQL string and assign it to the form's RecordSource. Example:
Dim strSql as String
strSql = "SELECT * FROM MyTable WHERE SomeField = 4;"
Me.RecordSource = strSql

Alternatively, you could set the Filter property of the form instead of
worrying about the parameters at all.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Weaver" <we*****@verizon.net> wrote in message
news:66**************************@posting.google.c om...

Thanks again Allen. But I'm not clear on a couple of things here.

Whats with this part:

dbEngine(0)(0).

I'm not familiar with that syntax.

Also, how do I cause the form to display the recordset that we're
manipulating here? Is it just:

SomeForm.Recordset = rsB
Chris.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...

You can assign values to the Parameters of the QueryDef before you
OpenRecordset:
Dim qdf As QueryDef

Set qdf = dbEngine(0)(0).QueryDefs("qryStuff")
qdf.Parameters("SomeParameter") = 4
Set rsB = qdf.OpenRecordset()

Another alternative is to just build the SQL statement dynamically
instead
of worrying about saving a QueryDef with parameters.
"Weaver" <we*****@verizon.net> wrote in message
news:66**************************@posting.google.c om...

> Supposing I wanted to do something like this:
>
> Set rsB = db.OpenRecordset("qryStuff")
>
> only qryStuff required parameters? How do I pass the parameter values
> to
> the query?
>
>
> Thanks.

[Weaver]

Filter is the way I'd gone from the beginning. Somehow I thought that
a parameterized query would be more efficient. I'll stay with the
Filter; it works.

In regard to dbEngine, it's the (0) (0) part that had me puzzled.

I am posting another question to this forum regarding a far more
generic question that has me stymied. I'll probably call it 'Posting
error'. Have a look if you get the chance.

Thanks,

Chris.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

dbEngine is the top of the tree in the DAO object model - the library that
Access is based on internally. Details and examples in:
DAO: Data Access Objects
at:
http://members.iinet.net.au/~allenbrowne/ser-04.html

If this is heading for a form, you could set the RecordSource of the form to
the changed query, but you don't really need the query. Instead, just build
the SQL string and assign it to the form's RecordSource. Example:
Dim strSql as String
strSql = "SELECT * FROM MyTable WHERE SomeField = 4;"
Me.RecordSource = strSql

Alternatively, you could set the Filter property of the form instead of
worrying about the parameters at all.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Weaver" <we*****@verizon.net> wrote in message
news:66**************************@posting.google.c om...

Thanks again Allen. But I'm not clear on a couple of things here.

Whats with this part:

dbEngine(0)(0).

I'm not familiar with that syntax.

Also, how do I cause the form to display the recordset that we're
manipulating here? Is it just:

SomeForm.Recordset = rsB
Chris.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...

You can assign values to the Parameters of the QueryDef before you
OpenRecordset:
Dim qdf As QueryDef

Set qdf = dbEngine(0)(0).QueryDefs("qryStuff")
qdf.Parameters("SomeParameter") = 4
Set rsB = qdf.OpenRecordset()

Another alternative is to just build the SQL statement dynamically
instead
of worrying about saving a QueryDef with parameters.
"Weaver" <we*****@verizon.net> wrote in message
news:66**************************@posting.google.c om...
> Supposing I wanted to do something like this:
>
> Set rsB = db.OpenRecordset("qryStuff")
>
> only qryStuff required parameters? How do I pass the parameter values
> to
> the query?
>
>
> Thanks.