Solved VBA Code to run a series of queries and to check the record count and based on record count either continue or display message (1 Viewer)

[abette]

Hi I have this embedded macro that is attached to a command button on my Form. I need this to be VBA code since I have to add a step to execute a select query that checks records against 2 tables to see if the record on my Temp table already has the same records on the "real" table. I also have to display a message to the user if the records already exist and if they don't continue with the process. I know how to convert a macro to VBA code, however, I think I need to use the .execute method and I am not sure how to code it.

Here's my converted macro:

Would you help me convert to the correct VBA code so it runs through the first 2 queries and then based on the results of the 2nd query continues with the rest of the steps. So If the record count from the 2nd query >0 display a message and stop processing Else continue with the steps and display message that the records were attested.
From my previous post yesterday, a gentleman (arnelgp) was able to help me create this VBA module that is attached to a different form's control button. I think I would have to used this type of process and statements.


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 = Eval(p.Name)
Next
.Execute
End With
With .QueryDefs("qryImportClaimsFromCYBER")
For Each p In .Parameters
p = 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

Thank you - Ann Marie

 

[theDBguy]

You shouldn't need to convert the embedded macro to VBA, but it could also be a good practice for future developments. I will suggest the same idea I gave you earlier when trying to determine the number of records in a query using a macro, and that is, try to use the DCount() function.

This time, you can use DCount() to check for duplicates.

 

[abette]

But How? Do I put the DCount() directly in my macro? I am asking for assistance with the syntax. Thank you.

 

[arnelgp]

the table tblClaimsWorkingTableTEMP has ClaimNumber.

now, what is the name of the Final table that the above table will be appended?
what are the corresponding field names?

if there are different ClaimNumber on the Temp table, it is wise to use recordset and get
each ClaimNumber.
we need to loop to each record and compare if the ClaimNumber is already on the "other" table.

another point to consider is if "some" records on the Temp table have been "inserted" to the
other table and there are other Claims not yet inserted, would the code continue to Insert
records (only those not yet inserted)?

 

[theDBguy]

But How? Do I put the DCount() directly in my macro? I am asking for assistance with the syntax. Thank you.

Yes, you could use DCount() in a macro. For example:

 

[abette]

I will try it now. Thank you!

 

[abette]

the table tblClaimsWorkingTableTEMP has ClaimNumber.

now, what is the name of the Final table that the above table will be appended?
what are the corresponding field names?

if there are different ClaimNumber on the Temp table, it is wise to use recordset and get
each ClaimNumber.
we need to loop to each record and compare if the ClaimNumber is already on the "other" table.

another point to consider is if "some" records on the Temp table have been "inserted" to the
other table and there are other Claims not yet inserted, would the code continue to Insert
records (only those not yet inserted)?

It's a replica table. The TEMP is basically a copy of tblClaimsWorkingTable. The Temp will receive imported records based on a date range entered by user. The temp is like a working table. Once they review the claims they need to attest them. The attested records get appended to tblClaimsWOrkingTable. The data elements are identical. Once the records are appended I delete recs from TEMP.

 

[abette]

Yes, you could use DCount() in a macro. For example:

View attachment 92757

Question - instead of a table can the Dcount reference a Query?

 

[abette]

Question - instead of a table can the Dcount reference a Query?

I modified my Macro

1. Can Dcount reference a Query? If so then every time I run this it appends the records so Dcount isn't > 0.
If I run the query independent of the macro is it picking up records. These records should not be appended to the real table since they already exist there. Here's the SQL version of my query

SELECT tblClaimsWorkingTableTEMP.ClaimNumber, tblClaimsWorkingTableTEMP.AuthNumber, tblClaimsWorkingTableTEMP.DateOfServiceFrom, tblClaimsWorkingTableTEMP.DateOfServiceTo, tblClaimsWorkingTableTEMP.ProviderCIMID, tblClaimsWorkingTableTEMP.PayeeName, tblClaimsWorkingTableTEMP.FEIN_SSN, tblClaimsWorkingTableTEMP.DateReceived, tblClaimsWorkingTableTEMP.DateProcessed, tblClaimsWorkingTableTEMP.MemberID, tblClaimsWorkingTableTEMP.YouthName, tblClaimsWorkingTableTEMP.DxCode, tblClaimsWorkingTableTEMP.DxCode2, tblClaimsWorkingTableTEMP.DxCode3, tblClaimsWorkingTableTEMP.DxCode4, tblClaimsWorkingTableTEMP.ServiceCode, tblClaimsWorkingTableTEMP.LineNumber, tblClaimsWorkingTableTEMP.UnitsRequested, tblClaimsWorkingTableTEMP.AmountBilled, tblClaimsWorkingTableTEMP.ClaimStatus, tblClaimsWorkingTableTEMP.DenialReasonComments, tblClaimsWorkingTableTEMP.ProcessedBy, tblClaimsWorkingTableTEMP.ClaimsFundYear, tblClaimsWorkingTableTEMP.ClaimsFundYear2, tblClaimsWorkingTableTEMP.ClaimsFundType, tblClaimsWorkingTableTEMP.AttestationCheck, tblClaimsWorkingTableTEMP.AttestationName, tblClaimsWorkingTableTEMP.AttestationDate

FROM tblClaimsWorkingTableTEMP LEFT JOIN tblClaimsWorkingTable ON tblClaimsWorkingTableTEMP.ClaimNumber = tblClaimsWorkingTable.ClaimNumber

WHERE (((tblClaimsWorkingTableTEMP.AttestationCheck)=True) AND ((tblClaimsWorkingTableTEMP.AttestationDate)=[tblClaimsWorkingTable]![AttestationDate]));

 

[theDBguy]

Question - instead of a table can the Dcount reference a Query?

Yes, DCount() can count the records in a query too.

Sent from phone...

 

[abette]

I got it working! Thank you for pointing me in the right direction with this. I learned something new.

 

[Isaac]

@abette

By beginning to consider moving your Macros to VBA code instead, you are steering your ship in an excellent direction that has a big payoff in so many ways it is hard to even assess them now.

I think it's great that you're doing that and just wanted to encourage you, if you so choose, to move in the pure-VBA direction. It is such an enriching experience, even apart from the value it will provide your apps...

 

[arnelgp]

you are using Left Join, so it will always have a Record to return?
you should use a Recordset and compare each ClaimNumber to the WorkingTable.
And you only Append those not in Working table.

you're current setup is if it At least find a ClaimNumber in WorkingTable, then
it will not append at all.