How to access query in VBA

  • Thread starter Thread starter Zed Gorski
  • Start date Start date
Z

Zed Gorski

Hi,

How can I access existing query with one parameter from VBA code. There is
no problem to access table or simple query from the code. OpenRecordset
using parametrised query does not prompt for parameter and recordset in
nothing. No error
The same query directly in Access works perfectly

All application is written in Access 2000

Please, help me

Thanks

Zed
 
Assign a value to the Parameter of the QueryDef before you OpenRecordset:

Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set qdf = dbengine(0)(0).QueryDefs("NameOfYourQueryHere")
qdf.Parameters("SomeParameter") = xxxx
Set rs = qdf.OpenRecordset(...
 
Hi Allen

Thanks for your response.
I would like to open - using VBA code - parametrised query already defined
in the database container
This query is also used directly by Access report - so I am not interested
to make any modification

How can I solve my problem

Thanks

Zed
 
Zed, the code does not modify the query in any way.
It simply shows how to supply the parameter.

If you want to get the parameter from the user, your code can use InputBox()
to get the value, and assign it to the Parameter of the querydef.
 
Allen,

Small pieces of the code

In the database container exists query called MyQuery:
SELECT *
FROM MyTable
WHERE MyTable.Salary = [Enter Salary Value]

When you open this query from the database container, you'll get a prompt to
provide Salary value.This prompt happens because the Jet database engine can
't resolve "[Enter Salary Value]" as referring to any object that it knows
about, so it resorts to prompting the user. As result of this query user
supplies the value of parameters. For example 8 records are return for 45
000

Now somewhere in VBA code:
 
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSalary As As String

Do While Not IsNumeric(strSalary)
strSalary = InputBox("Enter Salary Value")
Loop

Set qdf = dbengine(0)(0).QueryDefs("NameOfYourQueryHere")
qdf.Parameters("[Enter Salary Value]") = CCur(strSalary)
Set rs = qdf.OpenRecordset(...


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Zed Gorski said:
Allen,

Small pieces of the code

In the database container exists query called MyQuery:
SELECT *
FROM MyTable
WHERE MyTable.Salary = [Enter Salary Value]

When you open this query from the database container, you'll get a prompt to
provide Salary value.This prompt happens because the Jet database engine can
't resolve "[Enter Salary Value]" as referring to any object that it knows
about, so it resorts to prompting the user. As result of this query user
supplies the value of parameters. For example 8 records are return for 45
000

Now somewhere in VBA code:

.
.
.
Dim dbsRegistry As Database
Dim rstCustomers As Recordset
Dim strQueryByComp As String

' Setting database and recordset variables
Set dbsRegistry = CurrentDb()
strQueryByComp = "SELECT *" _
& " FROM MyQuery"
Set rstCustomers = dbsRegistry.OpenRecordset(strQueryByComp,
dbOpenDynaset)
rstCustomers.MoveLast
MsgBox rstCustomers.RecordCount
.
.
.
When you execute above code there is no prompt for Salary parameter and
rstCustomer is nothing
Still the same query. Please, provide help about this specific situation

Thanks for your support

Zed,


Allen Browne said:
Zed, the code does not modify the query in any way.
It simply shows how to supply the parameter.

If you want to get the parameter from the user, your code can use InputBox()
to get the value, and assign it to the Parameter of the querydef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

recordset
in
Click to expand...
Click to expand...
 
Back
Top