You must use the dbSeeChanges option with Open Recordset whenaccessing a SQL Server table that has a

[gjbankos]

Please accept my apologies for my ignorance in advance. I am learning and I appreciate any feedback on this issue that I may receive. I inherited an access database that I migrated to SQL 2008 and I am getting the above error in this code:
Private Sub Restore_Click()
Dim n As Integer
Dim lngID As Long
Dim frm As Form_frmFrontEnd

On Error GoTo RestoreError:

DoCmd.Hourglass True
DoCmd.SetWarnings False

With SelectedData(Me.lstInactive)
' alert the user when no records were selected
If .Count = 0 Then
MsgBox "Please select a record before " & _
"attempting to restore it.", vbInformation
GoTo ExitRestore:
End If

' get confirmation from user to restore record(s)
msg = MsgBox("Are you sure you want to restore these record(s)?", vbQuestion + vbYesNo)
If msg = vbNo Then
GoTo ExitRestore:
End If

' initials progress meter
SysCmd acSysCmdInitMeter, "Restoring Records Please Wait...", .Count

For n = 1 To .Count
' retrieve the ID number for later use
lngID = .Item(n)

' execute query to restore records
With CurrentDb.QueryDefs(RESTORE_BIDS_QUERY)
.Parameters![ID] = lngID
.Execute

' alert user when a record could not be restore
If .RecordsAffected = 0 Then
MsgBox "Could not reactive Record #" & lngID, vbExclamation
End If
End With

' update progress meter
SysCmd acSysCmdUpdateMeter, n
Next n

' refresh the list of records in the listbox
Me.lstInactive.Requery
End With

' refresh the front end form
If CurrentProject.AllForms(FRONT_END_FORM).IsLoaded = True Then
Set frm = Form_frmFrontEnd
frm.Requery
End If
ExitRestore:
Set frm = Nothing
SysCmd acSysCmdClearStatus
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Sub

RestoreError:
MsgBox Err.Description, vbCritical
Resume ExitRestore:
End Sub



I've seen other threads that discuss this error, but the code example isn't the same as what I have and I'm not sure how to incorporate the dbSeeChanges command within this code.

Any assistance would be greatly appreciated and thank you in advance.

 

[Douglas J Steele]

Change

..Execute

to

..Execute dbSeeChanges

In fact, you'd probably be better off using

..Execute dbSeeChanges + dbFailOnError


wrote in message

Please accept my apologies for my ignorance in advance. I am learning and I
appreciate any feedback on this issue that I may receive. I inherited an
access database that I migrated to SQL 2008 and I am getting the above error
in this code:
Private Sub Restore_Click()
Dim n As Integer
Dim lngID As Long
Dim frm As Form_frmFrontEnd

On Error GoTo RestoreError:

DoCmd.Hourglass True
DoCmd.SetWarnings False

With SelectedData(Me.lstInactive)
' alert the user when no records were selected
If .Count = 0 Then
MsgBox "Please select a record before " & _
"attempting to restore it.", vbInformation
GoTo ExitRestore:
End If

' get confirmation from user to restore record(s)
msg = MsgBox("Are you sure you want to restore these record(s)?",
vbQuestion + vbYesNo)
If msg = vbNo Then
GoTo ExitRestore:
End If

' initials progress meter
SysCmd acSysCmdInitMeter, "Restoring Records Please Wait...", .Count

For n = 1 To .Count
' retrieve the ID number for later use
lngID = .Item(n)

' execute query to restore records
With CurrentDb.QueryDefs(RESTORE_BIDS_QUERY)
.Parameters![ID] = lngID
.Execute

' alert user when a record could not be restore
If .RecordsAffected = 0 Then
MsgBox "Could not reactive Record #" & lngID,
vbExclamation
End If
End With

' update progress meter
SysCmd acSysCmdUpdateMeter, n
Next n

' refresh the list of records in the listbox
Me.lstInactive.Requery
End With

' refresh the front end form
If CurrentProject.AllForms(FRONT_END_FORM).IsLoaded = True Then
Set frm = Form_frmFrontEnd
frm.Requery
End If
ExitRestore:
Set frm = Nothing
SysCmd acSysCmdClearStatus
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Sub

RestoreError:
MsgBox Err.Description, vbCritical
Resume ExitRestore:
End Sub



I've seen other threads that discuss this error, but the code example isn't
the same as what I have and I'm not sure how to incorporate the dbSeeChanges
command within this code.

Any assistance would be greatly appreciated and thank you in advance.