I unfortunately am having the same issue... I've been researching it and have found many similar cases... but so far this seems to be the closest.
For me I am not new to Access development or VBA but I've been stuck on this one for hours and am reaching out. I can however provide more background as to why my situation is running into this problem.
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... currently the form is not updatable because the form is bound to a view through a DSN-Less connection.
Here is the ringer: I have a check all check box at the top of the form... and even though the bound form is not updatable I update the records like so:
Here is the kicker... I replicated this same code for when a user click's an individual checkbox... and this formely worked with the Access back end... but now I can't get the individual checkbox to update but the check all/ none code still works!
Here is the code for the individual checkbox:
I run this code on the Checkboxes MouseDown Event. I hope this makes sense... so in short my first bit of code which checks or unchecks all of the checkboxes works... but my second bit which a user would use to check each one individually does not work. But note it is updating the back end... If I close and reopen the form like the SO then it shows the updated records checked... but I can not get it to update... AND I've tried what worked for the SO i.e. Repaint, and Refresh... although I really feel all that should be needed is a Requery.
Any help in this would be greatly appreciated!
For me I am not new to Access development or VBA but I've been stuck on this one for hours and am reaching out. I can however provide more background as to why my situation is running into this problem.
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... currently the form is not updatable because the form is bound to a view through a DSN-Less connection.
Here is the ringer: I have a check all check box at the top of the form... and even though the bound form is not updatable I update the records like so:
Code:
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"
Here is the code for the individual checkbox:
Code:
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
Any help in this would be greatly appreciated!
Comment