Do While loop with Me.Recordsource

[Darhl Thomason]

I've been reading up on Do While loops and For Each loops. I think the Do
While loop is the one I want, but everything I have read talks about DAO
recordsets or ADO recordsets. My recordset is defined in the code as
Me.Recordset. Will the Do While loop work with this type of recordset?

Thanks,

Darhl

 

[Ken Snell \(MVP\)]

Depends upon what you are going to do, but the form's recordset is usually a
DAO recordset. Mind you, it's often better to use the RecordsetClone instead
of the Recordset because the "clone" doesn't trigger the Current event and
it also avoids many other possible issues.

 

[Darhl Thomason]

Ken,

Thanks for replying. I want to loop through the records and send an email
to each of the owners who have a store in the group. I've been looking and
reading and found a little help on Google, but it's still not quite right.
I went ahead and created a new recordset for the loop, but it doesn't go to
the next record, it always stays on the record that was displayed in the
form. For example, on the form I have a cmdEmail that calls the code to
loop through the recordset. Let's say there are 20 records that match the
criteria I've given the recordset. If I am displaying store MI026, then it
does loop through 20 times, but each time it uses store MI026. If I move
through the records on the form and select store AZ004, then hit cmdEmail,
it again loops 20 times but uses store AZ004 for each "stop" in the loop.
My code is below. Any help or guidance on a better way is highly
appreciated!

Darhl

Private Sub RecordSet_Loop()
On Error GoTo Err_RecordSet_Loop
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset(Me.RecordSource)
Do While Not rs.EOF
If IsNull(Me.txtFirstContact) Then
If MsgBox("Are you sure you want to send this owner the checklist?",
vbYesNo) = vbYes Then
Call subSendMessage
End If
Else
strMsgBox = Me.txtPMINumber & " has already received their checklist."
MsgBox strMsgBox, vbExclamation, "Redundancy Check"
End If
rs.MoveNext
Loop
rs.Close
Exit Sub
Err_RecordSet_Loop:
MsgBox Err.Number & " " & Err.Description
End Sub

 

[Darhl Thomason]

Ken,

I tried using the RecordSetClone, but don't really understand it. I'm
getting the same problem. Here is a copy of my new code:

Thanks,

Darhl

Private Sub RecordSet_Loop()
On Error GoTo Err_RecordSet_Loop
Do While Not Forms!frmStoreData.RecordsetClone.EOF
If IsNull(Me.txtFirstContact) Then
If MsgBox("Are you sure you want to send this owner the checklist?",
vbYesNo) = vbYes Then
Call subSendMessage
End If
Else
strMsgBox = Me.txtPMINumber & " has already received their checklist."
MsgBox strMsgBox, vbExclamation, "Redundancy Check"
End If
Forms!frmStoreData.RecordsetClone.MoveNext
Loop
Exit Sub
Err_RecordSet_Loop:
MsgBox Err.Number & " " & Err.Description
End Sub

 

[Douglas J. Steele]

It's difficult to say without knowing what code is in subSendMessage.
However, what you're showing refers to Me.txtFirstContact: in other words,
it's referring to a specific text box on the form. Looping through the
Recordset does not change what's in the referenced text box. If
subSendMessage is also referring to text boxes on the form, that's likely
your problem.

You need to refer to fields in the RecordsetClone instead. If, for example,
txtFirstContact is bound to field FirstContact, you'd refer to

If IsNull(Forms!frmStoreData.RecordsetClone!FirstContact) Then

or

If IsNull(Forms!frmStoreData.RecordsetClone.Fields("FirstContact")) Then

 

[Darhl Thomason]

Thanks Doug, that did the trick.

My next problem is with updating a field. Part of my email routine is to
check and see if FirstContactDate is null. If it is, then it send an email
to the store owner and needs to update the FirstContactDate field. Here is
the code I am using to do this:

Forms!frmStoreData.RecordsetClone!FirstContactDate = Date

It's giving me this error "update or cancelupdate without addnew or edit".
I searched the help files and found some references to this, but it's not
clear to me.

Thanks!

Darhl

 

[Ken Snell \(MVP\)]

You must open the recordsetclone for editing before you change a value in
it, and then update the recordset:

With Forms!frmStoreData.RecordsetClone
.Edit
!FirstContactDate = Date
.Update
End With

 

[Darhl Thomason]

Thanks Ken and everyone else! That did the trick.

Darhl