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?
Exactly what you have in line 3 of the above query.
5 1019
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.Execute command.
@Seth Schrock
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!
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.
@Seth Schrock
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;
-
Exactly what you have in line 3 of the above query.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sigurd Urdahl |
last post by:
I need to do CHECK TABLE on a lot of tables (actually on all tables in
several databases), and hoped to do something like
mysql> CHECK TABLE reports.*
which ddn't work. The only thing that...
|
by: Archana |
last post by:
I'm using .net crystal reports export the reports to the
html format. When in debug mode and viewing the html file
through my WEb Application the following exception is
thrown when I try to using...
|
by: valerio |
last post by:
Hi all,
I've some problem to import data to DB2 using the db2move and db2look
tools.
Follow the problem :
I have exported data from db2 v. 7 database on windows server, using
the db2look and...
|
by: Susan Bricker |
last post by:
I would like to generate a report (I have the report working already)
using MS/ACCESS 2000 and then have the ability to send the report as an
email attachment to my colleagues. I have looked...
|
by: kylei |
last post by:
I need to create a report for a monthly sales table that is sent to our
company. Is there a way to create a report that will automatically sum
totals from a file that is sent to us every month? ...
|
by: teddysnips |
last post by:
I have been asked to look at an Access application. It generates a
report successfully, so that you can see it on the screen with no
errors.
However, when you try to print it, the prompt dialog...
|
by: selimzairi |
last post by:
hi to all
would any body help please on this cross table report problem
yrs header -for years grouping
mnt header -for monthes grouping
state_id -for states
columns 1->30 is for branches id;...
|
by: imranabdulaziz |
last post by:
Dear All,
I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2005 as a back End
I now want to generate report using through sql server reporting services.
Please...
|
by: amitsukte |
last post by:
Hello Everyone I m Creating one procedure in that i want to check for existence of table i want to something like this..
if(table A Exists) then
some insert operation on a;
some update...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |