Append query does not run from code (1 Viewer)

[pbuethe]

Hello,

I am calling 3 append queries from my code. The first and the third queries are working fine. The second query works from design view or the database window but does not work from the code. The query is called exactly the same way as the other two. I compiled the code and did compact and repair and it still did not run from the code. What could be causing this? Thanks for any help.

 

[BlueIshDan]

Lets try showing us the code. This isn't a theoretical problem lol

 

[pbaldy]

And how about something more specific than "does not work". Error, doesn't run at all, etc. One possibility is that if you use the Execute method, the query can't have form references in it. I think you get the "too few parameters" error.

 

[Franklin]

This is extremely decent and average post....You shook posting it....Thanks a considerable measure for posting it...!!!



_________________
J0hn

 

[pbuethe]

The code is:

Public Sub TransferCleanData(strAUID As String, strRevType As String, strSample As String)

Dim rst As Recordset2
Dim qry As QueryDef
Dim n As Integer

On Error GoTo Err_TransferCleanData
    
    Set qry = CurrentDb.QueryDefs("qryTransferToCleanQueries")
    Set rst = qry.OpenRecordset
    rst.MoveLast

    With Form_frmMaintainErrorLog
        .ProgressBar = 0
        .ProgressBar.Min = 0
        .ProgressBar.Max = rst.RecordCount + 2
    End With
    
<snipped irrelevant code>    
    
    'Transfer Clean Data
    MsgBox "Transfer To Clean"
   ' rst.MoveFirst
   ' Do Until rst.EOF
      '  MsgBox rst!Name
      '  ExecuteQuery rst!Name, strAUID, strRevType, strSample

      MsgBox "Transfer Header"
        ExecuteQuery "qappHeaderClean", strAUID, strRevType, strSample
        Form_frmMaintainErrorLog.ProgressBar = Form_frmMaintainErrorLog.ProgressBar + 1
        MsgBox "Transfer HTL"
        ExecuteQuery "qappHTLClean", strAUID, strRevType, strSample
        Form_frmMaintainErrorLog.ProgressBar = Form_frmMaintainErrorLog.ProgressBar + 1
        MsgBox "Transfer Tracking"
        ExecuteQuery "qappTrackingClean", strAUID, strRevType, strSample
        Form_frmMaintainErrorLog.ProgressBar = Form_frmMaintainErrorLog.ProgressBar + 1
    '    rst.MoveNext
   ' Loop
    
    rst.Close
    Set rst = Nothing
    
    qry.Close
    Set qry = Nothing
        
    'Set Progress Bar
    Form_frmMaintainErrorLog.ProgressBar = 0
    Form_frmMaintainErrorLog.ProgressBar.Visible = False
        
Exit_TransferCleanData:
    Exit Sub

Err_TransferCleanData:
    MsgBox Err.Description, vbCritical
    Resume Exit_TransferCleanData
        
End Sub

What happens on running this code:

The expected number of records are appended to tblHeaderClean by qappHeaderClean.
Nothing is appended to tblHTLClean by qappHTLClean as expected. There is also no error message or indication that the query is running.
The expected number of records are appended to tblTrackingClean by qappTrackingClean.

Other notes:
The query qryTransferToCleanQueries produces a recordset of the three queries in order to run them in a loop. I commented out the looping code and put in separate calls to each query in the hope it would make it work but it didn't.
ExecuteQuery is a generic procedure to run parameter queries.

 

[CJ_London]

All very interesting, but since the problem is within ExecuteQuery, it would be worthwhile seeing that code, plus the sql of the qappHTLClean.

Have you tried stepping through the code?

Also, can you confirm which version of Access you are using for the backend.

 

[pbuethe]

ExecuteQuery:

'**************************************************************************************
'- Is called by multiple subs in various modules
'- to assign parameter to a query and then execute the query
'**************************************************************************************
Public Sub ExecuteQuery(qryName As String, Optional qryParameter0 As Variant, Optional qryParameter1 As Variant = "X", _
                        Optional qryParameter2 As Variant = "X", Optional qryParameter3 As Variant = "X", _
                        Optional qryParameter4 As Variant = "X", Optional qryParameter5 As Variant = "X")

