Form executing but not updating table on sql server (1 Viewer)

[equus]

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

 

[Pat Hartman]

When you post code, please use the code tool to retain the formatting. Code that is left aligned is much harder to read than is properly formatted code.

Not sure why you are using unbound forms. You need to write a lot more code when you use unbound forms. You also need to know how forms work so you can simulate what Access does for you with NO code when you use bound forms.

Add a break in the code and step through it line by line. print the SQL string to the debug window after it is built. Copy and paste it into SSMS and run it. What happens?

What is Q1?

You cannot set a RecordSource property to an update query. A RecordSource is a SELECT query, NEVER an action query.

 

[equus]

Thanks for you response. We are using linked tables to SQL Server. We upgraded from ACCESS 2010.adp to ACCES 2013 accdb.

Q1 is the continuation of the select statement based on the user.

When it's necessary to use a Sql Server Stored procedure for you source code, then you would have an unbound form. QryApproveTrackOpen is a stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER  proc [dbo].[QRY_TRACKING]
@sr int,
@er int
as
declare @approve bit, @smd_status_code char(1), @count_tests int
    select @approve = smd_approve_track
        from dbo.Tbl_Sample_details
        where smd_id = @sr
    select @smd_status_code = smd_status_code
        from dbo.Tbl_Sample_Details
        where smd_id = @sr
    select @count_tests = count(tsk_taskid) from dbo.tbl_tasks
        where tsk_smd_id = @sr
if @smd_status_code in ('B', 'R') or @count_tests = 0
        SELECT        a.sm_group_id, a.sm_program, d.dw_lnomen, e.ct_contract, c.CTR_CONTRACTOR, a.sm_lot_nr, a.sm_test_doc_rev, b.smd_serial_number, b.smd_id, a.sm_dw_rev, d.dw_dwg, a.sm_job_order, d.dw_mk, i.PER_LNAME,
                         b.smd_score, j.man_name, b.smd_req_date, a.sm_warn_esd, a.sm_warn_expl, a.sm_photo_digital, a.sm_photo_station, a.sm_rpt_cert, a.sm_rpt, a.sm_rpt_other, a.sm_photo_video, a.sm_warn_chemical,
                         b.smd_approve_track, NULL AS NAME, b.smd_approve_date, i.Per_Phone, a.sm_remarks, NULL AS tsk_ts_test_time, NULL AS sc_spec, NULL AS tsk_ts_number, NULL AS td_short_description, NULL AS tsk_ts_proc_para, NULL
                          AS tsk_StartDate, NULL AS operator, NULL AS tsk_score, NULL AS tsk_EndDate, NULL AS tsk_ts_proc, 'HARDWARE IS ' + k.Classification AS Classification
FROM            TBL_SAMPLE AS a LEFT OUTER JOIN
                         TblSecurityClass AS k ON k.ClassID = a.sm_ClassID INNER JOIN
                         TBL_SAMPLE_DETAILS AS b ON a.sm_group_id = b.smd_sm_group_id INNER JOIN
                         TBL_DRAWING AS d ON a.sm_dw_id = d.dw_id INNER JOIN
                         TBL_CONTRACT AS e ON a.sm_ct_counter = e.ct_counter INNER JOIN
                         TBL_PERSONNEL AS i ON a.sm_engineer = i.PER_ID INNER JOIN
                         TBL_CONTRACTOR AS c ON e.ct_ctr_code = c.CTR_CODE INNER JOIN
                         TBL_MANUFACTURERS AS j ON a.sm_man_code = j.man_code
