Loop Issue

[Guest]

For each employee in a table, I want to pull the transactions for that
employee, put the transactions into a report, then email the file to the
employee. I haven't coded the send statement yet, but I'm trying to get the
first parts to work before I do that. When I run the code, it selects all
employees, and outputs all of them. I need to access the table as if it were
an array or something I think.

for example:
emptable contains employee number and email address
empnum
email

transtable contains transactions and employee number for each transaction
empnum
transtype
amount

I've been trying to do this in a loop, but it outputs all employees to a
report. I don't know how to code this so that it would go through
the emptable one by one to do this.

Function Macro2()

Dim BegCt As Integer
Dim MaxCt As Integer

BegCt = 1
MaxCt = DCount("*", "emptable")

If BegCt <= MaxCt Then
Do
DoCmd.RunSQL "SELECT [transtable].*, emptable.EMail INTO wk_query FROM
[transtable] LEFT JOIN emptable ON [transtable].CT_AGT = emptable.CT_AGT;", 0
DoCmd.OutputTo acReport, "Weekly Detail Counts", "RichTextFormat(*.rtf)",
"testoutfile_" & empnum, False, "", 0
BegCt = BegCt + 1
Loop
End If

End Function

 

[Guest]

the database approach to this would presume that your Transaction records do
have a cross reference to an employee ID.....meaning that you simply would do
a query of the Transactions for a specific employee ID.

no code involving looping is necessary if the data is structured properly in
tables.