Microsoft Access Checkbox with SQL Backend Does Not Update
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:
Was a check all/ check none checkbox which when clicked checked or unchecked all of the checkboxes in the datasheet.
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"
' 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:
I am using SQL Server 2012 and the SQL Server Native Client 11.0 Driver for my connection string.
I am using Microsoft Access 2010 32 bit
The SQL Server field is a bit I’ve removed all nulls and set allow nulls to ‘no’ with a default of 0
3 Solutions collect form web for “Microsoft Access Checkbox with SQL Backend Does Not Update”
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”.
Me.Form.Requery should be
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?
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.
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.