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

[arnelgp]

your Delete query, does it have parameters also.
post the SQL please.

 

[abette]

I changed the .Execute "qryDeleteRecsFromTempIMPORT" and it's still giving me the same error message. The name is correct.

 

[abette]

YES!!

 

[arnelgp]

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]

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]

UGH -

 

[abette]

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]

its database not databases

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

 

[abette]

I am so sorry but now it's giving me this error message on the .Value

 

[arnelgp]

use post #28 code.

 

[abette]

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]));

 

[abette]

YES!! It works. THANK YOU SO MUCH!!!!!