Hi - This is the last piece of this project I have been working on. I have a Form that allows user to attest to reviewing records by marking each checkbox control and then clicking an Attest button. This will open an Attestation form which allows the user to "attest" to completing the review. This button runs an embedded macro that is attached to the button's On Click event property. So it basically append the records being attested from a Temp table to the 'Real" table and then deletes the records from the Temp table.
\
Macro
Here's my issue: How do I prevent records that were previously attested from being appended to the table again, thus creating duplicates? I know normally the user will be processing a week's worth of data based on date range but say they attest this week's data and then do it again for some reason. How can I prevent the append from running if the records are already on the "real" table? My Temp table is a duplicate design of the "real" table. It's just used to process records imported that will be reviewed and then once attested, moved to the "real" table.
Here's the SQL view of my query qryAppendAttestedRecsFromTEMP:
INSERT INTO tblClaimsWorkingTable ( ClaimNumber, AuthNumber, DateOfServiceFrom, DateOfServiceTo, ProviderCIMID, PayeeName, FEIN_SSN, DateReceived, DateProcessed, MemberID, YouthName, DxCode, DxCode2, DxCode3, DxCode4, ServiceCode, LineNumber, UnitsRequested, AmountBilled, ClaimStatus, DenialReasonComments, ProcessedBy, ClaimsFundYear, ClaimsFundYear2, ClaimsFundType, AttestationCheck, AttestationName, AttestationDate )
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
WHERE (((tblClaimsWorkingTableTEMP.AttestationCheck)=True));
I tried using criteria for most of the fields based on the value of the field on the "real" table but then when it executed is displayed the information as prompts.
Thank you for your assistance and patience with me.
Ann Marie
Macro
Here's my issue: How do I prevent records that were previously attested from being appended to the table again, thus creating duplicates? I know normally the user will be processing a week's worth of data based on date range but say they attest this week's data and then do it again for some reason. How can I prevent the append from running if the records are already on the "real" table? My Temp table is a duplicate design of the "real" table. It's just used to process records imported that will be reviewed and then once attested, moved to the "real" table.
Here's the SQL view of my query qryAppendAttestedRecsFromTEMP:
INSERT INTO tblClaimsWorkingTable ( ClaimNumber, AuthNumber, DateOfServiceFrom, DateOfServiceTo, ProviderCIMID, PayeeName, FEIN_SSN, DateReceived, DateProcessed, MemberID, YouthName, DxCode, DxCode2, DxCode3, DxCode4, ServiceCode, LineNumber, UnitsRequested, AmountBilled, ClaimStatus, DenialReasonComments, ProcessedBy, ClaimsFundYear, ClaimsFundYear2, ClaimsFundType, AttestationCheck, AttestationName, AttestationDate )
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
WHERE (((tblClaimsWorkingTableTEMP.AttestationCheck)=True));
I tried using criteria for most of the fields based on the value of the field on the "real" table but then when it executed is displayed the information as prompts.
Thank you for your assistance and patience with me.
Ann Marie