How to make a Report with multiple invoices (1 Viewer)

BJF

Registered User.
Local time
Today, 13:15
Joined
Feb 19, 2010
Messages
133
Hi,

I'm attempting to make a report with multiple invoices in the body of the report and could use some guidance please!

So far, what I have is a form that has a multiselection listbox. First, the listbox can be filtered to a specific set of customers invoices.
After the user filters the listbox to a customer, they can select(highlight) whichever invoices they want to print.
Then they click a print button and it opens a report that looks like a form for printing or emailing.

I want this report to have a header, a subform with the invoices selected listed in rows, and a footer.
What I have right now though is: (and understandably as this is the current design) a separate report for each record selected in the listbox.

I think I am on the right track with creating a subform to display the listbox records along with a header and footer but I'm not sure how to alter the code to do this.
I'm not great at vba but I found the code below online and got it working for separate records.

Could someone please point me in the right direction?
Here is the code on the button that opens the report for the selected listbox items as of now:

Private Sub Command21_Click()

Dim var As Variant
Dim strReturn As String
For Each var In Me.List14.ItemsSelected
strReturn = strReturn & Me.List14.ItemData(var) & ","
Next
If Len(strReturn) > 0 Then strReturn = "So In (" & Left(strReturn, Len(strReturn) - 1) & ")"


''****
'fieldName is the field you want to filter

'then you pass the strReturn as Where condition of your Report.

DoCmd.OpenReport "rptMultipleInvoices", acViewPreview, , strReturn

End Sub

Thanks,
Brian
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:15
Joined
Oct 29, 2018
Messages
21,358
Hi Brian. I don't see anything in your code that would produce separate invoice reports. You're only using the OpenReport method once in it. You might consider posting a sample db with test data to help understand the problem.
 

BJF

Registered User.
Local time
Today, 13:15
Joined
Feb 19, 2010
Messages
133
Hi DBguy,
It just clicked in my mind, I'm wrong to be calling this a report, its a form only I did this as a report, my mistake.
What I need is to have a form that has a report or subform listing of multiple row invoices in the center of the form with a head and footer for printing.

Can I use the same code or is that code only for reporting?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:15
Joined
Oct 29, 2018
Messages
21,358
Hi DBguy,
It just clicked in my mind, I'm wrong to be calling this a report, its a form only I did this as a report, my mistake.
What I need is to have a form that has a report or subform listing of multiple row invoices in the center of the form with a head and footer for printing.

Can I use the same code or is that code only for reporting?
Hmm, you lost me a little bit. The code you posted is definitely for a report, but you should be able to modify it for use in a form. However, you said you want to print the form, which is not the usual approach. You can certainly have a form display multiple invoices, but for "printing" them, I would still recommend using a report.
 

Ranman256

Well-known member
Local time
Today, 13:15
Joined
Apr 9, 2015
Messages
4,339
the form (fMyForm) has a listbox (lstClients) of all clients
the code will cycle thru a listbox , get next name, , creating the pdf file

the query (in the report) uses the listbox to pull only that person selected in the list.
i.e.: select * from table where [ClientID]=forms!fMyForm!lstClients

Code:
sub btnGo_click()
Dim i As Integer
dim vLoc
vDir = "c:\temp\"
For i = 0 To lstClients.ListCount - 1
vLoc= lstClients.ItemData(i) 'get next item in list data
   lstClients =vLoc       'set listbox to that item
   vFilename = "rInvoice_" & vLoc & ".pdf"
   vFile = vDir & vFileName
'create invoice data file
docmd.OutputTo acOutputReport ,"rInvoice",acFormatPDF,vFile
'or
   docmd.OutputTo acOutputQuery ,"qsMyQuery",acFormatPDF,vFile

'email invoice directly
   DoCmd.SendObject acSendReport, "rInvoice", acFormatPDF, "name@aol.com", , , "Subject", "message"
Next
end sub
 

Ranman256

