hgj1357
asked on
MS Access acoutput reports based on table data
I have table of invoice numbers like this:
BillingInvNum
19-563-00-3
19-570-00-2
19-575-00-5
20-511-00-1
...
I have a report that can filter on any of these invoice numbers and print out said invoice. I'd like to be able to Output to an RTF file for each invoice number. ie.19-563-00-3.rtf; 19-570-00-2.rtf etc.
So essentially I need to cycle through the entire (approx 56) invoice numbers and for each one use
DoCmd.OutputTo acOutputReport, "Invoice Complete duplicates", acFormatRTF, "XXX.rtf"
where XXX is the relevant invoice number from the table.
Thanks
BillingInvNum
19-563-00-3
19-570-00-2
19-575-00-5
20-511-00-1
...
I have a report that can filter on any of these invoice numbers and print out said invoice. I'd like to be able to Output to an RTF file for each invoice number. ie.19-563-00-3.rtf; 19-570-00-2.rtf etc.
So essentially I need to cycle through the entire (approx 56) invoice numbers and for each one use
DoCmd.OutputTo acOutputReport, "Invoice Complete duplicates", acFormatRTF, "XXX.rtf"
where XXX is the relevant invoice number from the table.
Thanks
ASKER
ok. So in your example is qry_rptName the record source for reportname?
yes
in your case,since your InvoiceNumbers are not numeric, this:
rpt.filter = "InvoiceNum = " & rs!InvoiceNum
might need to be changed to:rpt.filter = "InvoiceNum = " & chr$(34) & rs!InvoiceNum & chr$(34)
ASKER
what does that do: chr$(34)
ASKER
oh. it's that "
got it.
got it.
I have also seen this done by opening the recordset first, then looping through the recordset and opening the report with a WHERE argument for each invoice, followed by the Output method, but I found this slower than opening the report once with all of the invoices and then filtering for each invoice.
HTH
Dale
HTH
Dale
ASKER
OK, partial success. My trial data set has 23 records, and this produces 23 RTFs in the correct folder, with correct name. However, all 23 RTFs have all 23 records. It is not filtering on INVNUM. Does the report require INVNUM field to be in a particular place?
Private Sub Command0_Click()
DoCmd.OpenReport "Report1", acViewPreview
Dim rpt As Report
Set rpt = Reports("Report1")
Dim rs As DAO.Recordset
Dim strfilename As String
Set rs = CurrentDb.OpenRecordset("S ELECT distinct BillingName, INVNUM, Customer from rpt_q1")
While Not rs.EOF
rpt.Filter = "INVNUM = " & rs!INVNUM
rpt.FilterOn = True
rpt.Caption = rs!Customer & "-" & rs!BillingName & "-" & rs!INVNUM
strfilename = "C:\LocalDB\Rpt\" & rpt.Caption & ".rtf"
DoCmd.OutputTo acOutputReport, rpt.Name, acFormatRTF, strfilename
rs.MoveNext
Wend
End Sub
Private Sub Command0_Click()
DoCmd.OpenReport "Report1", acViewPreview
Dim rpt As Report
Set rpt = Reports("Report1")
Dim rs As DAO.Recordset
Dim strfilename As String
Set rs = CurrentDb.OpenRecordset("S
While Not rs.EOF
rpt.Filter = "INVNUM = " & rs!INVNUM
rpt.FilterOn = True
rpt.Caption = rs!Customer & "-" & rs!BillingName & "-" & rs!INVNUM
strfilename = "C:\LocalDB\Rpt\" & rpt.Caption & ".rtf"
DoCmd.OutputTo acOutputReport, rpt.Name, acFormatRTF, strfilename
rs.MoveNext
Wend
End Sub
ASKER
tried this too
rpt.Filter = "INVNUM=" & Chr$(34) & rs!INVNUM & Chr$(34)
rpt.Filter = "INVNUM=" & Chr$(34) & rs!INVNUM & Chr$(34)
ASKER
OK. It works if I put an interrupt between turning the filter on and outputting the report:
Private Sub Command0_Click()
DoCmd.OpenReport "Report1", acViewPreview
Dim rpt As Report
Set rpt = Reports("Report1")
Dim rs As DAO.Recordset
Dim strfilename As String
Set rs = CurrentDb.OpenRecordset("S ELECT distinct INVNUM, BillingName, Customer from rpt_q1")
While Not rs.EOF
rpt.Filter = "INVNUM=" & Chr$(34) & rs!INVNUM & Chr$(34)
rpt.FilterOn = True
MsgBox ("Create Invoice: " & rs!INVNUM)
rpt.Caption = rs!Customer & "-" & rs!BillingName & "-" & rs!INVNUM
strfilename = "C:\LocalDB\Rpt\" & rpt.Caption & ".rtf"
DoCmd.OutputTo acOutputReport, rpt.Name, acFormatRTF, strfilename
rs.MoveNext
Wend
End Sub
Private Sub Command0_Click()
DoCmd.OpenReport "Report1", acViewPreview
Dim rpt As Report
Set rpt = Reports("Report1")
Dim rs As DAO.Recordset
Dim strfilename As String
Set rs = CurrentDb.OpenRecordset("S
While Not rs.EOF
rpt.Filter = "INVNUM=" & Chr$(34) & rs!INVNUM & Chr$(34)
rpt.FilterOn = True
MsgBox ("Create Invoice: " & rs!INVNUM)
rpt.Caption = rs!Customer & "-" & rs!BillingName & "-" & rs!INVNUM
strfilename = "C:\LocalDB\Rpt\" & rpt.Caption & ".rtf"
DoCmd.OutputTo acOutputReport, rpt.Name, acFormatRTF, strfilename
rs.MoveNext
Wend
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If it works with the message box, you might just be able to insert a pause of a second or two instead of the msgbox.
Hello HG1357,
You might find this code useful.
The code spins through data that contains order numbers and invoice numbers. The order numbers points to invoice numbers. The invoice report is triggered but remains hidden and outputs to a PDF file. I do not want a printed copy. After each invoice is outputted a routine is called which emails a recipient (email address in my input file). The PDF is sent as an attachment.
Of course this same concept can be used to email any kind of output file.
Dave
sub SendInvoices
Set db = CurrentDb
rem dataset having the list of order numbers to print
Set Myquery = db.QueryDefs("qryorders)
Set SV = Myquery.OpenRecordset(DB_O PEN_DYNASE T)
Do
If SV.EOF Then
Exit Do
End If
rem order number used to find invoice number
w1$ = "OrderNumber=" & "'" & SV!OrderNumber & "'"
rem invoice print
DoCmd.OpenReport "rptOrderEntryMaster_Invoi ce", acViewPreview, , w1$, acHidden
rem output invoice to a file - using PDF
DoCmd.OutputTo acOutputReport, "rptorderentrymaster_Invoi ce", acFormatPDF, "C:\output_folder\" & & LTrim$(RTrim$(SV!InvoiceNu mber)) & ".PDF"
DoCmd.Close acReport, "rptorderentrymaster_Invoi ce"
rem call routine to send file as attachment
Call Send_CDO_Mail_Invoice_By_O utlook(Ema il$, SV!PurchaseOrderNumber, SV!InvoiceNumber)
MoveNext:
rem delete output file
Kill "C:\output_folder\" & & LTrim$(RTrim$(SV!InvoiceNu mber)) & ".PDF"
Bypass:
SV.MoveNext
Loop
SV.Close
Set SV = Nothing
Sending.Visible = False
MsgBox ("Number of Invoices Emailed: " & EmailCount)
End Sub
Sub Send_CDO_Mail_Invoice_By_O utlook(Ema il$, PurchaseOrderNumber$, Invoice$)
Dim olkApp As Outlook.Application, olkNS As Outlook.NameSpace, olkFolder As Outlook.MAPIFolder, olkMessage As Outlook.MailItem, strSubject As String, strBody As String
On Error GoTo BadEmail
Attachment1 = "C:\access2002.db\" & "METROV" & LTrim$(RTrim$(Invoice$)) & ".PDF"
ErrorSwitch = 0
Set objApp = CreateObject("Outlook.Appl ication")
Dim outobj, mailobj
TryAgain:
Set outobj = CreateObject("Outlook.Appl ication")
Set mailobj = outobj.CreateItem(0)
With mailobj
.Sender = "invoicing@metrovac.com"
.To = Email$
.Subject = "Metropolitan Vacuum Cleaner Company - Invoice " & Invoice$
GoSub Get_Text
.Body = message$
.Attachments.Add Attachment1
.Send
End With
Set outobj = Nothing
Set mailobj = Nothing
Exit Sub
BadEmail:
Select Case ErrorSwitch
Case 0
ErrorSwitch = 1
Email$ = InputBox("Fix Email Address: " & " For Invoice " & Invoice$ & " ", , Email$)
GoTo TryAgain
Case Else
MsgBox (" Can't Transmit Hold For Manual Processing:" & Invoice$)
End Select
Exit Sub
End Sub
You might find this code useful.
The code spins through data that contains order numbers and invoice numbers. The order numbers points to invoice numbers. The invoice report is triggered but remains hidden and outputs to a PDF file. I do not want a printed copy. After each invoice is outputted a routine is called which emails a recipient (email address in my input file). The PDF is sent as an attachment.
Of course this same concept can be used to email any kind of output file.
Dave
sub SendInvoices
Set db = CurrentDb
rem dataset having the list of order numbers to print
Set Myquery = db.QueryDefs("qryorders)
Set SV = Myquery.OpenRecordset(DB_O
Do
If SV.EOF Then
Exit Do
End If
rem order number used to find invoice number
w1$ = "OrderNumber=" & "'" & SV!OrderNumber & "'"
rem invoice print
DoCmd.OpenReport "rptOrderEntryMaster_Invoi
rem output invoice to a file - using PDF
DoCmd.OutputTo acOutputReport, "rptorderentrymaster_Invoi
DoCmd.Close acReport, "rptorderentrymaster_Invoi
rem call routine to send file as attachment
Call Send_CDO_Mail_Invoice_By_O
MoveNext:
rem delete output file
Kill "C:\output_folder\" & & LTrim$(RTrim$(SV!InvoiceNu
Bypass:
SV.MoveNext
Loop
SV.Close
Set SV = Nothing
Sending.Visible = False
MsgBox ("Number of Invoices Emailed: " & EmailCount)
End Sub
Sub Send_CDO_Mail_Invoice_By_O
Dim olkApp As Outlook.Application, olkNS As Outlook.NameSpace, olkFolder As Outlook.MAPIFolder, olkMessage As Outlook.MailItem, strSubject As String, strBody As String
On Error GoTo BadEmail
Attachment1 = "C:\access2002.db\" & "METROV" & LTrim$(RTrim$(Invoice$)) & ".PDF"
ErrorSwitch = 0
Set objApp = CreateObject("Outlook.Appl
Dim outobj, mailobj
TryAgain:
Set outobj = CreateObject("Outlook.Appl
Set mailobj = outobj.CreateItem(0)
With mailobj
.Sender = "invoicing@metrovac.com"
.To = Email$
.Subject = "Metropolitan Vacuum Cleaner Company - Invoice " & Invoice$
GoSub Get_Text
.Body = message$
.Attachments.Add Attachment1
.Send
End With
Set outobj = Nothing
Set mailobj = Nothing
Exit Sub
BadEmail:
Select Case ErrorSwitch
Case 0
ErrorSwitch = 1
Email$ = InputBox("Fix Email Address: " & " For Invoice " & Invoice$ & " ", , Email$)
GoTo TryAgain
Case Else
MsgBox (" Can't Transmit Hold For Manual Processing:" & Invoice$)
End Select
Exit Sub
End Sub
ASKER
I think Dale Fye's solution will work, but I have to try a few other things before I can confirm. Looks good for now.
Open in new window