Subform checkbox used as condition to update records

[brharrii]

My subform consists of a list of tasks that are waiting to be verified. in order to verify tasks, the user scrolls through the list of tasks and checks a checkbox (discrepancyverified) on each record they wish to verify. After the user has finished checking all the records they wish to verify, they click a verify button on the main form which should then go back through each record and update the verifieddate value of any that are checked to today.

This is what I have so far:

Private Sub Command19_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
 
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblDiscrepancy", dbOpenDynaset, dbAppendOnly)
Set ctl = Forms!frmReviewTracker!subfrmWorkList.Form!discrepancyVerified
 
 
For Each varItem In ctl
If ctl.Value = True Then
rs.AddNew
rs!Item = ctl.ItemData(varItem)
rs!VerifiedByDate = Now
rs.Update
End If
Next varItem
 
MsgBox "Date Updated"
End Sub

It is currently giving the following error:

Run-time error '438':

Object doesn't support this property or method

When i debug, this line is highlighted:

For Each varItem In ctl

I suspect that I'm messing up the syntax somehow, or perhaps missing something that needs to be included, I'm just not sure what it is.

Any ideas?

Thank you

 

[Cronk]

Firstly, your code is for looping through a list box. A check box does not have a set of items.

Secondly, you are not updating anything in the loop - you are appending records.

Is your check box bound to a field in the table? In which case, the records are being updated when you step to the next line in the sub form.

 

[brharrii]

Thanks Cronk.

To answer your question about the check box, yes it is bound to a field from my table.

I made some changes based on your recommendations and made some good progress. I switched to using a sql string instead as the means of updating the info and that seems to be working well. In case anyone is interested, this is the code I'm using now:

Private Sub Command19_Click()
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim ctl             As Control
    Dim varItem         As Variant
    Dim sql             As String
 
 
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("tblDiscrepancy", dbOpenDynaset, dbAppendOnly)
        Set ctl = Forms!frmReviewTracker!subfrmWorkList.Form!discrepancyVerified
 
 
 
            If ctl.Value = True Then
                sql = "update tblDiscrepancy set discrepancyverifieddate = Now, discrepancyVerifiedByWindowsID = '" & returnUserName() & "' WHERE discrepancyverified = true"
                CurrentDb.Execute sql
                ctl.requery
                Me.discrepancyVerified.Value = False
            End If
End Sub

It still throws an error at the end of the loop where I try to set the check box value back to false, but everything else seems to be working.

error:

Method or data member not found

Thanks!

 

[Cronk]

When you have
Me.discrepancyVerified.Value = False
the Me part refers to the object holding the control.

Which object is holding the control?

Also, the
.Value
after control names, is superfluous and can be omitted (unless you like typing).

Have you snipped the code in your procedure? Because the following lines do nothing (except to take up some CPU cycles.)

 Set rs = db.OpenRecordset("tblDiscrepancy", dbOpenDynaset, dbAppendOnly)
        Set ctl = Forms!frmReviewTracker!subfrmWorkList.Form!discrepancyVerified