Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Acccess - Update Field in Table when report is printed

Posted on 2016-09-29
6
Medium Priority
?
62 Views
Last Modified: 2016-09-30
Hi,

Seems very simple but I can't seem to make it work properly.
I have a macro created for me which exports reports as pdf files. WHich works great.

However I'd like field ("Printed") in my "Contracts" table to be updated with "Yes" when the report is exported.

Here's the existing code:

Private Sub Command31_Click()
DoCmd.RunCommand acCmdSaveRecord
Forms!Form2.SetFocus
Dim oApp As Object
Dim oEmail As Outlook.MailItem
Dim fileName As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Account], [InvoiceNum], [EmailAddress], [Company] FROM [ContactTotalsNoEmail] WHERE (((Contracts.SelectedPrint)=True)) ORDER BY [Account];", dbOpenSnapshot)

Do While Not rst.EOF
    strRptFilter = "[InvoiceNum] = " & Chr(34) & rst![InvoiceNum] & Chr(34)
    fileName = "C:\Scripts" & "\" & rst![Account] & " - " & rst![InvoiceNum] & ".pdf"
    DoCmd.OutputTo acOutputReport, "InvTotalNoEmail", acFormatPDF, fileName
    DoEvents
     rst.MoveNext
   Loop
rst.Close
Set rst = Nothing
Forms!Form2.Requery
End Sub

Open in new window


I couldn't fingure it with rst, so tried to create rst2 but it would not update the correct records.
rst2.Edit
rst2!Print = Yes
rst2.Update
rst2.MoveNext

Open in new window


Can anyone assist?

Many thanks.
0
Comment
Question by:Jack Marley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:PatHartman
ID: 41821928
Private Sub Command31_Click()
DoCmd.RunCommand acCmdSaveRecord
Forms!Form2.SetFocus
Dim oApp As Object
Dim oEmail As Outlook.MailItem
Dim fileName As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Account], [InvoiceNum], [EmailAddress], [Company] FROM [ContactTotalsNoEmail] WHERE (((Contracts.SelectedPrint)=True)) ORDER BY [Account];", dbOpenSnapshot)

Do While Not rst.EOF
    strRptFilter = "[InvoiceNum] = " & Chr(34) & rst![InvoiceNum] & Chr(34)
    fileName = "C:\Scripts" & "\" & rst![Account] & " - " & rst![InvoiceNum] & ".pdf"
    DoCmd.OutputTo acOutputReport, "InvTotalNoEmail", acFormatPDF, fileName
    rst.Edit
    rst!Print = True
    rst.Update
    DoEvents
     rst.MoveNext
   Loop
rst.Close
Set rst = Nothing
Forms!Form2.Requery
End Sub

Open in new window

I changed the Print set statement to use True rather than Yes.  If the Print field is actually text, then it should be  -- rst!Print = "Yes"
0
 

Author Comment

by:Jack Marley
ID: 41821944
Thanks PatHartman,

However that gives me a "Operation is not supported for this type of Object".

Do you know what might cause this?
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41821952
The query is not updateable.  Remove the DISTINCT predicate.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:Jack Marley
ID: 41822009
Still errors I'm afraid, on line
    rst.Edit

Open in new window

0
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 41822204
You have it opened as Snapshot.  Shapshot is read only.  Use Dynaset.
1
 

Author Comment

by:Jack Marley
ID: 41823181
Thanks Phil!

I also noticed I hadn't even specified the 'Print' field in the SELECT! Then I put the field in square brackets to prevent any disambiguation.

rst![Printed] = "Yes"

Open in new window


Thanks again!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question