WHERE        (b.smd_id BETWEEN @sr AND @er)
ORDER BY b.smd_id
    
    else
    
    SELECT        a.sm_group_id, a.sm_program, d.dw_lnomen, e.ct_contract, c.CTR_CONTRACTOR, a.sm_lot_nr, a.sm_test_doc_rev, b.smd_serial_number, b.smd_id, a.sm_dw_rev, d.dw_dwg, a.sm_job_order, d.dw_mk, i.PER_LNAME,
                         b.smd_score, j.man_name, b.smd_req_date, g.sc_spec, a.sm_warn_esd, a.sm_warn_expl, a.sm_photo_digital, a.sm_photo_station, a.sm_rpt_cert, a.sm_rpt, a.sm_rpt_other, a.sm_photo_video, a.sm_warn_chemical,
                         b.smd_approve_track, LEFT(l.PER_FNAME, 1) + '. ' + l.PER_LNAME + ' SIGNED ELECTRONICALLY' AS NAME, b.smd_approve_date, l.Per_Phone, a.sm_remarks, f.tsk_ts_number, h.td_short_description, f.tsk_ts_proc_para,
                         f.tsk_StartDate, f.tsk_ts_test_time, k.PER_LNAME AS operator, f.tsk_score, f.tsk_EndDate, f.tsk_ts_proc, 'HARDWARE IS ' + m.Classification AS Classification
FROM            TBL_SAMPLE AS a LEFT OUTER JOIN
                         TblSecurityClass AS m ON m.ClassID = a.sm_ClassID INNER JOIN
                         TBL_SAMPLE_DETAILS AS b LEFT OUTER JOIN
                         TBL_PERSONNEL AS l ON l.PER_ID = b.smd_approve_track ON a.sm_group_id = b.smd_sm_group_id INNER JOIN
                         TBL_DRAWING AS d ON a.sm_dw_id = d.dw_id INNER JOIN
                         TBL_CONTRACT AS e ON a.sm_ct_counter = e.ct_counter INNER JOIN
                         TBL_TASKS AS f LEFT OUTER JOIN
                         TBL_PERSONNEL AS k ON k.PER_ID = f.tsk_per_test_operator ON b.smd_id = f.tsk_smd_id INNER JOIN
                         TBL_SCENARIO AS g ON b.smd_sc_seq_id = g.sc_seq_id INNER JOIN
                         TBL_TEST_DESCRIPTIONS AS h ON f.tsk_td_counter = h.td_counter INNER JOIN
                         TBL_PERSONNEL AS i ON a.sm_engineer = i.PER_ID INNER JOIN
                         TBL_CONTRACTOR AS c ON e.ct_ctr_code = c.CTR_CODE INNER JOIN
                         TBL_MANUFACTURERS AS j ON a.sm_man_code = j.man_code
WHERE        (b.smd_id BETWEEN @sr AND @er)
ORDER BY b.smd_id, f.tsk_ts_number

 

[Pat Hartman]

You didn't address the other two points. You just posted more code with no explanation of what, if anything you want us to look at.
I couldn't see where Q1 was being used in the code. It was being populated but not used in the query.
You cannot set a RecordSource to an Action query. You can only use a Select query.

 

[equus]

We no longer have a Q1. We rewrote the code and it compiles with no errors. That, when the report is opened, there is not data, which tells me that it is not executing the stored procedure. I don't know why the code isn't executing the stored procedure, which I provided in my previous comment. This is the new code in the form's module:

Private Sub Report_Open(Cancel As Integer)
Dim SR As String
Dim ER As String
Dim db As Database
Dim rst As DAO.Recordset
Dim qdef As DAO.QueryDef

CheckUser
SetStartUpOptions Me
Set db = CurrentDb
'Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, MY_CONNECTION)
Set qdef = CurrentDb.CreateQueryDef("")

If gCurrentUser <> "dbo" Then
    Select Case gCurrentGroup
        Case "PRODUCTION CONTROL", "ENGINEER", "MANAGER", "ALTMANAGER", "TECHNICIAN", "CALIBRATION"
            DoCmd.Maximize
        Case Else
            MsgBox "You are not authorized to access this menu."
            DoCmd.CancelEvent
            Exit Sub
    End Select
End If

If IsNull(Me.OpenArgs) Or Me.OpenArgs = "" Then
    SR = Trim(InputBox("Enter Starting CATTS ID"))
    
ER = Trim(InputBox("Enter Ending CATTS ID"))
Else
    SR = Me.OpenArgs
    ER = Me.OpenArgs
