Moving through Recordset does not always see checkbox as true, even if checked.

[waubain]

I have a main single form and a continuous subform that is not bound to the main form. The main form contain information on a medical procedure and the subform contain all the medical references for all the procedures.

The user can click a checkbox (double state) on each reference record in the subform for any reference thatsupports the procedure. There can be many. There is a button in the subform that runs the below code and adds each record to a table if the checkbox is true

This is where it gets weird. Typically (but not always) it will add all the records that are checked, and then all the checkboxes are unchecked with an update query. If I then decide to add additional records all the records are added but the last record checked. If I check 4 boxes 3 are added, if I check 1, then 0 are added. The Update query will reset all the checkbox to false except the one record that was not added and that will remain checked. I double checked and the checkbox is only double state. I tried to debug with some counters and it loops through the complete recordset.

It is as if the checkbox, although checked, does not have a value of True

Can anyone see something in my code that is not quite right.

Thanks.

I attached my sample database.

Private Sub cmdAddReferences_Click()

    Dim db          As DAO.Database
    Dim rstSource   As DAO.Recordset
    Dim rstInsert   As DAO.Recordset
    Dim strInSQL    As String
    Dim strSoSQL    As String
    Dim lngLoop     As Long
    Dim fkOutcomeID As Integer
    
    Set db = CurrentDb
    
    fkOutcomeID = Forms![frmOutcomeJunction]![intOutcomeID].Value
    'Debug.Print fkOutcomeID

    strInSQL = "SELECT * FROM tblReferencesEd6Junction"
    strSoSQL = "SELECT * FROM tblReferencesEd6"

    Set rstInsert = db.OpenRecordset(strInSQL)
    Set rstSource = db.OpenRecordset(strSoSQL)
    
    'i = 0
    With rstSource
        If Not (.EOF And .BOF) Then
        .MoveFirst
            Do Until .EOF = True
                If .Fields("SelectChkBox") = True Then
                    With rstInsert
                        .AddNew
                            rstInsert!fk_OutcomeCodeID.Value = fkOutcomeID
                            rstInsert!fk_Reference6ID.Value = rstSource!pk_Reference6ID
                        .Update
                    End With
                End If
                .MoveNext
            Loop
            Else
                MsgBox "There are no records in the recordset."
        End If
    End With
    
    'Clean up
    rstSource.Close
    rstInsert.Close
    db.Close
  
    Set db = Nothing
    Set rstSource = Nothing
    Set rstInsert = Nothing
      
    DoCmd.SetWarnings False
    'Uncheck all checkboxes in Reference table
    DoCmd.RunSQL ("UPDATE tblReferencesEd6 SET tblReferencesEd6.SelectChkBox = False WHERE (((tblReferencesEd6.SelectChkBox)=True))")
    DoCmd.SetWarnings True
    
    'Requery the all forms
    Forms![frmOutcomeJunction].Requery
    Forms![frmOutcomeJunction]![subfrmReferences].Requery

End Sub

You don't know what you don't know...

 

[waubain]

I got to thinking that since it was always the last box to be checked that did not get included that possibly that record was not saved when the recordset was called.

I added an onclick event to the checkbox and this seems to have solved the problem.

Private Sub chkSelect_Click()

DoCmd.RunCommand (acCmdSaveRecord)

End Sub

You don't know what you don't know...