Microsoft Access Checkbox with SQL Backend Does Not Update

Question

I've been researching it and have found many similar cases... but none exactly the same. I've tried a lot of different resolutions in the previous cases mentioned and none of them fixed this one.

I just recently did a SQL Server Migration and am now troubleshooting the issues that have sprung up with the new SQL back end. This is the last one that I can not figure out:

I have a split form with a check box control, bound to a SQL Server View through a DSN-Less connection. Before the migration the Bound Access Query could not be updated... so I programatically updated the forms checkboxes through VBA using two different methods:

1. Was a check all/ check none checkbox which when clicked checked or unchecked all of the checkboxes in the datasheet.

2. Gave the ability for the user to check or uncheck each individual checkbox by using the checkboxes Mouse_Down Event.

Here is the code for each of the two methods:

    ' Check All/ Check None
    Dim rsSelect As DAO.Recordset
    Dim rsUpdate As DAO.Recordset
    Dim SQL As String
    Dim CurrDb As Database
    Dim currFilter As String

On Error GoTo chkSelect_Click_Error

   ' Capture current filter
    If Me.FilterOn Then currFilter = Me.Filter
    Set rsSelect = Me.RecordsetClone

    Set CurrDb = CurrentDb

     rsSelect.MoveFirst
    Do While Not rsSelect.EOF
         SQL = "SELECT * FROM tblTimesheet WHERE [TimesheetID] = " & rsSelect("TimesheetID")
        Set rsUpdate = CurrDb.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
        If Not rsUpdate.EOF Then
            If Me.chkSelect Then
                With rsUpdate
                    .Edit
                    rsUpdate("TimesheetSelect") = True
                    .Update
                End With
            Else
                 With rsUpdate
                    .Edit
                    rsUpdate("TimesheetSelect") = False
                    .Update
                End With
            End If
        End If
        rsSelect.MoveNext
     Loop
    rsUpdate.Close
    rsSelect.Close
    Me.Requery
    If currFilter > "" Then
        Me.Filter = currFilter
        Me.FilterOn = True
    End If
    If Me.chkSelect Then
        Me.lblSelect.Caption = "Select None"
    Else
        Me.lblSelect.Caption = "Select All"
    End If

   On Error GoTo 0
   Exit Sub

chkSelect_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure chkSelect_Click of VBA Document Form_frmTimesheetSummary"

And Secondly:

    ' Check/ Uncheck Individual Checkbox
    Dim rsUpdate As DAO.Recordset
    Dim SQL As String
    Dim CurrDb As Database
    Dim currFilter As String

   ' Capture current filter
    If Me.FilterOn Then currFilter = Me.Filter

    Set CurrDb = CurrentDb
     SQL = "SELECT * FROM tblTimesheet WHERE [TimesheetID] = " & Me.TimesheetID
    Set rsUpdate = CurrDb.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
    If Not rsUpdate.EOF Then
        If Me.TimesheetSelect Then
            With rsUpdate
                .Edit
                rsUpdate("TimesheetSelect") = False
                .Update
            End With
        Else
             With rsUpdate
                .Edit
                rsUpdate("TimesheetSelect") = True
                .Update
            End With
        End If
    End If


    rsUpdate.Close
    Me.Form.Requery
    'Me.Repaint
    Me.Refresh
    If currFilter > "" Then
        Me.Filter = currFilter
        Me.FilterOn = True
    End If

Both of these procedures worked with an Access back end... but the "Check Individual" procedure refuses to work now. When I check a checkbox it does update the SQL Backend... but the control itself refuses to update the new status... I of course have tried Requery, but also Repaint and Refresh and it refuses to update unless I completely close the form and reopen it again.

The real kicker in all this is that the Check All method still works! I've spent hours on this and am hoping to get some fresh eyes on it because it should be working if the backend is updating!!

ADDITIONAL NOTES ADDED LATER: In response to some of the great reasoning below I feel I should include this additional notes:

1. I am using SQL Server 2012 and the SQL Server Native Client 11.0 Driver for my connection string.

2. I am using Microsoft Access 2010 32 bit

3. The SQL Server field is a bit I've removed all nulls and set allow nulls to 'no' with a default of 0

Answer 1

Thanks to all who posted... the answer that ended working for me in this case was ditching the split form... I had heard it suggested that split forms can be problematic at times so redesigned the form in a standard Parent/ Subform setup which completely resolved the issue.

Answer 2

If Andre's suggestions don't solve it, instead of Me.Requery, try resetting the form's recordsource. Me.RecordSource = . You don't say what version of SQL Server you are using or which ODBC driver. Make sure you are using the correct ODBC Driver for your version of SQL Server and not the default 'SQL Server' driver.

Answer 3

Some things come to mind:

1) You can replace the whole rsUpdate construction in the second procedure by this:

SQL = "UPDATE tblTimesheet SET TimesheetSelect = " & _
      IIf(Me.TimesheetSelect, "0", "-1") & _
      " WHERE TimesheetID = " & Me.TimesheetID
CurrDb.Execute SQL, dbSeeChanges

It depends on your TimesheetSelect datatype whether you should use "-1" or "1".

2) Me.Form.Requery should be Me.Requery.

3) If it still doesn't work, adding a TIMESTAMP column to tblTimesheet might help Access recognize that the record was changed. This is generally a good thing to have, but shouldn't be necessary.

Recommended reading: https://stackoverflow.com/a/2861581/3820271
tblTimesheet does have a primary key, doesn't it?