End If
If ER = "" Then ER = SR
If SR <> "" And ER <> "" Then
    If IsNumeric(SR) = True And IsNumeric(ER) = True Then
        If ER >= 10000 Or ER >= SR Then
            On Error Resume Next
            qdef.Connect = CurrentDb.TableDefs("dbo_TBL_SAMPLE_DETAILS").Connect
            qdef.SQL = "exec dbo.QRY_TRACKING " & SR & ", " & ER
            qdef.ReturnsRecords = True
            qdef.OpenRecordset
            On Error Resume Next
            
            If Err Then MsgBox Error$: DoCmd.CancelEvent
        Else
            MsgBox "The Starting CATTS ID is greater than the Ending CATTS ID.  Report aborted."
            DoCmd.CancelEvent
        End If
    Else
        MsgBox "The Starting or Ending CATTS ID you entered is not numeric.  Report aborted."
        DoCmd.CancelEvent
    End If
Else
    MsgBox "You did not enter a Starting or an Ending CATTS ID.  Report aborted."
    DoCmd.CancelEvent
    
End If
 
 
End Sub

 

[Minty]

Remove or comment out the
On Error Resume Next

And see if it raises an error, which might explain why "nothing" appears to happen.

 

[equus]

I commented out the On Error Resume Next and received no error and report still doesn't work. It opens, but there is no data.

 

[Minty]

How are you setting the reports recordsource? I can't see how it is being assigned the query result.

Save the pass-through query as qptMyReport.
Then set your reports recordsource to qptMyReport.

Use your code to modify that pass through query.

 

[equus]

The recordsouce is now set to the Query_Tracking. When we run the report, it execute and brings up data, but not the parameters we passed. So now it looks like the parameters being provided aren't actually getting passed and what ever was stored in earlier executions, is what runs. What would the syntax be in the SQL view in Access to look for the parameters?

 

[Minty]

If I'm creating a qdef updates on the fly I would do it more like this;

    qdef.Connect = CurrentDb.TableDefs("dbo_TBL_SAMPLE_DETAILS").Connect
    qdef.SQL = "exec dbo.QRY_TRACKING " & SR & ", " & ER
    qdef.ReturnsRecords = True
    qdef.Close
    
    '''For Debugging
    Docmd.OpenQuery "Query_Tracking" , acViewNormal, acReadOnly

This will open your query after it is set up.

I actually use a public sub for this in a lot of databases connected to SQL server;

Public Sub sSendToPT_Generic(strQuery As String, bReturnRecs As Boolean)
    Dim db As Database
    Dim qDef As QueryDef
    
    Set db = CurrentDb()
    
    Set qDef = db.QueryDefs("qPT_Generic")
    qDef.Connect = db.TableDefs("tb_A_LinkedTableInYourDB").Connect
    qDef.SQL = strQuery
    qDef.ReturnsRecords = bReturnRecs
    qDef.Close
    If Not bReturnRecs Then
        db.Execute "qPT_Generic", dbFailOnError
    End If
  
    Set qDef = Nothing
    Set db = Nothing

End Sub

 

[equus]

Fixed the code and the actual parameters for the query aren't getting passed and I don't know why. Linked tables is very new to me and I don't understand it all.
As far as the Public Sub, how does your code know what "qPT_Generic" is?

 

[Minty]

As far as the Public Sub, how does your code know what "qPT_Generic" is?

It's a pass-through query I set up as a hold-all query to continually re-use.

Parameters - Check your query SQL - I normally create it into a string then debug.print it something like

Dim strSQL as String

strSQL = "exec dbo.QRY_TRACKING " & SR & ", " & ER
debug.print strSQL

If you copy the result of that into SSMS and execute it should give you results or demonstrate why you aren't getting any.
Alternatively, simply open your saved query Query_Tracking in access in design view - it should have the last values from when you ran it.

EDIT : Just noticed this;
SR = Me.OpenArgs
ER = Me.OpenArgs

Doesn't this mean SR & ER are always the same value?