checkbox on a continuous form not looping (1 Viewer)

[JeanieGreene]

Hi again. I am trying to make a checkbox on a continuous form, bound to a query, update all the forms that are check with a given "case number" that is entered into a text box. I have the following code, but it is not looping at all. It will only put the data into the line that I clicked on last.

Option Compare Database
Dim rs As DAO.Recordset


Private Sub Command17_Click()
    Set rs = Me.RecordsetClone
    With rs
        rs.MoveFirst
        Do Until rs.EOF
            If Check13 = True Then
                rs.Edit
                CADEventNumber = txtCaseNumber
                rs.Update
            End If
            rs.MoveNext
        Loop
    End With
    rs.Close
    Set rs = Nothing
    Me.Requery
End Sub

Any idea what I am doing wrong?

 

[Galaxiom]

Probably should be

rs!CADEventNumber = txtCaseNumber

 

[JeanieGreene]

Ok, I made that change, and it seems to loop, but now it is filling in all the records between the first clicked record and the last clicked record, but not filling in the last clicked record. It doesn't recognize the check box.

 

[isladogs]

Try moving the checkbox line before the recordset code and use me.check13=true

Also as you are using With rs.... End With, you do not needs to keep using rs. Inside that section

In other words
.Edit
...
.update

Etc

 

[JeanieGreene]

Then I get an error that the object hasn't been defined (rs).

 

[jdraw]

Try putting the Dim rs As DAO.Recordset

right after the Private Sub... line.

 

[JeanieGreene]

I wasn't sure if you meant with moving the Set rs line, so I tried both ways. If I move the Set rs line, I still get the error that the object isn't defined. If I do not move the Set rs line it enters the number into all fields except the record with emphasis.

 

[isladogs]

Combining my comments and Jack's, I think this will work

Option Compare Database
Option Explicit

Private Sub Command17_Click()

    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    
    With rs
        .MoveLast
        .MoveFirst
        Do Until .EOF
		If Me.Check13 = True Then
                     .Edit
                     !CADEventNumber = me.txtCaseNumber
                    .Update
                End If
            .MoveNext
        Loop
        .Close
    End With
        
    Set rs = Nothing
    Me.Requery
End Sub

If not, blame the fact that its almost midnight here

EDIT just fixed a couple of typos

 

[JeanieGreene]

Thank you for trying to help, but I am still having all records updated, except the one record with focus.

I have attached my database, hopefully that will help.

 

[MarkK]

I would do it like this...

Option Compare Database
Option Explicit

Private Const SQL_UPDATE As String = _
    "UPDATE RadioLog " & _
    "SET CADEventNumber = p0 " & _
    "WHERE CheckBox = True " & _
        "AND [Date] >= Date() - 3 " & _
        "AND [Date] <= Date()"

Private Sub Command17_Click()
    Me.Refresh  [COLOR="Green"]'save pending edits in the form[/COLOR]
    If SetCaseNumber(Nz(Me.txtCaseNumber)) Then Me.Requery
End Sub

Public Function SetCaseNumber(CaseNumber As String) As Integer
[COLOR="Green"]'   Runs the the update as defined by SQL_UPDATE
'   Returns the number of records affected
[/COLOR]    With CurrentDb.CreateQueryDef("", SQL_UPDATE)
        .Parameters(0) = CaseNumber
        .Execute dbFailOnError
        SetCaseNumber = .RecordsAffected
        .Close
    End With
End Sub

...and avoid using recordsets altogether. Also, if you decouple the functionality from the button click, you may find at a later date that it's useful to run the now independently available SetCaseNumber() method using some other trigger. This is an advantage of breaking out your functionality into subroutines and decoupling your business logic from your user interface.
hth
Mark

 

[arnelgp]

check if this will solve it.
save all pending update first.
you are using the form's checkbox
and not the recordset's checkbox.

Private Sub Command17_Click()

    ' save all pending update
    Me.Dirty = False
    
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    
    With rs
        .MoveLast
        .MoveFirst
        Do Until .EOF
            If !Checkbox = True Then
                .Edit
                !CADEventNumber = txtCaseNumber
                .Update
            End If
            .MoveNext
        Loop
        .Close
    End With
    
    Set rs = Nothing
    Me.Refresh
End Sub

 

[JeanieGreene]

arnelgp you are the KING!

Thank you all who helped out. You are all wonderful!