VBA to check a box in table after a report is generated

[benny1983]

Hi All,

I'm searching for an easy way to get this to work.

Basically, I have a database and when I run a nd generate a report on the entries so far, I'm after some VBA to check a box in the table to indicate that a report has already been run on that entry (so I can filter these out on future reports).

My code is fine for generating and emailing the report I just need the code to mark it off in the records table as having been sent.

Any ideas?

[Seth Schrock]

Build an UPDATE query in your VBA code that sets the value of your checkbox field to true based on the criteria that you need. You can then run this code using the CurrentDb.Execu te command.

[benny1983]

Build an UPDATE query in your VBA code that sets the value of your checkbox field to true based on the criteria that you need. You can then run this code using the CurrentDb.Execu te command.

Ok So I understand the concept here but I am stuck on what I put in the WHERE section.

To help, here is the code so far to generate my pdf. Once generated I need to update the checkbox box "ReportSent " in the table but only based on the records generated in the report.

Code to Generate the PDF:

Private Sub Command20_Click()
On Error GoTo Command20_Click_Err

    DoCmd.OutputTo acOutputReport, "rprtAuditResult", "PDFFormat(*.pdf)", "", False, "", , acExportQualityPrint

Command20_Click_Exit:
    Exit Sub

Command20_Click_Err:
    MsgBox Error$
    Resume Command20_Click_Exit

End Sub

I've got this far with the additional code but completely stuck as I only want the records generated in that report to be updated so not sure what to put:

sSQL = "UPDATE tblQAAuditrecords2014 SET AuditReportSent = "True" WHERE 'I am stuck at this point!

[Seth Schrock]

You would put in the same criteria as the report has that you are generating. Also, you need to remove the double quotes around "True". They will mess up your VBA string.

[benny1983]

You would put in the same criteria as the report has that you are generating. Also, you need to remove the double quotes around "True". They will mess up your VBA string.

I'm really stuck on this. Here is my query in SQL used for the report (which I assume I will need given its an SQL part of the code). I just dont know what to enter after WHERE. Ther report itself is based on parameters entered from a dialog box I created so how would I work that into the code.

