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:

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 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

Quote:

Originally Posted by ChipR

Here's a quick example:

Expand|Select|Wrap|Line Numbers

 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

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

Quote:

Originally Posted by 7 of 9

Doing it this way runs the query and puts the results for all employees in each file.

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

Expand|Select|Wrap|Line Numbers

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

if you'd like.

7 of 9

Quote:

Originally Posted by ChipR

We can look at your code for

Expand|Select|Wrap|Line Numbers

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

if you'd like.

Sure here is what i have

Expand|Select|Wrap|Line Numbers

 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

Understanding VBA Loop

re: Understanding VBA Loop

re: Understanding VBA Loop

re: Understanding VBA Loop

re: Understanding VBA Loop

re: Understanding VBA Loop

re: Understanding VBA Loop

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about