Passing a parameter to a query by code. How to do it?

[Ralph Heidecke]

I want to have a module open a file and output a report to an html file
based on a paramter value like a unique employee number. The report uses a
query as a data source, SQL and code I have tried included below. I can't
figure out how to pass the value in the variable "txtnum" to the query? I
get the default Input Box "Enter Parameter Value"

Is there a way to do this?

Is there a better approach?


Thanks in advance for any help...

SQL statement for the query (called queEmpMas)
-----
SELECT EmpMaster.Number, EmpMaster.Name, EmpMaster.Dept
FROM EmpMaster
WHERE (((EmpMaster.Number)=[txtnum]))
ORDER BY EmpMaster.Name;



VBA Module code

Sub lstReports_1()
Dim rec As DAO.Recordset
Dim db As DAO.Database
Dim irec As Integer
Dim txtnum As String
Dim txtName As String
Dim sDir As String
Dim sFile As String
Dim iCount As Integer

Set db = CurrentDb()
Set rec = db.OpenRecordset("EmpMaster", dbOpenDynaset)
sDir = "V:\Management\Business Manager\Leave Database\test\"
rec.MoveFirst

irec = rec.RecordCount

iCount = 1
Do While icounter < = irec
txtnum = rec("Number")
txtName = rec("Name")


sFile = sDir & txtName & ".html"
DoCmd.OutputTo Objecttype:=acOutputReport, _
ObjectName:="rptEmpMas", _
OutputFormat:=acFormatHTML, _
OutputFile:=sFile, _
Autostart:=False
rec.MoveNext
i = icounter + 1
Loop
rec.Close
db.Close

End Sub

 

[Ted Allen]

Hi Ralph,

You can't reference a VBA variable directly in the query sql. In general,
you can pass a parameter value in VBA, or you can reconstruct the sql in VBA,
and then assign the parameter value or revised sql to the query object. But,
I don't know if you can pass a parameter value to the query and then open a
report on the querydef object (I've never tried). You should be able to use
the sql reassignment method if you wanted to.

I think that likely your best bet will be to not referenct the emplyee
number at all in the query, and when you open the query pass the number to
the report as a filter criteria (take a look at access help for help on
passing filter criteria).

For future info regarding working with queries though, Following are the
methods of assigning parameters or constructing revised sql.

To do this, you would first have to attach to the query as a QueryDef
object. Such as:

Dim db as DAO.Database
Dim qdf as QueryDef
Dim strSQL as String

Set db = CurrentDb
Set qdf = db.QueryDefs("queEmpMas")

Then, to assign a parameter value, you would use
qdf.Parameters("txtnum") = txtnum

Or, you could reconstruct your sql statement, by concatenating with the
variable value, such as:

strSQL = "SELECT EmpMaster.Number, EmpMaster.Name, EmpMaster.Dept " _
& "FROM EmpMaster " _
& "WHERE EmpMaster.Number=" & [txtnum] & " " _
& "ORDER BY EmpMaster.Name;"

qdf.sql = strSQL
qdf.close
set qdf = nothing

note that reassigning the query sql in this manner will permanently change
the query sql (well, at least until the next time that it is reassigned).
But, assigning the parameter value will only be valid for the qdf object
until it is closed.

HTH, Post back if you have any other questions.

-Ted Allen

I want to have a module open a file and output a report to an html file
based on a paramter value like a unique employee number. The report uses a
query as a data source, SQL and code I have tried included below. I can't
figure out how to pass the value in the variable "txtnum" to the query? I
get the default Input Box "Enter Parameter Value"

Is there a way to do this?

Is there a better approach?


Thanks in advance for any help...

SQL statement for the query (called queEmpMas)
-----
SELECT EmpMaster.Number, EmpMaster.Name, EmpMaster.Dept
FROM EmpMaster
WHERE (((EmpMaster.Number)=[txtnum]))
ORDER BY EmpMaster.Name;



VBA Module code

Sub lstReports_1()
Dim rec As DAO.Recordset
Dim db As DAO.Database
Dim irec As Integer
Dim txtnum As String
Dim txtName As String
Dim sDir As String
Dim sFile As String
Dim iCount As Integer

Set db = CurrentDb()
Set rec = db.OpenRecordset("EmpMaster", dbOpenDynaset)
sDir = "V:\Management\Business Manager\Leave Database\test\"
rec.MoveFirst

irec = rec.RecordCount

iCount = 1
Do While icounter < = irec
txtnum = rec("Number")
txtName = rec("Name")


sFile = sDir & txtName & ".html"
DoCmd.OutputTo Objecttype:=acOutputReport, _
ObjectName:="rptEmpMas", _
OutputFormat:=acFormatHTML, _
OutputFile:=sFile, _
Autostart:=False
rec.MoveNext
i = icounter + 1
Loop
rec.Close
db.Close

End Sub