473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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?
Nov 10 '14 #1

✓ answered by Seth Schrock

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

5 1019
Seth Schrock
2,965 Expert 2GB
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.
Nov 10 '14 #2
@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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command20_Click()
  2. On Error GoTo Command20_Click_Err
  3.  
  4.     DoCmd.OutputTo acOutputReport, "rprtAuditResult", "PDFFormat(*.pdf)", "", False, "", , acExportQualityPrint
  5.  
  6. Command20_Click_Exit:
  7.     Exit Sub
  8.  
  9. Command20_Click_Err:
  10.     MsgBox Error$
  11.     Resume Command20_Click_Exit
  12.  
  13. 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:

Expand|Select|Wrap|Line Numbers
  1. sSQL = "UPDATE tblQAAuditrecords2014 SET AuditReportSent = "True" WHERE 'I am stuck at this point!
Nov 12 '14 #3
Seth Schrock
2,965 Expert 2GB
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.
Nov 12 '14 #4
@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.

Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM tblQAAuditRecords2014
  3. 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))
  4. ORDER BY tblQAAuditRecords2014.Assessor;
  5.  
Nov 19 '14 #5
Seth Schrock
2,965 Expert 2GB
Exactly what you have in line 3 of the above query.
Nov 19 '14 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
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...
0
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...
2
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...
7
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...
1
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? ...
5
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...
0
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;...
1
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...
1
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
0
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...
1
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)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.