Clear all checkboxes on form

[Tommo]

Hi,

Please see the attached screenshot of my vba.

Basically the form on the left is generated when i create a new class term. I then want to tick the classes that are being added to the new term and when i hit the button it needs to;

Save (To update the table)
Run the append query. (To add them to the new term)
Clear all the checkboxes.
Save again
Close.

Sometimes it works. Most of the time it works on all but the last checkbox i check.

ie If i check 5 boxes it will only append 4 and only clear 4.

Been working on this for ages. Any help please.

[ranman256]

If these check boxes are unbound on a form,
make a routine for them all. Don't uncheck 4 of 5, just uncheck them all. ChkBox1.value = false.

If they are fields (bound) then run 1 single update query.

[June7]

ranman, I expect there is one bound checkbox otherwise all records would have same value when checked/unchecked.

Can't read the code in the image. Copy/paste code into post. Use CODE tags to retain indentation.

Probably, the record for the last checkbox checked must first be committed to table. This happens when move to another record, close form, or run code. One method: DoCmd.RunCommand acCmdSaveRecord

[Tommo]

So the checkboxes are bound to a field in the table the form relates to.
The query checks for true values on this field and then appends, adding 1 to the term. Hence adding to the new term.
That's why i need the values changed to false once it has run.


Sorry didnt check the image.

Private Sub cmdPassToNewTerm_Click()

DoCmd.Save
DoCmd.OpenQuery "qryAddClassesToNewTerm", , acEdit

    Dim i As Integer
    
    Dim db As Database
    Dim rs As Recordset
    Dim ClassNumberOfRecords As Long
    
    Set db = CurrentDb
    Set rs = db.OpenRecordSet("tblClasses")
  
    ClassNumberOfRecords = DCount("ClassID", "tblClasses")
  
            rs.MoveLast
            rs.MoveFirst
            
            For i = 0 To ClassNumberOfRecords - 1
                If rs.Fields("Copy") = True Then
                    rs.Edit
                    rs.Fields("Copy") = False
                    rs.Update
                End If
                
            rs.MoveNext
            Next I
        
    rs.Close
    Set rs = Nothing
    db.Close
    
DoCmd.Save
DoCmd.RefreshRecord


DoCmd.Close




End Sub

[June7]

Did you try the suggestion about first committing record?

I think DoCmd.Save has nothing to do with saving record.

Do you just want to set all records in table back to False? Instead of looping recordset, could:

CurrentDb.Execute "UPDATE tblClasses SET Copy=False"

Is this a multi-user database? Could there be conflicts between users checking/unchecking this field?

[Tommo]

In theory it doesnt have to be a multi user db. We will see in practice if people can do as they are told.

Will try your suggestion

[Tommo]

Did you try the suggestion about first committing record?

I think DoCmd.Save has nothing to do with saving record.

Do you just want to set all records in table back to False? Instead of looping recordset, could:

CurrentDb.Execute "UPDATE tblClasses SET Copy=False"

Is this a multi-user database? Could there be conflicts between users checking/unchecking this field?

Yeh that works. Thanks man.

FFS i spent ages working out all that RS shit.


lol

Thanks.