problem outputing a unique report for each record in a table (using Access 200)

[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
-----
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

 

[Guest]

Your SQL stament should look like this:

SELECT EmpMaster.Number, EmpMaster.Name, EmpMaster.Dept
FROM EmpMaster
WHERE (((EmpMaster.Number)=[forms]![MyForm]![txtnum]))
ORDER BY EmpMaster.Name;

Replace MyForm with your form's name,