Looping question (1 Viewer)

[Jalnac74]

Hi,

I hope someone can help me. I have looked all over the internet for a solution to this (including here) and can't seem to find a solution to my problem, I am certain that what I want is possible. Basically, I have some code (i can't attach it for some reason - something to do with email addresses) that sends an emails to members of staff listing an intervention that they need to review. Please could you tell me what i am doing wrong. I am at the point where the code below sends one member of staff one email for each intervention, this is very inefficient. What I want to do is send each member of staff a personalised list of interventions by email.

 

[JHB]

Without the code it isn't easy to say what you do wrong.
Ps. When you post code use the code tags, click the #.

 

[Jalnac74]

here is the code!

 Private Sub Command2_Click()
Dim em As String
Dim MyDB As DAO.Database
Dim rstEAddr As DAO.Recordset
Dim strBuild As String
Dim sqlstr As String
Dim varwhere As String
Dim subject As String
Dim QRYSTR As String
Dim strinter As String
Dim sqlinter As String
  
sqlstr = "Select * From [reviewsdue] where [int_id]is not null"
 
Set MyDB = CurrentDb
Set rstEAddr = MyDB.OpenRecordset(sqlstr, dbOpenForwardOnly)
  
With rstEAddr
  Do While Not .EOF
    If ![em] <> "" Then
      strBuild = ![em]
      strinter = ![provision]
      strdate = ![enddate] + 14
        
        If strBuild = "" Then
            MsgBox "There are no interventions to review", , "I.T.S."
        Else:
            subject = "Intervention Tacking System - Reviews Due"
             em = strBuild
    
DoCmd.SendObject , , , em, , , subject, _
 "Hi," & vbNewLine _
& vbNewLine & "You have interventions that are due to end within the next couple of weeks." _
& vbNewLine & vbNewLine & strinter & vbNewLine & vbNewLine & "Please could you complete the intervention review by " & strdate & vbNewLine _
& vbNewLine & "Many thanks for your cooperation" & vbNewLine _
& vbNewLine & "***PLEASE NOTE: Reminder emails will be sent weekly until reviews have been completed.***", True
         End If
    End If
      .MoveNext
  Loop
End With
 
End Sub

 

[Jalnac74]

I have also tried this code with a nested loop to no avail!

 [FONT=Courier New]Dim dbs As DAO.Database