Well-known member
Local time
Today, 13:15
Joined
Apr 9, 2015
Messages
4,339
the email name would also be in the listbox, in another column:

vSentTo = lstClients.column(1)
 

BJF

Registered User.
Local time
Today, 13:15
Joined
Feb 19, 2010
Messages
133
Hi DBguy,

I just read what you said and this is where i get lost, because i've had no formal training in Access, but find my way though it pretty unconventionally sometimes.
the form (fMyForm) has a listbox (lstClients) of all clients
the code will cycle thru a listbox , get next name, , creating the pdf file

the query uses the listbox to pull only that person selected in the list.
i.e.: select * from table where [ClientID]=forms!fMyForm!lstClients

Code:
sub btnGo_click()
Dim i As Integer
dim vLoc
vDir = "c:\temp\"
For i = 0 To lstClients.ListCount - 1
vLoc= lstClients.ItemData(i) 'get next item in list data
   lstClients =vLoc       'set listbox to the state
   vFilename = "rInvoice_" & vLoc & ".pdf"
   vFile = vDir & vFileName
'create invoice data file
docmd.OutputTo acOutputReport ,"rInvoice",acFormatPDF,vFile
'or
   docmd.OutputTo acOutputQuery ,"qsMyQuery",acFormatPDF,vFile

'email invoice directly
   DoCmd.SendObject acSendReport, "rInvoice", acFormatPDF, "name@aol.com", , , "Subject", "message"
Next
end sub
Thank you, I will give this a try if i can figure out where to start!
 

BJF

Registered User.
Local time
Today, 13:15
Joined
Feb 19, 2010
Messages
133
Is it possible for me to use the code I originally used and embed a report in datasheet type view into a form? Sorry if this is confusing.
 

Ranman256

Well-known member
Local time
Today, 13:15
Joined
Apr 9, 2015
Messages
4,339
1. make a form
2. make a listbox on the form
3. make a query to put in the listbox to show the clients info: clientID, clientEmail
4. make the invoice report
5. make the query in the report to read the item selected in the list as criteria.
select * from table where [ClientID]=forms!fMyForm!lstClient

6. put a button to create the invoice
7. put the code given behind the button to do it.
 
  • Like
Reactions: BJF

BJF

Registered User.
Local time
Today, 13:15
Joined
Feb 19, 2010
Messages
133
In other words, right now my report design more or less looks like a 8.5x11 form!
what i need to do is keep my report format, but put a subreport on the report to list what i selected from my listbox.
Can you help me change the code to open the report and then assign the listbox choices to the subreport instead of the report?
 

BJF

Registered User.
Local time
Today, 13:15
Joined
Feb 19, 2010
Messages
133
1. make a form
2. make a listbox on the form
3. make a query to put in the listbox to show the clients info: clientID, clientEmail
4. make the invoice report
5. make the query in the report to read the item selected in the list as criteria.
select * from table where [ClientID]=forms!fMyForm!lstClient

6. put a button to create the invoice
7. put the code given behind the button to do it.
thankyou will give this a try
 

BJF

Registered User.
Local time
Today, 13:15
Joined
Feb 19, 2010
Messages
133
I'm not clear on step 7 - the rest is done already
What code should be on the button?
 

BJF

Registered User.
Local time
Today, 13:15
Joined
Feb 19, 2010
Messages
133
Thank you both, i will keep working with this until i get it right
 

BJF

Registered User.
Local time
Today, 13:15
Joined
Feb 19, 2010
Messages
133
1. make a form
2. make a listbox on the form
3. make a query to put in the listbox to show the clients info: clientID, clientEmail
4. make the invoice report
5. make the query in the report to read the item selected in the list as criteria.
select * from table where [ClientID]=forms!fMyForm!lstClient

6. put a button to create the invoice
7. put the code given behind the button to do it.
Thankyou for helping me, i am getting it together now by using the select query, takes me a minute to process sometimes but its coming along thanks again
 

Users who are viewing this thread

Top Bottom