Solved Import process runs append query: is there a way to alternate the message depending on the record count? (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:11
Joined
May 7, 2009
Messages
19,169
your Delete query, does it have parameters also.
post the SQL please.
 

abette

Member
Local time
Today, 16:11
Joined
Feb 27, 2021
Messages
85
I changed the .Execute "qryDeleteRecsFromTempIMPORT" and it's still giving me the same error message. The name is correct.
 

abette

Member
Local time
Today, 16:11
Joined
Feb 27, 2021
Messages
85
YES!!
1625149157015.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:11
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub Command82_Click()
    dim db as dao.databases
    dim p as parameter
    set db=currentdb

    
    with db
        with .querydefs("qryDeleteRecsFromTempImport")
            for each p in .parameters
                .value = eval(.name)
            next
            .execute
        end with
        with .querydefs("qryImportClaimsFromCYBER")
            for each p in .parameters
                .value = eval(.name)
            next
            .execute       
            if .RecordsAffected <> 0 Then
                msgbox .RecordsAffected & " Claims successfully imported from CYBER."   
            else
                mgbox "No Claims were imported from CYBER."
            end if
        end with
    end with
end sub
 

abette

Member
Local time
Today, 16:11
Joined
Feb 27, 2021
Messages
85
FYI I just removed the parameter in the Delete query and it's still giving me the same error message.
DELETE tblClaimsWorkingTableTEMP.*
FROM tblClaimsWorkingTableTEMP;
 

abette

Member
Local time
Today, 16:11
Joined
Feb 27, 2021
Messages
85
Hi - I just realized it's probably because the import query is reading and appending records from a table that is linked from a SQL database! Maybe it's that table it's not recognizing because it's linked? I am guessing....
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:11
Joined
May 7, 2009
Messages
19,169
its database not databases
Code:
Private Sub Command82_Click()
    Dim db As DAO.Database
    Dim p As Parameter
    Set db = CurrentDb

  
    With db
        With .QueryDefs("qryDeleteRecsFromTempImport")
            For Each p In .Parameters
                p.Value = Eval(p.Name)
            Next
            .Execute
        End With
        With .QueryDefs("qryImportClaimsFromCYBER")
            For Each p In .Parameters
                p.Value = Eval(p.Name)
            Next
            .Execute
            If .RecordsAffected <> 0 Then
                MsgBox .RecordsAffected & " Claims successfully imported from CYBER."
            Else
                MsgBox "No Claims were imported from CYBER."
            End If
        End With
    End With
end sub
 
Last edited:

abette

Member
Local time
Today, 16:11
Joined
Feb 27, 2021
Messages
85
I am so sorry but now it's giving me this error message on the .Value
1625151259163.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:11
Joined
May 7, 2009
Messages
19,169
use post #28 code.
 

abette

Member
Local time
Today, 16:11
Joined
Feb 27, 2021
Messages
85
Here's the SQL for the Import query that has parameters in it.
INSERT INTO tblClaimsWorkingTableTEMP ( ClaimNumber, AuthNumber, DateOfServiceFrom, DateOfServiceTo, ProviderCIMID, PayeeName, FEIN_SSN, DateReceived, MemberID, YouthName, DxCode, DxCode2, DxCode3, DxCode4, ServiceCode, ServiceDescription, LineNumber, UnitsRequested, AmountBilled, ClaimStatus, DenialReasonComments, ProcessedBy, DateProcessed, ClaimsFundYear2, ClaimsFundYear, ClaimsFundType )
SELECT dbo_tblClaimsAttestation.claimsId, dbo_tblClaimsAttestation.AuthNumber, dbo_tblClaimsAttestation.ServiceDateFrom, dbo_tblClaimsAttestation.ServiceDateTo, dbo_tblClaimsAttestation.CIMID, dbo_tblClaimsAttestation.ProviderName, dbo_tblClaimsAttestation.FEIN, dbo_tblClaimsAttestation.DateReceived, dbo_tblClaimsAttestation.MemberID, dbo_tblClaimsAttestation.YouthName, dbo_tblClaimsAttestation.DxCode, dbo_tblClaimsAttestation.DxCode2, dbo_tblClaimsAttestation.DxCode3, dbo_tblClaimsAttestation.DxCode4, dbo_tblClaimsAttestation.ServiceCode, dbo_tblClaimsAttestation.ServiceDesciption, dbo_tblClaimsAttestation.Line, dbo_tblClaimsAttestation.UnitsReq, dbo_tblClaimsAttestation.AmountBilled, dbo_tblClaimsAttestation.ClaimStatus, dbo_tblClaimsAttestation.ClaimStatusReason, dbo_tblClaimsAttestation.Prosessedby, dbo_tblClaimsAttestation.UpdateDate, dbo_tblClaimsAttestation.ClaimsfundYear, "FY"+Left([ClaimsFundYear2],2) AS ClaimsFundYearNew, dbo_tblClaimsAttestation.ClaimsFundType
FROM dbo_tblClaimsAttestation
WHERE (((dbo_tblClaimsAttestation.ClaimStatus)="Approved" Or (dbo_tblClaimsAttestation.ClaimStatus)="Returned" Or (dbo_tblClaimsAttestation.ClaimStatus)="Denied") AND ((dbo_tblClaimsAttestation.UpdateDate) Between [Forms]![frmImportClaimsFromCYBER]![StartDateRange] And [Forms]![frmImportClaimsFromCYBER]![EndDateRange]));
 

Users who are viewing this thread

Top Bottom