Jack Marley
asked on
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:
I couldn't fingure it with rst, so tried to create rst2 but it would not update the correct records.
Can anyone assist?
Many thanks.
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
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
Can anyone assist?
Many thanks.
ASKER
Thanks PatHartman,
However that gives me a "Operation is not supported for this type of Object".
Do you know what might cause this?
However that gives me a "Operation is not supported for this type of Object".
Do you know what might cause this?
The query is not updateable. Remove the DISTINCT predicate.
ASKER
Still errors I'm afraid, on line
rst.Edit
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks again!
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"
Thanks again!
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"