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

abette

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
Hi - I have a macro (mcrImportClaims) that is attached to the OnClick Event of a button that resides on a Form. This simple macro executes an append query (qryImportClaims) that reads records from a SQL table and appends them to my Access table. The macro runs a delete query first, then the Append query and displays a message. The reality is - not all the time are records imported so I would like to know if there's a way to differentiate the message based on record count? So if the record count > 0 display "Claims successfully imported." Else "Zero records imported." I have used If/Else logic in macros before and actually tried to create a 3rd query that checks my table and counts by the ID field but I can't refer to the query object in my macro. I get an error message. Any suggestions on how I can go about making this work so messages display according to the correct scenario?
1625096075215.png

Thank you,
Ann Marie
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:07
Joined
Oct 29, 2018
Messages
21,357
Have you tried using DCount()?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:07
Joined
May 7, 2009
Messages
19,169
first create a Function for msgbox in a Module.
i don't think you can pass a variable to the macro messagebox?

Public Function msgbx(byval s as variant)
msgbox s
end function

Copy qryImportClaimsFromCYBER to qrySelectClaimsFromCYBER.
Modify the New query and remove the "Insert Into..." and leave
only the "SELECT" part of the query to make it into a Select Query.

insert a Code to your macro, before running OpenQuery, qryImportClaimsFromCYBER:

RunCode
Name: msgbx(DCount("1","qrySelectClaimsFromCYBER"))
 

abette

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
Thank you for your quick reply.


I am receiving this message when I click the form button (on click event) to execute the macro
1625100781064.png


Here's my embedded macro
1625100824599.png


1625100923765.png

Here's my function (vba module)
1625100876216.png

Also, how will I display messages based on the record count? So if the record count = 0 display message A else display message B.
Thank you. :)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:07
Joined
Feb 28, 2001
Messages
26,999
OK, I'll tell you another way to do this, but it requires VBA.

Convert your macro to VBA. (There is a ribbon item to do that.)

Now you will see several DoCmd.RunSQL commands that reference your queries.

Change each of DoCmd.RunSQL queryname to CurrentDB.Execute queryname, dbFailOnError

After each of the .Execute commands, use

Code:
If CurrentDB.RecordsAffected = 0 Then
    MsgBox "Something didn't work for queryname."
End if

Macros can have very limited logic. Converting everything to VBA gives you precision control over what and when you do things. If you use the DB.Execute method you can query the DB object (whatever you choose to call it) to see how many records were affected by the operation.

If you are not yet comfortable with VBA, then file my comments away as something for you to attempt your future.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:07
Joined
May 7, 2009
Messages
19,169
can you show the SQL string of the query:

qrySelectClaimsFromCYBER
 

abette

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
Here's the SQL string of the query qrySelectClaimsFromCYBER

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

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
Good Morning - the vba code is not working. I am getting this message when I run the process
1625145422549.png
 

abette

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
I literally copied and pasted the query name into the code. it's not misspelled.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:07
Joined
May 7, 2009
Messages
19,169
if you are using VBA, there is no need for the custom msgbox
Code:
Private Sub Command82_Click()
    dim db as dao.database
    set db=currentdb
   
    with db
        .execute "qryDeleteRecsFromTempImport", dbfailonerror
        .execute "qryImportClaimsFromCYBER", dbfailonerror
        if .RecordsAffected <> 0 Then
            msgbox .RecordsAffected & " Claims successfully imported from CYBER."  
        else
            smgbox "No Claims were imported from CYBER."
        END IF
    end with
end sub
 

abette

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
Hi again - So I made some adjustments to the VBA code. It seems to prefer the DoCmd better than execute. If I comment the Call msgbx function it runs, however it drops into the CurrentDb.RecordsAffected = 0 message when there were 8 recs imported. When I remove the comment and let it execute the Call msgbx I get this error
1625146446657.png
 

abette

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
if you are using VBA, there is no need for the custom msgbox
Code:
Private Sub Command82_Click()
    dim db as dao.database
    set db=currentdb
  
    with db
        .execute "qryDeleteRecsFromTempImport", dbfailonerror
        .execute "qryImportClaimsFromCYBER", dbfailonerror
        if .RecordsAffected <> 0 Then
            msgbox .RecordsAffected & " Claims successfully imported from CYBER." 
        else
            smgbox "No Claims were imported from CYBER."
        END IF
    end with
end sub
 

abette

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
Hi - I tried this VBA code but it's failing on the .Execute

1625146697477.png
 

abette

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
It's failing on the .Execute statement. the yellow highlighting isn't showing.
 

abette

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
So if I modify the VBA and use the DoCmd statement it runs the queries, however, it always falls through the If .recordsAdffected = 0 message and displays 'No claims were imported from CYBER.'

1625147196215.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:07
Joined
May 7, 2009
Messages
19,169
that is because your Insert query has a parameter referring to a Form control.
Code:
Private Sub Command82_Click()
    dim db as dao.databases
    dim p as parameter
    set db=currentdb

    
    with db
        .execute "qryDeleteRecsFromTempImport", dbfailonerror
        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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:07
Joined
May 7, 2009
Messages
19,169
t always falls through the If .recordsAdffected = 0 message
because you are not Using (.execute) the db at all, what are you using is the DoCmd object.
 

abette

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
Hi Again - the user inputs a date range on the Form and when they click Import button is should not kick off this VBA code. I need the Form field date ranges in order to select the correct range of records to import. So should I copy your new code? Thank you! Dang do you know your VBA code and Access. Unfortunately, I am a beginner. LOL
 

abette

Member
Local time
Today, 10:07
Joined
Feb 27, 2021
Messages
85
Ugh...I am sorry but I copied and pasted your latest VBA code and now I am getting this:
1625148203600.png
 

Users who are viewing this thread

Top Bottom