How can I do a loop?

[JNariss]

Hello,

My form works great accept for one small flaw.................(of
course right). The code below pulls all the information that is on the
form and submits it into a text message in an email. Well here is the
tricky part. This form is created off a query, so when the form is
opened the user sees multiple records and can place a check next to
each one that pertains to them.

I would like the form to send the details for all the records that have
been checked in the send object command.

The code I am using is:

Private Sub cmdSubmit_Click()
On Error GoTo ErrHandler


Dim strRequest_ID As String
Dim strBrief_Description As String
Dim strMoveNumber As String
Dim strDateToMove As String
Dim strAnalystName As String
Dim strAnalystComment As String
Dim strSubmitAuthorizedBy As String
Dim strMoveProdAuthorizedBy As String
Dim strTo As String
Dim strHeader As String
Dim strMessage As String

strRequest_ID = Me.Request_ID
strBrief_Description = Me.Brief_Description
strMoveNumber = Me.MoveNumber
strDateToMove = Me.DateToMove
strAnalystName = Me.AnalystName
strAnalystComment = Me.AnalystComment
strSubmitAuthorizedBy = Me.SubmitAuthorizedBy
strMoveProdAuthorizedBy = Me.MoveProdAuthorizedBy
strTo = "(e-mail address removed)"
strHeader = "Requests have been 'moved' to production"
strMessage = "The following request(s) have been moved into production:
" & Chr$(13) & Chr$(13) & _
"Request ID: " & strRequest_ID & Chr$(13) & _
"Brief Description: " & strBrief_Description & Chr$(13) & _
"Move Number: " & strMoveNumber & Chr$(13) & _
"Date To Move: " & strDateToMove & Chr$(13) & _
"Analyst: " & strAnalystName & Chr$(13) & _
"Analyst Comment: " & strAnalystComment & Chr$(13) & _
"Submitted To Production By: " & strSubmitAuthorizedBy & Chr$(13) &
Chr$(13) & _
"Moved To Production By: " & strMoveProdAuthorizedBy & Chr$(13) &
Chr$(13) & _
"For futher details regarding this request please go to the database
at: \\egsrosintra1\Databases\SystemChangeRequest.mdb " & _
"Please do not reply to this automated email."

DoCmd.SendObject acSendNoObject, , , strTo, , , strHeader,
strMessage, No, False

DoCmd.Close acForm, "LEO TEST FORM", acSaveYes

MsgBox "You have successfully moved the request(s) into
production" & _
"and notified management and the analyst.", vbOKOnly, "Move
Successful"

ErrHandler:
Resume Next
Exit Sub
End Sub


_______________________________________________________________

This code displays to the user in an email as:

The following request(s) have been moved into production:

Request ID: 50
Brief Description: Prr500 opy function not working causing FLT records
to duplicate.
Move Number: RE45
Date To Move: 6/9/2006
Analyst: Habeggar, Matt
Analyst Comment:
Submitted To Production By: Positano, Mike

Moved To Production By: McAndrews, Leo

For futher details regarding this request please go to the database at:
\\egsrosintra1\Databases\SystemChangeRequest.mdb Please do not reply to
this automated email.

____________________________________________________________________

As you can see the only request that shows up is for "Request ID" 50.
However when I filled out this form I had checked multiple boxes (or
request ids if you will) and only the last one seems to populate into
the email.

Can I somehow add a loop in the code or do something to make it display
all the fields that have been checked?

-Justine

 

[Duncan Bachen]

As you can see the only request that shows up is for "Request ID" 50.
However when I filled out this form I had checked multiple boxes (or
request ids if you will) and only the last one seems to populate into
the email.

Can I somehow add a loop in the code or do something to make it display
all the fields that have been checked?

-Justine

I saw your earlier message too, and assuming this is the same item, so
I'll reply here. These are conceptual ideas, not the actual code you
would need.

My initial thought was in regards to the use of the checkboxes to
determine which records should be moved. You could use the After Update
event of the checkbox to check (no pun intended) on the value of
itself. If it's true, then you add the recordID to a temporary table.
If it's false, then you remove the ID from the table. Then, your submit
button opens the temporary table as a recordset and loops through it,
performing your operation on each record ID it finds.

Second thought to avoid the temporary table portion would be assuming
that the tblRequest has a field in it called 'MoveToProduction'. This is
what's bound to the checkbox, and ultimately displayed on the form.

Now you can check each of the records you want to move and do the same
loop for the movement to the production table.