Understanding VBA Loop

[7 of 9]

Hello all, I need help with a "simple" loop. I have an employee table that has 50 records and i want to run a query (qryEmp01Sel) for each employee. So far I have been doing this manually but I would like to automate it. How to I create the following loop in VBA:

select * from qryEmp01Sel 
Where EmpID = (first employee) 
output results to file ( I know how to do this part) 
then move on to the next employee.

Thanks in advance

[ChipR]

Here's a quick example:

Dim records as Object
Dim strEmpID as String
 
Set db = CurrentDb()
Set records = db.OpenRecordset("SELECT * FROM qryEmp01Sel")
 
While Not records.EOF
    strEmpID = records!empID
    ' do stuff
 
    records.MoveNext
Wend

[7 of 9]

@ChipR
Thanks but I don’t think I was clear the first time around. I need the query results to show results for only 1 employee at a time because after the query runs I output the result to a file for each employee. Doing it this way runs the query and puts the results for all employees in each file. Should I be referencing the employee table (tblEmployee) somewhere?

thanks

[ChipR]

@7 of 9
I'm not sure what you mean. At line 9, you have 1 record, and the employee name in that one record. At line 11 you go to the next record. In between, you can do whatever you want, including open a different file each time through the loop. We can look at your code for

3. output results to file ( I know how to do this part) 

if you'd like.

[7 of 9]

@ChipR
Sure here is what i have

Dim records as Object 
Dim strEmpID as String 
 
Set db = CurrentDb() 
Set records = db.OpenRecordset("SELECT * FROM qryEmp01Sel") 
 
While Not records.EOF 
    strEmpID = records!empID 
    DoCmd.OutputTo acOutputQuery,  ""& strEmpID & "SalesData", "HTML", "E:\Sales\" & strEmpId & "sales.html
 
    records.MoveNext 
Wend
 

[ChipR]

For each employee you are outputting a query. The data in the output file is generated by the query, so if you want to change the output, we'll have to look at the query itself.

[7 of 9]

ok I think I get it now thanks ChipR