[/FONT][COLOR=black][FONT=Courier New]Dim rst As DAO.Recordset[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Dim rstFiltered As DAO.Recordset[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Dim strEmail As String[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Dim sqlstring as string[/FONT][/COLOR]
[SIZE=3][FONT=Calibri]Dim subject As String[/FONT][/SIZE]


[COLOR=black][FONT=Courier New]Set dbs = CurrentDb[/FONT][/COLOR]
[SIZE=3][FONT=Calibri]sqlstr = "Select * From [reviewsdue] where [int_id]is not null ORDER BY [em] DESC"[/FONT][/SIZE]


[COLOR=black][FONT=Courier New]'Create the first filtered Recordset[/FONT][/COLOR]

[COLOR=black][FONT=Courier New]Set rest = dbs.OpenRecordset[/FONT][/COLOR][SIZE=3][FONT=Calibri](sqlstr, dbOpenForwardOnly)[/FONT][/SIZE]

[COLOR=black][FONT=Courier New]'Begin row processing[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Do While Not rst.EOF[/FONT][/COLOR]

[COLOR=black][FONT=Courier New]'Retrieve the name of the first person in the selected rows[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]stremail = rst!em[/FONT][/COLOR]

[COLOR=black][FONT=Courier New]'Now filter the Recordset to return only the customers from that city[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]rst.Filter = "em = '" & stremail & "'"[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Set rstFiltered = rst.OpenRecordset[/FONT][/COLOR]

[COLOR=black][FONT=Courier New]'Process the rows[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Do While Not rstFiltered.EOF[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Strbuild = strbuild [/FONT][/COLOR][FONT=Calibri][SIZE=3]& ![provision] & "[/SIZE][SIZE=3]-[/SIZE][SIZE=3]" & ![enddate] & vbNewLine[/SIZE][/FONT]
[COLOR=black][FONT=Courier New]rstFiltered.MoveNext[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Loop[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Exit Do[/FONT][/COLOR]

[FONT=Calibri][SIZE=3]subject = "Intervention Tacking System - Reviews Due"[/SIZE][/FONT]

[FONT=Calibri][SIZE=3]em = stremail[/SIZE][/FONT]

[SIZE=3][FONT=Calibri]DoCmd.SendObject , , , em, , , subject, _[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]"Hi," & vbNewLine _[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]& vbNewLine & "You have interventions that are due to end within the next couple of weeks." _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]& vbNewLine & vbNewLine & strbuild & vbNewLine & vbNewLine & "Please could you complete the intervention review by ASAP” & vbNewLine _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]& vbNewLine & "Many thanks for your cooperation" & vbNewLine _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]& vbNewLine & "***PLEASE NOTE: Reminder emails will be sent weekly until reviews have been completed.***", True[/FONT][/SIZE]


[COLOR=black][FONT=Courier New]rst.MoveNext[/FONT][/COLOR]

[COLOR=black][FONT=Courier New]Loop[/FONT][/COLOR]

[COLOR=black][FONT=Courier New]'Cleanup[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]rstFiltered.Close[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]rst.Close[/FONT][/COLOR]

[COLOR=black][FONT=Courier New]Set rstFiltered = Nothing[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]Set rst = Nothing[/FONT][/COLOR]

 

[JHB]

You have some fault in the code, you've not declared rest and you don't use it afterwards you've set it.

[COLOR=black][FONT=Courier New]Set[B][COLOR=Red] rest[/COLOR][/B] = dbs.OpenRecordset[/FONT][/COLOR][SIZE=3][FONT=Calibri](sqlstr, dbOpenForwardOnly)[/FONT][/SIZE]

 

[Jalnac74]

You have some fault in the code, you've not declared rest and you don't use it afterwards you've set it.

[COLOR=black][FONT=Courier New]Set[B][COLOR=red] rest[/COLOR][/B] = dbs.OpenRecordset[/FONT][/COLOR][SIZE=3][FONT=Calibri](sqlstr, dbOpenForwardOnly)[/FONT][/SIZE]

I have changed the code in my database, it should have said rst not rest. The code still does not do what I want it to do though!

I am now getting a compile error on this line

 strBuild = strBuild & ![provision] & "-" & ![enddate] & vbNewLine

 

[JHB]

..
I am now getting a compile error on this line

 strBuild = strBuild & ![provision] & "-" & ![enddate] & vbNewLine

Yes because it is not clear for MS-Access from where [provision] and [enddate] are coming. You're using some code which normally is used in connection with a "With" - "End With" structure like below:

With rstEAddr
  ..
  ... 
End With

 

[Jalnac74]

Hi,

I have rewritten my code. I can now loop through each recipient but get a list of all interventions (for everyone) so I have added a recordset filter that doesn't seem to work. can you help?

 Dim dbs As DAO.Database
Dim rstemail As DAO.Recordset
Dim rstinter As DAO.Recordset
Dim rstfiltered As DAO.Recordset
Dim stremail As String
Dim strinter As String
 Set dbs = CurrentDb
Set rstemail = dbs.OpenRecordset("emaildue")
Set rstinter = dbs.OpenRecordset("reviewsdue")
 
With rstemail
    Do While Not .EOF
        stremail = ![firstofemail]
        Debug.Print stremail
             With rstinter
            rstinter.Filter = "[temail] = '" & stremail & "'"
            rstfiltered = rst.OpenRecordset
            Do While Not .EOF
            strinter = strinter & " " & ![provision]
            .MoveNext
            
            Loop
            End With
        Debug.Print strinter
    .MoveNext
    Loop
 End With

 

[JHB]

Try the below, (code not tested):

  Dim dbs As DAO.Database
  Dim rstemail As DAO.Recordset
  Dim rstinter As DAO.Recordset
  Dim rstfiltered As DAO.Recordset
  Dim strinter As String
 
  Set dbs = CurrentDb
  Set rstemail = dbs.OpenRecordset("emaildue")
 
  With rstemail
    Do While Not .EOF
        Set rstinter = dbs.OpenRecordset("Select * From reviewsdue Where [temail] = '" & ![firstofemail] & "'")
        If Not rstinter.EOF Then
          strinter=""
          Do
            strinter = strinter & " " & ![provision]
            rstinter.MoveNext
          Loop Until rstinter.EOF
        End If
        'Then you need some code for attaching and sending the Email
      .MoveNext
    Loop
  End With

 

[Jalnac74]

hi JHB,

Thank you so much! I am now getting exactly what I need. I had to tweak slightly on this line:

  strinter = strinter & " " & [COLOR=red]rstinter[/COLOR]![provision]

but otherwise this works perfectly. I realise now where I was going wrong, I was not referring to the recordset properly in this line:

 Set rstinter = dbs.OpenRecordset("Select * From reviewsdue Where [temail] = '" & ![firstofemail] & "'")

I had tried something like this earlier and had simply forgotten to include the brackets!

thanks again for your help!

 

[JHB]

Good you got it to work!