Выполнение нескольких хранимых процедур (Access & SQL)

Среда, 29 Марта 2017 г. 17:26 + в цитатник

В ниже приведенном коде, 1-ый и 2-ой блок по отдельности работают,
но как только пытаюсь их вместе включить, появляется следующая ошибка "Object variable or With block variable not set".
Не понимаю, что здесь может мне мешать.

Private Sub cmd_add_Click()

Private Sub cmd_add_Click()
On Error GoTo Er
Dim idpath As String
idpath = Forms![gamokvleva_add]![idpatient_history]
Dim t As Currency
t = Nz(DMax("nomer", "dbo_baza", "id_patient_history=" & idpath), 0) + 1

' ----------------------------------------  1-ый блок
Call OpenProcSql_In
If (Forms![gamokvleva_add]![check_other] = -1) Then
    qdf.SQL = "Exec proc_insert_baza_1 @idpathistory=" & idpath & ", @nomer=" & t & ", @idregtest=" & Me.id_registration_test
Else
    qdf.SQL = "Exec proc_insert_baza_2 @idpathistory=" & idpath & ", @nomer=" & t & ", @idregtest=" & Me.id_registration_test
End If
qdf.ReturnsRecords = False
Call OpenProcSql_Out


' ---------------------------------------- 2-ой блок
Call OpenProcSql_In
If Forms![gamokvleva_add]![numsqesi] = 1 Then
   qdf.SQL = "EXEC proc_insert_bazapatienttests_1 @idpathistory=" & idpath & ", @idregtest=" & Me.id_registration_test
Else
   qdf.SQL = "EXEC proc_insert_bazapatienttests_2 @idpathistory=" & idpath & ", @idregtest=" & Me.id_registration_test
End If
qdf.ReturnsRecords = False
Call OpenProcSql_Out


' ---------------------------------------- 
Call OpenProcSql_In
    Dim rst As DAO.Recordset
    Set dbsReport = CurrentDb
    Set qdf = dbsReport.QueryDefs("sproc_summa_idpathistory_fasi")
        qdf.SQL = "EXEC proc_summa_idpathistory_fasi @idpathistory=" & idpath
        qdf.ReturnsRecords = True
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
Dim dsumma As Currency
dsumma = rst.Fields(0)
Call OpenProcSql_Out

    Dim t1 As Currency
        t1 = dsumma - (dsumma * (Nz(Forms![registratura_newedit]![procent]))) / 100
    Call OpenProcSql_In
    If (Nz(Forms![registratura_newedit]![procent]) = 0) Then
        qdf.SQL = "EXEC proc_update_gamokvleva_add_1 @idpatienthistory=" & idpath & ", @salarostanxa=" & t1
    Else
        qdf.SQL = "EXEC proc_update_gamokvleva_add @idpatienthistory=" & idpath & ", @salarostanxa=" & t1
    End If
    Call OpenProcSql_Out
    
Set dbsReport = CurrentDb
Set qdf = dbsReport.QueryDefs("sproc_select_baza")
    qdf.SQL = "EXEC proc_select_baza @idpathistory=" & idpath
    qdf.ReturnsRecords = True
    Forms![registratura_newedit]![registratura_newedit_subform].Form.RecordSource = "sproc_select_baza"
    Forms![registratura_newedit]![salaros_tanxa].Requery
    Forms![registratura_newedit]![baratis_tanxa].Requery
    
Ex: Exit Sub
Er: MsgBox Err.Description
    Resume Ex
End Sub

OpenProcSql_In /// OpenProcSql_Out

Public qdf As QueryDef
Public frm As Form
Public dbsReport As DAO.Database
Public rstReport As DAO.Recordset

Function OpenProcSql_In()
    qdf.Connect = "ODBC;" & _
            "Driver={SQL Server Native Client 11.0};" & _
            "Server=SQL;" & _
            "Database=HHHHH; UID=SSS; PWD=PPPPPPPPP;" & _
            "Trusted_Connection=yes;"
End Function

Function OpenProcSql_Out()
    qdf.ReturnsRecords = False
    qdf.Execute dbFailOnError
    Set qdf = Nothing
    Set dbsReport = Nothing
End Function