On Error GoTo Err_ExecuteQuery

    Dim qry As New QueryDef

    Set qry = CurrentDb.QueryDefs(qryName)
    qry.Parameters(0) = qryParameter0

    If qryParameter1 <> "X" And qryParameter1 <> "Null" Then
        qry.Parameters(1) = qryParameter1
    ElseIf qryParameter1 = "Null" Then
        qry.Parameters(1) = Null
    End If
    
    If qryParameter2 <> "X" And qryParameter2 <> "Null" Then
        qry.Parameters(2) = qryParameter2
    ElseIf qryParameter2 = "Null" Then
        qry.Parameters(2) = Null
    End If
    
    If qryParameter3 <> "X" Then
        qry.Parameters(3) = qryParameter3
    End If

    If qryParameter4 <> "X" Then
        qry.Parameters(4) = qryParameter4
    End If

    If qryParameter5 <> "X" Then
        qry.Parameters(5) = qryParameter5
    End If
    qry.Execute
    qry.Close
    
Exit_ExecuteQuery:
    Exit Sub

Err_ExecuteQuery:
    MsgBox Err.Description, vbCritical
    Resume Exit_ExecuteQuery
    
End Sub

qappHTLClean:

PARAMETERS pAUID Text ( 255 ), pSample Text ( 255 ), pRevType Text ( 255 );
INSERT INTO tblHTLClean ( CaseID, AUID, Sample, RevType, ListType, DataYear, Form_Id,TestDoc, TreatedEmer, RefusedTest, PreviousTest, LackingCapacity, Verbal, Literature, ChecklistForm, InfoPkg, UnkMedia, OralConsent, WrittenConsent, ResultPos, ResultToPatient, PrimaryProv, ThirdParty, ModifiedBy, ModifiedDate )
SELECT tblHTLDirty.CaseID, tblHTLDirty.AUID, tblHTLDirty.Sample, tblHTLDirty.RevType, tblHTLDirty.ListType, tblHTLDirty.DataYear, tblHTLDirty.Form_Id, tblHTLDirty.TestDoc, tblHTLDirty.TreatedEmer, tblHTLDirty.RefusedTest, tblHTLDirty.PreviousTest, tblHTLDirty.LackingCapacity, tblHTLDirty.Verbal, tblHTLDirty.Literature, tblHTLDirty.ChecklistForm, tblHTLDirty.InfoPkg, tblHTLDirty.UnkMedia, tblHTLDirty.OralConsent, tblHTLDirty.WrittenConsent, tblHTLDirty.ResultPos, tblHTLDirty.ResultToPatient, tblHTLDirty.PrimaryProv, tblHTLDirty.ThirdParty, tblHTLDirty.ModifiedBy, tblHTLDirty.ModifiedDate
FROM (tblHeaderDirty INNER JOIN tblHTLDirty ON tblHeaderDirty.CaseID = tblHTLDirty.CaseID) LEFT JOIN tblHTLClean ON tblHTLDirty.CaseID = tblHTLClean .CaseID
WHERE (((tblHTLDirty.AUID)=[pAUID]) AND ((tblHTLDirty.Sample)=[pSample]) AND ((tblHTLDirty.RevType)=[pRevType]) AND ((tblHeaderDirty.PRAID) Is Not Null) AND ((tblHeaderDirty.PRADate) Is Not Null) AND ((tblHTLClean .CaseID) Is Null)) OR (((tblHTLDirty.AUID)=[pAUID]) AND ((tblHTLDirty.Sample)=[pSample]) AND ((tblHTLDirty.RevType)=[pRevType]) AND ((tblHeaderDirty.PRADate) Is Not Null) AND ((tblHTLClean .CaseID) Is Null) AND ((tblHeaderDirty.TempID) Is Not Null));

This is in Access 2007.

 

[CJ_London]

in your sql, the parameters are in order

pAUID, pSample and pRevType

in your call the order is

strAUID, strRevType, strSample

Is this the same for the other two queries?

 

[pbuethe]

CJ_London,

That could be it. I will change the order of the parameters in the query. After I receive the next set of data, I will run the code again and let you know if it works.

 

[pbuethe]

Yes, changing the order of the parameters worked. All the expected records were appended to tblHTLClean by running the code. Thanks!

 

[CJ_London]

glad it is resolved - for the future, please provide as much relevant info as possible in the initial post, it does save time for everyone