passing a parameter to a querry using VBA code

[Patrick]

HI!!

I would like to know how to pass a parameter(from VBA) to
a query I built using the query wizard tool in access.

Here's what I've got so far:

1.
Query name: qrFirstTry
content of query:

PARAMETERS Pnbr Text ( 255 );
SELECT ClientName,Address,City,Province,ClientNo
FROM Clients
WHERE ClientNo=[Pnbr];

2. some code (Don't know if its any good)
Dim Number As Double
Dim prmNumber As Parameter
Dim cmd As Command

Number = Text9.Value

Set prmNumber = cmd.CreateParameter
("EssaiPourPasserParametre", adVarChar, , , Number)
cmd.Parameters.Append prmNumber

When I try this code it gives me an error message.

What I would like to do is capture the content of a
textBox place it in the variable 'Number', then send this
variable inside my query(as a parameter) and execute my
query(qrFirstTry).

Other question:
Can I see the result of the query and/or can I capture the
results in a recordSet for further analysis.

Can anyone help me?

Pat

 

[TC]

Here is how you would generate a recordset from a query that expected two
parameters, P1 and P2.

(untested)

dim db as database, qd as querydef, rs as recordset
set rs = currentdb()
set qd = db.querydefs![YourQueryNameHere]
qd.parameters![P1] = 123
qd.parameters![P2] = "abc"
set rs = qd.openrecordset()
' do things with rs here.
qd.close
set qd = nothing
set rs = nothing
set db = nothing

HTH,
TC

 

[TC]

oops, set DB = currentdb (of course).

TC

Here is how you would generate a recordset from a query that expected two
parameters, P1 and P2.

(untested)

dim db as database, qd as querydef, rs as recordset
set rs = currentdb()
set qd = db.querydefs![YourQueryNameHere]
qd.parameters![P1] = 123
qd.parameters![P2] = "abc"
set rs = qd.openrecordset()
' do things with rs here.
qd.close
set qd = nothing
set rs = nothing
set db = nothing

HTH,
TC

HI!!

I would like to know how to pass a parameter(from VBA) to
a query I built using the query wizard tool in access.

Here's what I've got so far:

1.
Query name: qrFirstTry
content of query:

PARAMETERS Pnbr Text ( 255 );
SELECT ClientName,Address,City,Province,ClientNo
FROM Clients
WHERE ClientNo=[Pnbr];

2. some code (Don't know if its any good)
Dim Number As Double
Dim prmNumber As Parameter
Dim cmd As Command

Number = Text9.Value

Set prmNumber = cmd.CreateParameter
("EssaiPourPasserParametre", adVarChar, , , Number)
cmd.Parameters.Append prmNumber

When I try this code it gives me an error message.

What I would like to do is capture the content of a
textBox place it in the variable 'Number', then send this
variable inside my query(as a parameter) and execute my
query(qrFirstTry).

Other question:
Can I see the result of the query and/or can I capture the
results in a recordSet for further analysis.

Can anyone help me?

Pat

 

[Patrick]

Thanx very much, that's exactly what I was looking for...

-----Original Message-----
oops, set DB = currentdb (of course).

TC

Here is how you would generate a recordset from a query that expected two
parameters, P1 and P2.

(untested)

dim db as database, qd as querydef, rs as recordset
set rs = currentdb()
set qd = db.querydefs![YourQueryNameHere]
qd.parameters![P1] = 123
qd.parameters![P2] = "abc"
set rs = qd.openrecordset()
' do things with rs here.
qd.close
set qd = nothing
set rs = nothing
set db = nothing

HTH,
TC

HI!!

I would like to know how to pass a parameter(from VBA) to
a query I built using the query wizard tool in access.

Here's what I've got so far:

1.
Query name: qrFirstTry
content of query:

PARAMETERS Pnbr Text ( 255 );
SELECT ClientName,Address,City,Province,ClientNo
FROM Clients
WHERE ClientNo=[Pnbr];

2. some code (Don't know if its any good)
Dim Number As Double
Dim prmNumber As Parameter
Dim cmd As Command

Number = Text9.Value

Set prmNumber = cmd.CreateParameter
("EssaiPourPasserParametre", adVarChar, , , Number)
cmd.Parameters.Append prmNumber

When I try this code it gives me an error message.

What I would like to do is capture the content of a
textBox place it in the variable 'Number', then send this
variable inside my query(as a parameter) and execute my
query(qrFirstTry).

Other question:
Can I see the result of the query and/or can I capture the
results in a recordSet for further analysis.

Can anyone help me?

Pat

.