Greetings all - I have a form that is based on a SP that was originally coded in ACCESS 2010 and now we are "using" ACCESS 2013. The form runs fine and behaves like it is updating the record (messages displayed), but never actually updates the table. Tried a lot of different approaches, but now joy. The code is on a button click event.
Original code from Access 2010 attached, new code as follows:
NEW CODE FOR FRMAPPROVETRACKING ACCESS 2013
Private Sub cmdApprove_Click()
Dim i As Integer
Dim Q As String
Dim rst As Recordset
Dim db As Database
Dim Q1 As String
CheckUser
Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, MY_CONNECTION)
'check for current record
If IsNull(smd_id) Then MsgBox "You do not have a current tracking form to approve. Operation Aborted": Exit Sub
'Prompt for approval with yes/no messagebox. If no (7) exit sub
i = MsgBox("You have requested to approve this Tracking Form. By continuing this process, you are certifying that all information on this form is correct. Are you sure you want to approve this form?", 4)
If i = 7 Then Exit Sub
qdef.Connect = CurrentDb.QueryDefs("dbo.QryAproveTrackOpen").Connect
qdef.ReturnsRecords = True
qdef.Execute
'update date approved and person approving 97 is Production Control else the user logged in
Q = "update dbo.TBL_SAMPLE_DETAILS " & _
"Set dbo.TBL_SAMPLE_DETAILS.smd_approve_date = getDate() "
If gCurrentUser = "dbo" Or gCurrentGroup = "PRODUCTION CONTROL" Then
Q1 = Q1 = "dbo.TBL_SAMPLE_DETAILS.smd_approve_track = 97 "
Else
If gCurrentUser = "dbo" Or gCurrentGroup = "MANAGER" Or gCurrentGroup = "ALTMANAGER" Then
Q1 = Q1 & "dbo.TBL_SAMPLE_DETAILS.smd_approve_track = " & GUserID & " "
Else
If gCurrentUser = "dbo" Or gCurrentGroup = "ENGINEER" Then
Q1 = Q1 & "dbo.TBL_SAMPLE_DETAILS.smd_approve_track = " & GUserID & " "
End If
End If
End If
Q = Q & "where dbo.TBL_SAMPLE_DETAILS.smd_id = " & smd_id & ";"
Set rst = db.OpenRecordset(Q, dbOpenSnapshot, dbSQLPassThrough)
On Error Resume Next
If Err Then
MsgBox "The following error occurred while approving the Tracking Form. " & Error$ & " Please report this to Production Control."
Else
MsgBox "The Tracking Form was electronically approved. Production Control is being notified electronically to continue processing."
Me.Requery
End If
rst.Close
Me.RecordSource = Q
End Sub
Original code from Access 2010 attached, new code as follows:
NEW CODE FOR FRMAPPROVETRACKING ACCESS 2013
Private Sub cmdApprove_Click()
Dim i As Integer
Dim Q As String
Dim rst As Recordset
Dim db As Database
Dim Q1 As String
CheckUser
Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, MY_CONNECTION)
'check for current record
If IsNull(smd_id) Then MsgBox "You do not have a current tracking form to approve. Operation Aborted": Exit Sub
'Prompt for approval with yes/no messagebox. If no (7) exit sub
i = MsgBox("You have requested to approve this Tracking Form. By continuing this process, you are certifying that all information on this form is correct. Are you sure you want to approve this form?", 4)
If i = 7 Then Exit Sub
qdef.Connect = CurrentDb.QueryDefs("dbo.QryAproveTrackOpen").Connect
qdef.ReturnsRecords = True
qdef.Execute
'update date approved and person approving 97 is Production Control else the user logged in
Q = "update dbo.TBL_SAMPLE_DETAILS " & _
"Set dbo.TBL_SAMPLE_DETAILS.smd_approve_date = getDate() "
If gCurrentUser = "dbo" Or gCurrentGroup = "PRODUCTION CONTROL" Then
Q1 = Q1 = "dbo.TBL_SAMPLE_DETAILS.smd_approve_track = 97 "
Else
If gCurrentUser = "dbo" Or gCurrentGroup = "MANAGER" Or gCurrentGroup = "ALTMANAGER" Then
Q1 = Q1 & "dbo.TBL_SAMPLE_DETAILS.smd_approve_track = " & GUserID & " "
Else
If gCurrentUser = "dbo" Or gCurrentGroup = "ENGINEER" Then
Q1 = Q1 & "dbo.TBL_SAMPLE_DETAILS.smd_approve_track = " & GUserID & " "
End If
End If
End If
Q = Q & "where dbo.TBL_SAMPLE_DETAILS.smd_id = " & smd_id & ";"
Set rst = db.OpenRecordset(Q, dbOpenSnapshot, dbSQLPassThrough)
On Error Resume Next
If Err Then
MsgBox "The following error occurred while approving the Tracking Form. " & Error$ & " Please report this to Production Control."
Else
MsgBox "The Tracking Form was electronically approved. Production Control is being notified electronically to continue processing."
Me.Requery
End If
rst.Close
Me.RecordSource = Q
End Sub