Help! Loops!

[beavetoots]

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

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_" & AOR_AGT_BUS_ID, False, "", 0
BegCt = BegCt + 1
Loop
End If

End Function

 

[Stephen English]

Dim rs as recordset
Dim strSQL as string
Dim db as database

Set db=currentdb
docmd.set warnings false
Set rs=db.openrecordset("SELECT * FROM emptable")
If not rs.eof then
rs.movefirst
Do until rs.eof
DoCmd.runsql ("DELETE * FROM wk_query") ' Assuming this is a temporary
table
strSQL = "SELECT * FROM transtable into wk_query WHERE empnum=" &
rs!empnum
doCmd.runSQL(strSQL)
' you now have the data you want for this employee in wk_query
' if your report takes all data fron wk_query
' then run the report on this table and the email address is rs!email
rs.movenext
loop
end if

THere are probably many other ways to do it but this should work.
Stephen

 

[beavetoots]

Stephen,
I get a type mismatch on this statement:
Set rs=db.openrecordset("SELECT * FROM emptable")

Dim rs as recordset
Dim strSQL as string
Dim db as database

Set db=currentdb
docmd.set warnings false
Set rs=db.openrecordset("SELECT * FROM emptable")
If not rs.eof then
rs.movefirst
Do until rs.eof
DoCmd.runsql ("DELETE * FROM wk_query") ' Assuming this is a temporary
table
strSQL = "SELECT * FROM transtable into wk_query WHERE empnum=" &
rs!empnum
doCmd.runSQL(strSQL)
' you now have the data you want for this employee in wk_query
' if your report takes all data fron wk_query
' then run the report on this table and the email address is rs!email
rs.movenext
loop
end if

THere are probably many other ways to do it but this should work.
Stephen

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

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_" & AOR_AGT_BUS_ID, False, "", 0
BegCt = BegCt + 1
Loop
End If

End Function

 

[Douglas J. Steele]

Unfortunately, "Recordset" is an object in both the ADO and DAO models.

Since you're not getting an error on Dim db As Database, nor on Set
db=CurrentDb, that implies that you have a reference set to DAO (it's not
set by default in Access 2000 and 2002).

The error, therefore, is likely because you have a reference to ADO higher
in the list of references, so it's getting precedence. Since the code is
trying to use DAO methods, that's going to cause the "type mismatch" error.

When you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO recordset,
you'll need to use Dim rsCurr as DAO.Recordset (to guarantee an ADO
recordset, you'd use Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

In other words, change the declarations to:

Dim rs as DAO.Recordset
Dim strSQL as String
Dim db as DAO.Database

(the DAO.Database isn't strictly necessary, since Database isn't an object
in any other common library. However, it's always a good idea to be as
explicit as possible!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Stephen,
I get a type mismatch on this statement:
Set rs=db.openrecordset("SELECT * FROM emptable")

Dim rs as recordset
Dim strSQL as string
Dim db as database

Set db=currentdb
docmd.set warnings false
Set rs=db.openrecordset("SELECT * FROM emptable")
If not rs.eof then
rs.movefirst
Do until rs.eof
DoCmd.runsql ("DELETE * FROM wk_query") ' Assuming this is a
temporary
table
strSQL = "SELECT * FROM transtable into wk_query WHERE empnum=" &
rs!empnum
doCmd.runSQL(strSQL)
' you now have the data you want for this employee in wk_query
' if your report takes all data fron wk_query
' then run the report on this table and the email address is rs!email
rs.movenext
loop
end if

THere are probably many other ways to do it but this should work.
Stephen

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

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_" & AOR_AGT_BUS_ID, False, "", 0
BegCt = BegCt + 1
Loop
End If

End Function

 

[beavetoots]

now i get a mismatch on WHERE empnum=" &
rs!empnum

both are of type text for 11

Unfortunately, "Recordset" is an object in both the ADO and DAO models.

Since you're not getting an error on Dim db As Database, nor on Set
db=CurrentDb, that implies that you have a reference set to DAO (it's not
set by default in Access 2000 and 2002).

The error, therefore, is likely because you have a reference to ADO higher
in the list of references, so it's getting precedence. Since the code is
trying to use DAO methods, that's going to cause the "type mismatch" error.

When you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO recordset,
you'll need to use Dim rsCurr as DAO.Recordset (to guarantee an ADO
recordset, you'd use Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

In other words, change the declarations to:

Dim rs as DAO.Recordset
Dim strSQL as String
Dim db as DAO.Database

(the DAO.Database isn't strictly necessary, since Database isn't an object
in any other common library. However, it's always a good idea to be as
explicit as possible!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Stephen,
I get a type mismatch on this statement:
Set rs=db.openrecordset("SELECT * FROM emptable")

Dim rs as recordset
Dim strSQL as string
Dim db as database

Set db=currentdb
docmd.set warnings false
Set rs=db.openrecordset("SELECT * FROM emptable")
If not rs.eof then
rs.movefirst
Do until rs.eof
DoCmd.runsql ("DELETE * FROM wk_query") ' Assuming this is a
temporary
table
strSQL = "SELECT * FROM transtable into wk_query WHERE empnum=" &
rs!empnum
doCmd.runSQL(strSQL)
' you now have the data you want for this employee in wk_query
' if your report takes all data fron wk_query
' then run the report on this table and the email address is rs!email
rs.movenext
loop
end if

THere are probably many other ways to do it but this should work.
Stephen
:

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

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_" & AOR_AGT_BUS_ID, False, "", 0
BegCt = BegCt + 1
Loop
End If

End Function

 

[beavetoots]

I'm all set now ... I'd like to thank both of you VERY much for your
wonderful assistance!