Combining Selected Reports

[nate]

I have an invoice function on an enrollment database I'm working on. Currently we can open up a formatted invoice for a particular order, and it is all laid out and totalled properly, however we ocassionally need to combine 2 or more invoices together, usually when there are multiple children in the family, but we want to give the parents only one invoice.

What I'd like is for a form to open up, where the user can select a number of different OrderID which would then have those orders detailed on the Invoice report.

Has anyone done anything like this before, and could you possible help me in the right direction?

 

[vbaInet]

This requires some VBA nate. I remember you mentioning you're not good with this. But see if this links helps:

http://baldyweb.com/BuildSQL.htm

 

[nate]

I might be completely wrong here, but if I created a form with 5 combo boxes which the OrderID could be selected (Its usually only 2 or 3 invoices that need to be joined, never more than 5) could I use a SELECT clause with FROM and WHERE to use the results of the combo box to find the correct sub reports..... :/

 

[vbaInet]

You're on the right path but instead of using combo boxes you can use a multiselect listbox so users can select multiple IDs in that control. I will give you the code to get the input.

In any case you would still need to do some vba coding to build that sql string. If you were to use the combo boxes then selecting 2 out of 3 will cause it to fail.

 

[nate]

I suspected that might cause problems, I'll keep looking at it for a little while and see what I can do.

 

[vbaInet]

It's mainly the WHERE part that needs to be built in code. You can combine OR or IN ()

 

[nate]

I'm guessing I'll need something like...

A form that lets the users choose invoices from a list box, then a button that links the choices to the report...

Is something like this on the right path?...

SELECT RptSubInvoice
FROM RptSubInvoice
WHERE "[OrderID Field]=" & Nz([OrderID]

DoCmd.OpenReport "rptCombinedInvoice", acViewPreview,

I know I'm missing a bunch, obviously, but if someone could tell me if I'm getting a little closer.

 

[vbaInet]

You're warming up!

It depends on what kind of control you've chosen to use? Multiselect listbox?

 

[nate]

I solved my dilemma. I created a report which listed all of the orderIDs on it (formatted as I need it) and then used a form with a multiselect listbox to filter which OrderIDs would stay on the form.

Thanks for the help, I was expecting this to be a massive headache, but it turned out to be a pretty simple task.

 

[vbaInet]

Good job nate!

I hope the link was helpful?

Can we see your code? Might help you optimise it (if needs be)

 

[nate]

My form code is;

Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In ListFilter.ItemsSelected
stDocCriteria = stDocCriteria & "[OrderID] = " & ListFilter.Column(0, VarItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function


Button Code;
Private Sub Command2_Click()
DoCmd.OpenReport "rptCustomInvoices", acPreview, , GetCriteria()

End Sub

I found the code on another site, and just corrected it so it works.

 

[vbaInet]

Looks good to me.

 

[Simon_MT]

I would be tempted to create a file that you gather one Order or many Orders. The invoice is a primary document and it is important that these can be reproduced and seen for audit purposes.

Simon

 

[nate]

I would be tempted to create a file that you gather one Order or many Orders. The invoice is a primary document and it is important that these can be reproduced and seen for audit purposes.

Simon

I'm a little confused by what you mean.

 

[Simon_MT]

Whilst an Order is Order an Invoice is a Invoice. If you collect various Orders how do record or reproduce the Invoice. The Invoice information is Revenue and should be retained.

Simon

 

[nate]

Whilst an Order is Order an Invoice is a Invoice. If you collect various Orders how do record or reproduce the Invoice. The Invoice information is Revenue and should be retained.

Simon

The invoice displays the information recorded in the Orders, Products and Customers tables in a way that is readable by customers. I think I understand what you mean, but I'm sure its not quite what I was looking for. But thanks for your help.

 

[Simon_MT]

All I was saying that the Invoice should be able to recreated.

Simon