Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

MS Acccess - Update Field in Table when report is printed

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
Jack Marley
Asked:
Jack Marley
  • 3
  • 3
1 Solution
 
PatHartmanCommented:
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
 
Jack MarleyAuthor Commented:
Thanks PatHartman,

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

Do you know what might cause this?
0
 
PatHartmanCommented:
The query is not updateable.  Remove the DISTINCT predicate.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jack MarleyAuthor Commented:
Still errors I'm afraid, on line
    rst.Edit

Open in new window

0
 
PatHartmanCommented:
You have it opened as Snapshot.  Shapshot is read only.  Use Dynaset.
1
 
Jack MarleyAuthor Commented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now