Looping Through a Continuous Form

[barrylowe]

I have a continous with a number of bound fields on it, the last of which is a checkbox.

When the form is run, the user can select one or more of the records by selecting the apprpriate checkbox. When the user click the OK button some code is executed to run throughall of the rows on the form and create a comma delimted list of the ID numbers.

The code is as follows:

Do While Me.NewRecord = False
MsgBox Me.SampleID
If Me.chkResultsReceived Then
If strReturnedList = "" Then
strReturnedList = Me.SampleID
Else
strReturnedList = strReturnedList & ", " & strReturnedList
End If
End If
DoCmd.GoToRecord acDataForm, Me.Name, acNext
Loop

My problem is that the loop begins at the current row (i.e. the last record selected).

Can anyone tell me how to start from the first row?

Thanks in advance.

 

[HandsOnAccess]

In your code paste the following code above the line Do While Me.NewRecord = False

DoCmd.RunCommand acCmdRecordsGoToFirst

ie.,

DoCmd.RunCommand acCmdRecordsGoToFirst
Do While Me.NewRecord = False
    MsgBox Me.SampleID
    If Me.chkResultsReceived Then
        If strReturnedList = "" Then
            strReturnedList = Me.SampleID
        Else
           strReturnedList = strReturnedList & ", " & strReturnedList
        End If
    End If
    DoCmd.GoToRecord acDataForm, Me.Name, acNext
Loop

 

[PHV]

Another thing to change I guess:
strReturnedList = strReturnedList & ", " & [!]Me!SampleID[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barrylowe]

When I add in the RunCommand line I get the following error:

2046: The command or action 'RecordsGoToFirst' isn't available now.

Any thoughts?

 

[HandsOnAccess]

I assume you have placed the above code in the OK Button On Click Event, Does the event execute any other code prior to this loop, can you paste full code of the OK Button On Click events here

Thanks

 

[MajP]

Personally I would not recommend looping through the form, but always the underlying recordset. I think you have a lot more chances of throwing errors.

  Dim rs As DAO.Recordset
  Dim strReturnedList As String
  Set rs = Me.RecordsetClone
  rs.MoveFirst
  Do While Not rs.EOF
    If rs.Fields("blnResultsRecieved") Then
       strReturnedList = strReturnedList & ", " & rs.Fields("SampleID")
    End If
    rs.MoveNext
 Loop
 If Len(strReturnedList) > 0 Then
   strReturnedList = Mid(strReturnedList, 3)
 End If
 Debug.Print strReturnedList

also move as many if then out of the loop for speed. So I strip the blank comma at the end and only get checked once vice every record.