Code To Loop Through A Recordset (FORM)

Question

Dim db As DAO.Database

Dim rst As DAO.Recordset

Set db = CurrentDb

Set rst = db.OpenRecordset("TableOrQueryNameHere")

Do Until rst.EOF

   ' do something here

   rst.MoveNext

Loop

rst.Close

Set rst = Nothing

Is there any way i can makes this or get something similar to look thoguh forms data ??? not tbl or query i would like it to adapt to this code...

Private Sub Command17_Click()

Dim rst As DAO.Recordset

Dim strAdrs As String

Dim strBody As String

Dim strSQL As String

Set rst = CurrentDb.OpenRecordset("qrynofollowuptraining")

strSQL = "SELECT [Text] FROM tblEditLetters WHERE [Title] = 'FUA'"

rst.MoveFirst

Do Until rst.EOF

strAdrs = strAdrs & Forms!frmNoFollowUptraining.ClientEmail & " :"

rst.MoveNext

Loop

strAdrs = Left(strAdrs, Len(strAdrs) - 1)

Debug.Print strAdrs

    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

    If Not (rst.BOF And rst.EOF) Then

        DoCmd.SendObject , , , Me.ClientEmail, , , "[BDS]we haven't herd from you in a while", rst![Text]

        If (Err <> 0) Then MsgBox "Error " & Err.Number & vbCrLf & Err.Description

    End If

End Sub

so it only grabs the email addresses on the current form not the TBL client detials :D that simple right? i hope :P

---

R.King the best DB noob

Answer 1

Hi Rik

User the form's RecordsetClone:

Set rst = Me.RecordsetClone ' for code in the form module use "Me"

' For code in another module, use Forms("Form name").RecordsetClone

rst.MoveFirst

Do Until rst.EOF

    strAdrs = strAdrs & rst!ClientEmail & " ;"

    rst.MoveNext

Loop

HTH!

---

Graham Mandeno [Access MVP]

Answer 2

I think the logic you want to use is more like:

Dim rst As DAO.Recordset

Dim strAdrs As String

Dim strBody As String

Set rst = me.RecordsetClone

rst.MoveFirst

Do Until rst.EOF

   strAdrs = strAdrs& "," & rst!ClientEmail

   rst.MoveNext

Loop

strAdrs = Mid(strAdrs, 2)

If Len(strAdrs) > 0 Then

   Debug.Print strAdrs   

   DoCmd.SendObject To:=strAdrs,  _

                                  Subject:="[BDS]we haven't herd from you in a while", _

                                  MessageText:= " ? ? ? "

End If

But, I am no expert in emailing so double check the send object stuff.

Answer 3

Will wait to hear back...

On another note, it's polite (and helpful) if you acknowledge where the code came from...

Polite - Just because

Helpful - If we know what the code was originally written for it will be easier to modify it to fit your specific needs.  Remember, we all tackle this in our *free time* and the more time we have to spend deciphering the code the less time we get to spend answering.

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

Answer 4

Instead of...

Set rst = CurrentDb.OpenRecordset("qrynofollowuptraining")

....

Set rst = Me.RecordsetClone

Not sure I get the double loop but if it works let's not fix it!

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

Answer 5

I think the part you want to change is:

   Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

   If Not (rst.BOF And rst.EOF) Then

        DoCmd.SendObject , , , Me.ClientEmail, , , "[BDS]we haven't herd from you in a while", rst![Text]

        If (Err <> 0) Then MsgBox "Error " & Err.Number & vbCrLf & Err.Description

   End If

If so, try something more like:

   With Me.RecordsetClone

      If .RecordCount > 0 Then

         Do Until .EOF

            DoCmd.SendObject , , , Me.ClientEmail, , , "[BDS]we haven't herd from you in a while", ![Text]

            If (Err <> 0) Then MsgBox "Error " & Err.Number & vbCrLf & Err.Description

         Loop

      End If

   End With