Running a parameter query from code

[Guest]

Hi there.

I have a button on a form which I would like to export the data on this
form's sub form into excel. When I try this:

Private Sub Command42_Click()
Dim db As Database
Dim rstQueryData As Recordset
Dim objExcel As Object, intRowCounter As Integer

Set db = CurrentDb

Set objExcel = CreateObject("excel.Application")
With objExcel
.Visible = True
.Workbooks.Add

Set rstQueryData = db.OpenRecordset("qryTest")

intRowCounter = 0
Do Until rstQueryData.EOF

.Range("A2").offset(intRowCounter, 0) = rstQueryData![Vendor]
.Range("b2").offset(intRowCounter, 0) = rstQueryData![InvTotal]

intRowCounter = intRowCounter + 1

rstQueryData.MoveNext
Loop
End With

End Sub

This works beautifully until I add the following parameter to my qryTest in
my InvAmt field:

[forms]![frmLookup]![Amt]

I then get err 3061 too few parameters expected 1. Do I have to repeat the
parameter in the query in code or what do I do?

Thanks for all help offered!

J

 

[Nikos Yannacopoulos]

Johnny,

You have stumped on a known Access shortcoming, if I may call it that...
parameter queries can't be run from code. The workaround is to build the
SQL for the recordset definition within the code. A good start is to
open the saved query in SQL view and copy the SQL expression from there,
especially if this is a complex query. Then, assuming the (copied)
expression looks something like (simplified example):

SELECT * FROM MyTable
WHERE InvAmt = [forms]![frmLookup]![Amt]
ORDER BY FieldX

use a strng variable to build the expression into, maintaining the form
control reference, like:

Dim strSQL As String
....
strSQL = "SELECT * FROM MyTable WHERE InvAmt = " _
& [forms]![frmLookup]![Amt] _
& " ORDER BY FieldX"

and modify your recordset definition to:

Set rstQueryData = db.OpenRecordset(strSQL)

Note: I have assumed the InvAmt field to be numeric; in the unlikely
event it is text, change the expression to:

strSQL = "SELECT * FROM MyTable WHERE InvAmt = '" _
& [forms]![frmLookup]![Amt] _
& "' ORDER BY FieldX"

so the value read from the form control is enclosed in single quotes.

HTH,
Nikos

 

[Van T. Dinh]

You can use the QueryDef to create Recordset like (****untest****):

Private Sub Command42_Click()
Dim db As Database
Dim qdf As DAO.QueryDef '****Added
Dim prm As DAO.Parameter '****Added
Dim rstQueryData As DAO.Recordset '****Changed
Dim objExcel As Object, intRowCounter As Integer

Set db = CurrentDb

Set objExcel = CreateObject("excel.Application")
With objExcel
.Visible = True
.Workbooks.Add

Set qdf = db.QueryDefs("qryTest")

Set rstQueryData = db.OpenRecordset("qryTest")
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next

Set rstQueryData = qdf.OpenRecordset(type,options,lockedits)
' Check Access VB Help for valid type, options, lockedits

intRowCounter = 0
Do Until rstQueryData.EOF
....

--
HTH
Van T. Dinh
MVP (Access)

Hi there.

I have a button on a form which I would like to export the data on this
form's sub form into excel. When I try this:

Private Sub Command42_Click()
Dim db As Database
Dim rstQueryData As Recordset
Dim objExcel As Object, intRowCounter As Integer

Set db = CurrentDb

Set objExcel = CreateObject("excel.Application")
With objExcel
.Visible = True
.Workbooks.Add

Set rstQueryData = db.OpenRecordset("qryTest")

intRowCounter = 0
Do Until rstQueryData.EOF

.Range("A2").offset(intRowCounter, 0) = rstQueryData![Vendor]
.Range("b2").offset(intRowCounter, 0) = rstQueryData![InvTotal]

intRowCounter = intRowCounter + 1

rstQueryData.MoveNext
Loop
End With

End Sub

This works beautifully until I add the following parameter to my qryTest
in
my InvAmt field:

[forms]![frmLookup]![Amt]

I then get err 3061 too few parameters expected 1. Do I have to repeat
the
parameter in the query in code or what do I do?

Thanks for all help offered!

J