Looping through code

[Guest]

It is probably obvious that I am a beginner, but I am trying to loop through
code to send a report as an attachment to an email to separate email
addresses. I am able to send the emails individually using SendObject in a
separate Sub (SeparateEmails), but I can't loop through the records to send
all emails with one command button without errors. My latest attempt with
the Do...Loop is as follows:

Dim db As Database
Dim Rst As Recordset

Set db = Currentdb()
Set Rst = db.OpenRecordset()

Do Until Rst.EOF

Call SeparateEmails
Rst.MoveNext

Loop

This results in a Compile Error: User-defined type not defined and
highlights Dim db As Database. Any suggestions?
Thanks.

 

['69 Camaro]

Hi.

Your database application is missing the DAO Object Library reference. For
instructions on how to fix this, please see the tip, "ADO and DAO Library
References in Access Databases" byTom Wickerath on this Web page:

http://www.Access.QBuilt.com/html/gem_tips1.html

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Larry Daugherty]

Hi,

It may be a references issue. You may need to Dim db as DAO.Recordset.

I don't see that you've named your recordset: Set Rst =
db.OpenRecordset("tblMyEmail")

Nor do I see where you are passing anything to SeparateEmails.

When you fix the obvious, try using the debugger and turning on the
Immediate Window. Then set a breakpoint on the debug.print line I put into
your code below. and start stepping through your code by tapping F8. Later,
when you think the bugs are pretty well out you can use F5 to fly through.

This will step you into your subroutine and you can write more debug.print
commands to print the contents of simple variables in your application.

If you get closer but it stays broken, simply copy your code and paste it
into your post. Tell what happened and when it happened in your code.

I've found that using the debugger sometimes clarifies things a whole bunch.
It's a great tool and well worth the bother to learn.

HTH
--
-Larry-
--

It is probably obvious that I am a beginner, but I am trying to loop through
code to send a report as an attachment to an email to separate email
addresses. I am able to send the emails individually using SendObject in a
separate Sub (SeparateEmails), but I can't loop through the records to send
all emails with one command button without errors. My latest attempt with
the Do...Loop is as follows:

Dim db As Database
Dim Rst As Recordset

Set db = Currentdb()
Set Rst = db.OpenRecordset()

Rst.MoveFirst 'removes ambiguity

 

[Guest]

Thanks for the help, I tried your suggestions (I think) and now I am getting
an error that says "Object variable or With variable not set" when I try to
run the code. My code is as follows:

Private Sub Command142_Click()
On Error GoTo Err_Command142_Click

Dim db As DAO.Database
Dim Rst As DAO.Recordset

Set Rst = db.OpenRecordset("Tasks scheduled", Form, "", "")

Rst.MoveFirst
Do Until Rst.EOF

Call SeparateEmails
Rst.MoveNext

Loop

Exit_Command142_Click:
Exit Sub

Err_Command142_Click:
MsgBox Err.Description
Resume Exit_Command142_Click

End Sub

 

[Mark]

Private Sub Command142_Click()
On Error GoTo Err_Command142_Click

Dim db As DAO.Database
Dim Rst As DAO.Recordset

Set Rst = db.OpenRecordset("Tasks scheduled", Form, "", "")

Here's where your problem is. The word Form in the OpenRecordset line is
being interpreted as a variable. I'm guessing you don't have "Option
Explicit" turned on, which makes you Dim every variable before you use it.
Since you don't set the variable Form equal to anything, you get the "Object
variable not set" error. Try changing the line to:

Set Rst = db.OpenRecordset("Tasks scheduled", dbOpenSnapshot)

You don't need the empty quotes ("") if you don't want to use the extra
options of the command. Using dbOpenSnapshot tells Access to open a
non-editable (snapshot) of the recordset. I like to use this option if I'm
just going through the recordset to get information...just in case I try
something stupid and am not paying attention.

 

['69 Camaro]

Hi.

now I am getting
an error that says "Object variable or With variable not set"

"db" has not been set to a Database object.

Put the line:

Set db = Currentdb( )

back into the code. You are going to have problems with the
OpenRecordset( ) method as well. "Form" is not a valid constant, nor are
the zero-length strings for the other two optional parameters. You should
always close objects and set their variables to nothing before the variables
lose scope in the procedure, so that memory is cleared and TableID's are
able to be recycled when needed.

Without making too many edits to your code (I use a very different style to
help prevent bugs and for ease of maintenance), you may want to try the
following:

' * * * * Start Code * * * *

Option Compare Database
Option Explicit

Private Sub Command142_Click()

On Error GoTo Err_Command142_Click

Dim db As Database
Dim Rst As DAO.Recordset


Set db = Currentdb( )
Set Rst = db.OpenRecordset("Tasks scheduled")

Rst.MoveFirst
Do Until Rst.EOF

Call SeparateEmails
Rst.MoveNext

Loop

Exit_Command142_Click:

On Error Resume Next

Rst.Close
Set Rst = Nothing
db.Close
Set db = Nothing

Exit Sub

Err_Command142_Click:
MsgBox Err.Description
Resume Exit_Command142_Click

End Sub

' * * * * End Code * * * *

 

[Guest]

Thank you for your help, now I can stop dreaming about Access codes!

 

['69 Camaro]

You're welcome. Glad it helped!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Guest]

Back again. I finally got my loop to work with the following code:

Private Sub Command142_Click()

On Error GoTo Err_Command142_Click

Dim db As Database
Dim Rst As DAO.Recordset

Set db = CurrentDb()
Set Rst = db.OpenRecordset("Tasks scheduled")

Rst.MoveFirst
Do Until Rst.EOF

Call SeparateEmails
Rst.MoveNext

Loop
MsgBox "All emails have been sent", vbOKOnly

Exit_Command142_Click:

On Error Resume Next

Rst.Close
Set Rst = Nothing
db.Close
Set db = Nothing

Exit Sub

Err_Command142_Click:
MsgBox Err.Description
Resume Exit_Command142_Click

End Sub

But, when I copied it and pasted it into a duplicate database, I got the
error "Compile Code: User-defined type not defined" and the "Dim db As
Database" line was highlighted. The only difference between the two databases
is that the "working" database has only four recordsets so I could test the
code and the "error" database is the final version that has many more. Any
suggestions?

 

[Guest]

I think I figured it out, the Microsoft DAO 3.6 Object Library was not
checked in the Available References in MS VB of the "error" database. I am
finally catching on to some of this Access stuff!