CheckBoxes on a SubForm - Items to be on Report 1

[jw5107]

I have a main form w/ a subform - the subform is a continous form with a query for the recordsource. There is a checkbox for each record on the subform. I would like for the user to be able to "check" each checkbox - then click on a command button to view a report. This report is to show each item - FROM - that subform that has been "checked". The code below is what I am working with. I works as long as the first checkbox is "checked" on the subform. If the subform has 10 records, and only 3 are "checked", and the "checked" checkboxes are at the bottom of the subform (or recordset), the report won't open - I get the message created from the code below...
Any examples or suggestions...????
Thanks in advance,
jw5107

Private Sub cmdViewCvrSht_Click()
On Error GoTo Err_cmdViewCvrSht_Click
Const conCannotGoToRecord = 3021
Dim rs As DAO.Recordset
Dim stDocName As String
stDocName = "WeekendCoverSheet"
Set rs = Forms!CoverSheetMx!PartsSubFrm.Form.RecordsetClone
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
If rs.Fields("ForCoverSheet").Value = True Then
DoCmd.OpenReport stDocName, acPreview
Exit Sub
Else
DisplayMessage "No parts are 'checked' to be on the Cover Sheet"
Exit Sub
End If
rs.Update
rs.MoveNext
Loop
DoCmd.Hourglass False
Set rs = Nothing
Exit_cmdViewCvrSht_Click:
DisplayMessage "This EO doesn't have any required material/parts - can't view Cover Sheet at this time"
Exit Sub
Err_cmdViewCvrSht_Click:
If Err <> conCannotGoToRecord Then
DisplayMessage Err.DESCRIPTION
End If
Resume Exit_cmdViewCvrSht_Click
End Sub

 

[Remou]

It is much easier to see what is happening if you indent your code.

It is also a good idea to use [ignore]

[/ignore] to mark your code in Tek-Tips, it makes it easier to read, so people in a hurry will answer you rather than passing on to an easier to read question.

Private Sub cmdViewCvrSht_Click()
On Error GoTo Err_cmdViewCvrSht_Click
Const conCannotGoToRecord = 3021
Dim rs As DAO.Recordset
Dim stDocName As String
stDocName = "WeekendCoverSheet"
Set rs = Forms!CoverSheetMx!PartsSubFrm.Form.RecordsetClone
rs.MoveFirst

Do While Not rs.EOF
'you do not need edit, you are not updating the recordset
'rs.Edit
'This means that the report will open at the first True checkbox encountered ...
    If rs.Fields("ForCoverSheet").Value = True Then
        DoCmd.OpenReport stDocName, acPreview
        Exit Sub
    Else
... And the code will terminate at the first False
        DisplayMessage "No parts are 'checked' to be on the Cover Sheet"
        Exit Sub
    End If
'See Edit comment
'rs.Update
rs.MoveNext
Loop
DoCmd.Hourglass False
Set rs = Nothing

'This will never be true, from the above
Exit_cmdViewCvrSht_Click:
DisplayMessage "This EO doesn't have any required material/parts - can't view Cover Sheet at this time"
Exit Sub
Err_cmdViewCvrSht_Click:
If Err <> conCannotGoToRecord Then
DisplayMessage Err.Description
End If
Resume Exit_cmdViewCvrSht_Click
End Sub

Private Sub cmdViewCvrSht_Click()
On Error GoTo Err_cmdViewCvrSht_Click
Const conCannotGoToRecord = 3021
Dim rs As DAO.Recordset
Dim stDocName As String
stDocName = "WeekendCoverSheet"

If Forms!CoverSheetMx!PartsSubFrm.Form.Recordset.RecordCount=0 Then
    DisplayMessage "This EO doesn't have any required material/parts - can't view Cover Sheet at this time"
    Exit Sub
End If
Set rs = Forms!CoverSheetMx!PartsSubFrm.Form.RecordsetClone

rs.MoveFirst

TickCount=0
Do While Not rs.EOF
    If rs.Fields("ForCoverSheet").Value = True Then
        TickCount=TickCount+1
    End If
    rs.MoveNext
Loop

If TickCount=0 Then
    DisplayMessage "No parts are 'checked' to be on the Cover Sheet"
Else
    DoCmd.OpenReport stDocName, acPreview
    Exit Sub
End If
DoCmd.Hourglass False
Set rs = Nothing

Exit_cmdViewCvrSht_Click:
Exit Sub

Err_cmdViewCvrSht_Click:
'If Err <> conCannotGoToRecord Then
'DisplayMessage Err.Description
'End If
Resume Exit_cmdViewCvrSht_Click
End Sub

Or there abouts.

 

[jw5107]

remou,

Awesome, your examples worked perfect..!!! Thanks for droppin' knowledge about the rs.Edit/rs.Update part...
I better understand how that works now..!!

Star for ya..!!!!!!!
Thanks,
jw5107