I've been fighting this code for way too long, can someone help? Trying to
send an email from access and it hangs on this line:

Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

I have DAO Library referenced and can't get past this line

Private Sub Form_Close()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT EmailAddress FROM %Staff " & "Where StaffMember Like '" &
Me.ActivityLead & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!EmailAddress & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "New Activity Assignment",
"A new Activity has been assigned to you in the Review Tracker", True


End Sub

--
Your guidance is greatly appreciated!

What is the error message? Have you tried dropping the dbOpenSnapshot and
simply having Set rs = db.OpenRecordset(sql)? All of my code with recordsets
is simply either a coded Select statement or a string variable (like yours)
and then manipulate the data as you have done.

"Apprentice" wrote:

> I've been fighting this code for way too long, can someone help? Trying to
> send an email from access and it hangs on this line:
>
> Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
>
> I have DAO Library referenced and can't get past this line
>
> Private Sub Form_Close()
> Me.Dirty = False
> Dim db As Database
> Dim rs As DAO.Recordset
> Dim ToVar As String
> Dim sql As String
> sql = "SELECT EmailAddress FROM %Staff " & "Where StaffMember Like '" &
> Me.ActivityLead & "'"
> Set db = CurrentDb
> Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
>
> Do Until rs.EOF
> ToVar = ToVar & rs!EmailAddress & ";"
> rs.MoveNext
> Loop
>
> DoCmd.SendObject acSendNoObject, , , ToVar, , , "New Activity Assignment",
> "A new Activity has been assigned to you in the Review Tracker", True
>
>
> End Sub
>
> --
> Your guidance is greatly appreciated!

"Apprentice" <(E-Mail Removed)> wrote in message
news:7DC3552D-8865-4379-87DC-(E-Mail Removed)...
> I've been fighting this code for way too long, can someone help? Trying
> to
> send an email from access and it hangs on this line:
>
> Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
>
> I have DAO Library referenced and can't get past this line
>
> Private Sub Form_Close()
> Me.Dirty = False
> Dim db As Database
> Dim rs As DAO.Recordset
> Dim ToVar As String
> Dim sql As String
> sql = "SELECT EmailAddress FROM %Staff " & "Where StaffMember Like '" &
> Me.ActivityLead & "'"
> Set db = CurrentDb
> Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
>
> Do Until rs.EOF
> ToVar = ToVar & rs!EmailAddress & ";"
> rs.MoveNext
> Loop
>
> DoCmd.SendObject acSendNoObject, , , ToVar, , , "New Activity Assignment",
> "A new Activity has been assigned to you in the Review Tracker", True
>
>
> End Sub
>
> --
> Your guidance is greatly appreciated!

If this:

%Staff

isn't just a typo, then it may be the problem. Try this:

Create a new query
Include the %Staff table
Add all the fields
Close and save the query as qryStaff
Use qryStaff instead of %Staff in your sql

Thanks to both Pendragon and Mr. McCall, I used both suggestions and its
ticking perfect now. Thanks alot! Here is the completed code for others:

Private Sub Form_Close()
Me.Dirty = False
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT EmailAddress FROM AutoEmailQuery" & "Where StaffMember Like '"
&
Me.ActivityLead & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql)

Do Until rs.EOF
ToVar = ToVar & rs!EmailAddress & ";"
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , ToVar, , , "New Activity Assignment",
"A new Activity has been assigned to you in the Review Tracker", True


End Sub

--
Your help was greatly appreciated!


"Stuart McCall" wrote:

> "Apprentice" <(E-Mail Removed)> wrote in message
> news:7DC3552D-8865-4379-87DC-(E-Mail Removed)...
> > I've been fighting this code for way too long, can someone help? Trying
> > to
> > send an email from access and it hangs on this line:
> >
> > Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
> >
> > I have DAO Library referenced and can't get past this line
> >
> > Private Sub Form_Close()
> > Me.Dirty = False
> > Dim db As Database
> > Dim rs As DAO.Recordset
> > Dim ToVar As String
> > Dim sql As String
> > sql = "SELECT EmailAddress FROM %Staff " & "Where StaffMember Like '" &
> > Me.ActivityLead & "'"
> > Set db = CurrentDb
> > Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
> >
> > Do Until rs.EOF
> > ToVar = ToVar & rs!EmailAddress & ";"
> > rs.MoveNext
> > Loop
> >
> > DoCmd.SendObject acSendNoObject, , , ToVar, , , "New Activity Assignment",
> > "A new Activity has been assigned to you in the Review Tracker", True
> >
> >
> > End Sub
> >
> > --
> > Your guidance is greatly appreciated!
>
> If this:
>
> %Staff
>
> isn't just a typo, then it may be the problem. Try this:
>
> Create a new query
> Include the %Staff table
> Add all the fields
> Close and save the query as qryStaff
> Use qryStaff instead of %Staff in your sql
>
>
>