Query Parameters from Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a module for a form load event, I have the following line of code:
rs.Open "Case", CurrentProject.Connection, adOpenDynamic

"Case" is the name of a query in the .mdb the module is part of. This worked
fine until I changed one of the criteria fields to prompt for a parameter.
Now when I load the form I get an error and it does not prompt for the
parameter. What do I have to do special to make it prompt for the parameter
from code just like it does when I run the query in Access or I could make my
own MsgBox prompt but then how do I specify the parameter when opening that
query?

- Tom
 
Thomas Tutko said:
In a module for a form load event, I have the following line of code:
rs.Open "Case", CurrentProject.Connection, adOpenDynamic

"Case" is the name of a query in the .mdb the module is part of. This
worked fine until I changed one of the criteria fields to prompt for
a parameter. Now when I load the form I get an error and it does not
prompt for the parameter. What do I have to do special to make it
prompt for the parameter from code just like it does when I run the
query in Access or I could make my own MsgBox prompt but then how do
I specify the parameter when opening that query?
Click to expand...

This is easy to do using DAO using the QueryDef object, and it would be
about equally easy in ADO if you didn't need a dynamic recordset. If
you can live with a forward-only, static recordset, then you could use
code like this:

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "Case"
cmd.CommandType = adCmdTable
cmd.Parameters.Refresh
For Each prm In cmd.Parameters
prm.Value = InputBox(prm.Name)
Next prm
Set rs = cmd.Execute

If you need a dynamic, updatable recordset, I don't know enough about
ADO to tell you how to get one. As I said before, if you want to use
DAO, it's easy.
 
Dirk Goldgar said:
This is easy to do using DAO using the QueryDef object, and it would
be about equally easy in ADO if you didn't need a dynamic recordset.
If you can live with a forward-only, static recordset, then you could
use code like this:

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "Case"
cmd.CommandType = adCmdTable
cmd.Parameters.Refresh
For Each prm In cmd.Parameters
prm.Value = InputBox(prm.Name)
Next prm
Set rs = cmd.Execute

If you need a dynamic, updatable recordset, I don't know enough about
ADO to tell you how to get one.
Click to expand...

Ah, here it is:

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "Case"
cmd.CommandType = adCmdTable
cmd.Parameters.Refresh
For Each prm In cmd.Parameters
prm.Value = InputBox(prm.Name)
Next prm
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseServer
rs.LockType = adLockOptimistic
rs.Open cmd
 
Back
Top