SELECT tblQAAuditRecords2014.QAQuarter, tblQAAuditRecords2014.Assessor, tblQAAuditRecords2014.QAConsultant, tblQAAuditRecords2014.[ex-gratia payment made], tblQAAuditRecords2014.ClaimNumber, tblQAAuditRecords2014.ClaimType, tblQAAuditRecords2014.IncomingDoc1Ref, tblQAAuditRecords2014.IncomingDoc2, tblQAAuditRecords2014.DateDoc2Received, tblQAAuditRecords2014.DateDoc2LoggedGLSCI, tblQAAuditRecords2014.DateDoc2Assessed, tblQAAuditRecords2014.IncomingDoc2BreachReason, tblQAAuditRecords2014.IncomingDoc2Notes, tblQAAuditRecords2014.IncomingDoc2Ref, tblQAAuditRecords2014.IncomingDoc3, tblQAAuditRecords2014.DateDoc3Received, tblQAAuditRecords2014.DateDoc3LoggedGLSCI, tblQAAuditRecords2014.DateDoc3Assessed, tblQAAuditRecords2014.IncomingDoc3BreachReason, tblQAAuditRecords2014.IncomingDoc3Notes, tblQAAuditRecords2014.IncomingDoc3Ref, tblQAAuditRecords2014.IncomingCorrespondence4, tblQAAuditRecords2014.DateDoc4Received, tblQAAuditRecords2014.DateDoc4LoggedGLSCI, tblQAAuditRecords2014.DateDoc4Assessed, tblQAAuditRecords2014.IncomingDoc4BreachReason, tblQAAuditRecords2014.InvoiceReceivedDate, tblQAAuditRecords2014.InvoicePaidDate, tblQAAuditRecords2014.InvoiceBreachReason, tblQAAuditRecords2014.InvoiceRef, tblQAAuditRecords2014.InvoiceNotes, tblQAAuditRecords2014.IncomingDoc4Notes, tblQAAuditRecords2014.IncomingDoc4Ref, tblQAAuditRecords2014.WrittenCorrespondence1, tblQAAuditRecords2014.WrittenCorrespondence1BreachReason, tblQAAuditRecords2014.WrittenCorrespondence1BreachReason2, tblQAAuditRecords2014.WrittenCorrespondence1BreachReason3, tblQAAuditRecords2014.WrittenCorrespondence1Notes, tblQAAuditRecords2014.WrittenCorrespondence1Ref, tblQAAuditRecords2014.WrittenCorrespondence2, tblQAAuditRecords2014.WrittenCorrespondence2BreachReason, tblQAAuditRecords2014.WrittenCorrespondence2BreachReason2, tblQAAuditRecords2014.WrittenCorrespondence2BreachReason3, tblQAAuditRecords2014.WrittenCorrespondence2Notes, tblQAAuditRecords2014.WrittenCorrespondence2Ref, tblQAAuditRecords2014.WrittenCorrespondence3, tblQAAuditRecords2014.WrittenCorrespondence3BreachReason, tblQAAuditRecords2014.WrittenCorrespondence3BreachReason2, tblQAAuditRecords2014.WrittenCorrespondence3BreachReason3, tblQAAuditRecords2014.WrittenCorrespondence3Notes, tblQAAuditRecords2014.WrittenCorrespondence3Ref, tblQAAuditRecords2014.InitialPhoneCallRecordingID, tblQAAuditRecords2014.InitialPhoneCallDate, tblQAAuditRecords2014.InitialPhoneCallBreachReason, tblQAAuditRecords2014.InitialPhoneCallBreachReason2, tblQAAuditRecords2014.InitialPhoneCallBreachReason3, tblQAAuditRecords2014.InitialPhoneCallNotes, tblQAAuditRecords2014.OngoingPhoneCall1RecordingID, tblQAAuditRecords2014.OngoingPhoneCall1Date, tblQAAuditRecords2014.OngoingPhoneCall1BreachReason, tblQAAuditRecords2014.OngoingPhoneCall1BreachReason2, tblQAAuditRecords2014.OngoingPhoneCall1BreachReason3, tblQAAuditRecords2014.OngoingPhoneCall1Notes, tblQAAuditRecords2014.OngoingPhoneCall2RecordingID, tblQAAuditRecords2014.OngoingPhoneCall2Date, tblQAAuditRecords2014.OngoingPhoneCall2BreachReason, tblQAAuditRecords2014.OngoingPhoneCall2BreachReason2, tblQAAuditRecords2014.OngoingPhoneCall2BreachReason3, tblQAAuditRecords2014.OngoingPhoneCall2Notes, tblQAAuditRecords2014.ComplaintReceived, tblQAAuditRecords2014.ComplaintRespondedTo, tblQAAuditRecords2014.ComplaintProcessFollowed, tblQAAuditRecords2014.ComplaintReceivedDate, tblQAAuditRecords2014.ComplaintAcknowledgedDate, tblQAAuditRecords2014.ComplaintResponseDate, tblQAAuditRecords2014.BenefitPayable, tblQAAuditRecords2014.BenefitPayableBreachReason, tblQAAuditRecords2014.BenefitPayableBreachReason2, tblQAAuditRecords2014.BenefitPayableBreachReason3, tblQAAuditRecords2014.BenefitPayableNotes, tblQAAuditRecords2014.BasicClaimRequirementBreachReason, tblQAAuditRecords2014.BasicClaimRequirementBreachReason2, tblQAAuditRecords2014.BasicClaimRequirementBreachReason3, tblQAAuditRecords2014.BasicClaimRequirementNotes, tblQAAuditRecords2014.EligibilityBreachReason, tblQAAuditRecords2014.EligibilityBreachReason2, tblQAAuditRecords2014.EligibilityBreachReason3, tblQAAuditRecords2014.EligibilityNotes, tblQAAuditRecords2014.InitialAssessmentBreachReason, tblQAAuditRecords2014.InitialAssessmentBreachReason2, tblQAAuditRecords2014.InitialAssessmentBreachReason3, tblQAAuditRecords2014.InitialAssessmentBreachReason4, tblQAAuditRecords2014.InitialAssessmentBreachReason5, tblQAAuditRecords2014.InitialAssessmentBreachReason6, tblQAAuditRecords2014.InitialAssessmentNotes, tblQAAuditRecords2014.OngoingStrategyBreachReason, tblQAAuditRecords2014.OngoingStrategyBreachReason2, tblQAAuditRecords2014.OngoingStrategyBreachReason3, tblQAAuditRecords2014.OngoingStrategyBreachReason4, tblQAAuditRecords2014.OngoingStrategyBreachReason5, tblQAAuditRecords2014.OngoingStrategyBreachReason6, tblQAAuditRecords2014.OngoingStrategyNotes, tblQAAuditRecords2014.DecisionSignOffOutcome, tblQAAuditRecords2014.PaymentAuthoriser, tblQAAuditRecords2014.SupportedRecommendation, tblQAAuditRecords2014.DecisionBreachReason1, tblQAAuditRecords2014.DecisionBreachReason2, tblQAAuditRecords2014.DecisionBreachReasonNotes, tblQAAuditRecords2014.QAdate, tblQAAuditRecords2014.DateDoc1Received, tblQAAuditRecords2014.IncomingDoc1, tblQAAuditRecords2014.DateDoc1LoggedGLSCI, tblQAAuditRecords2014.IncomingDoc1BreachReason, tblQAAuditRecords2014.IncomingDoc1Notes, tblQAAuditRecords2014.ClaimantName, tblQAAuditRecords2014.PolicyNumber, tblQAAuditRecords2014.DateDoc1Received, tblQAAuditRecords2014.DateDoc1Assessed, tblQAAuditRecords2014.[General Comments]
FROM tblQAAuditRecords2014
WHERE (((tblQAAuditRecords2014.QAQuarter) Like [Forms]![frmQuarterlyByAssessor]![cmbQuarter]) AND ((tblQAAuditRecords2014.Assessor)=[Forms]![frmQuarterlyByAssessor]![Assessortext1]) AND ((tblQAAuditRecords2014.QAConsultant) Like [Forms]![frmQuarterlyByAssessor]![cmbQAConsultant]) AND ((tblQAAuditRecords2014.ClaimNumber) Like [Forms]![frmQuarterlyByAssessor]![claimnumbertext1])) OR (((tblQAAuditRecords2014.QAQuarter) Like [Forms]![frmQuarterlyByAssessor]![cmbQuarter]) AND ((tblQAAuditRecords2014.QAConsultant) Like [Forms]![frmQuarterlyByAssessor]![cmbQAConsultant]) AND ((([tblQAAuditRecords2014].[Assessor]) Like [Forms]![frmQuarterlyByAssessor]![Assessortext1]) Is Null))
ORDER BY tblQAAuditRecords2014.Assessor;

[Seth Schrock]

Exactly what you have in line 3 of the above query.