Invoice

[Saxman]

I have created a query relating to customer details and order forms. I would like
to produce an invoice from a report based on the query data. However, when created
the report lists every customer and all orders.

How can I produce an invoice relating to just one variable, namley 'First Order'
from the query form? To paraphrase, I need an invoice with customer details and a
list of products relating to just one order number.

TIA





--

 

[Ken Snell \(MVP\)]

Add a criterion to the query that limits the records to a specific order
number. You can do this by having the query ask you for the desired order
number, or (more preferably) use a form that lets the user decide which
order number to use and then the query can read the value from that form.

Tell us more about your setup and how you want to run the query and then we
can provide specific suggestions.

 

[Saxman]

Add a criterion to the query that limits the records to a specific order number.
You can do this by having the query ask you for the desired order number, or (more
preferably) use a form that lets the user decide which order number to use and
then the query can read the value from that form.

Tell us more about your setup and how you want to run the query and then we can
provide specific suggestions.

I have created a select query which are contained on a form.

Variables are:-
Name
Contact Title
Address
City
County
Postcode
Product
Unit Price
Quantity
Order Date
Invoice Date
Order Number

From the above I intend to create a report (?) which can be used as an invoice.

Is it best to do it with a wizard or in design mode? I'm not sure how I can be
selective with just one order number.
--

 

[Ken Snell \(MVP\)]

Base your report on that query.

Put a command button on the form to run the report based on the order number
that is showing on the form. This code will then be used in the button's
Click event procedure to print the report for that specific order number:

Private Sub ButtonName_Click()
DoCmd.OpenReport "NameOfTheReport", , , "[Order Number]=" & _
Me.[Order Number].Value
End Sub


The above assumes that the Order Number field in the table is numeric data
type (not text), that you have a control named "Order Number" on the form
and that the "Order Number" control holds the value of the Order Number
field, and that you have saved the data for that Order Number to the table
(via the query) underlying the form before you click the command button.

The code takes advantage of using the fourth argument of the
DoCmd.OpenReport action to pass a filter string to the report.

 

[Saxman]

Base your report on that query.

Put a command button on the form to run the report based on the order number that
is showing on the form. This code will then be used in the button's Click event
procedure to print the report for that specific order number:

Private Sub ButtonName_Click()
DoCmd.OpenReport "NameOfTheReport", , , "[Order Number]=" & _
Me.[Order Number].Value
End Sub

Thanks for that. I will give it a whirl on Monday. I'll let you know.

 

[Saxman]

Base your report on that query.

I'm getting a bit bogged down with information overflow.

I have created select query based on tables 'customer' and 'orders' as stated
before, which has all the information required.

Do I need to create another table or report based on the above select query?

I also have another table 'orders' with order input details. Do I need to put the
command button on this form or elsewhere?

Sorry to be a pain, but books don't really teach this.

 

[Ken Snell \(MVP\)]

You will need a query that will provide the appropriate data for your
report. Whether you can use an existing one or need to create a new one
depends upon your needs -- I don't know what your queries are right now, nor
what you want to show on the report.

However, you will want your form to be designed so that it knows what the
order number is to be used for printing the report for that order number.

You should be able to put the button on the form that you're using to create
the order; however, the one caveat is that you need to be sure that your
form has saved its data to the table before you try to print the report,
else there'll be no data in the tables yet for the report to "find" and
display.

 

[Saxman]

You will need a query that will provide the appropriate data for your report.
Whether you can use an existing one or need to create a new one depends upon your
needs -- I don't know what your queries are right now, nor what you want to show
on the report.

However, you will want your form to be designed so that it knows what the order
number is to be used for printing the report for that order number.

You should be able to put the button on the form that you're using to create the
order; however, the one caveat is that you need to be sure that your form has
saved its data to the table before you try to print the report, else there'll be
no data in the tables yet for the report to "find" and display.

Thanks for the valuable feedback. Basically, somebody asked me to do a small
database and after I completed it and requested the possibility of printing an
invoice.

As stated before, I have created a select query from two forms (Orders and
Customers)with all the data required for an invoice, but I only need details related
to one order as stated beforehand. You have provided me with the code for this,
which should be used in conjunction with a control button.

Should I then produce a table from the select query with a command button (with
code) on it and then to produce a report from that table?

How will the contol button know which order number to seek?

What exactly will the code do when run? Ask for an order number?

If I can't get this to work, then they will have to print their own invoices!

Thanks for your patience once again.



--

 

[Ken Snell \(MVP\)]

Tell me more about the design of the form that will have the button on it
for running this report. What data are being displayed on the form? Will it
be a form just for selecting an order number and then printing it? Or is it
a form that is used to create the order?

With this information, we can assist you with the answers to your questions.

 

[Saxman]

Tell me more about the design of the form that will have the button on it for
running this report. What data are being displayed on the form? Will it be a form
just for selecting an order number and then printing it? Or is it a form that is
used to create the order?

With this information, we can assist you with the answers to your questions.

Thanks for your help so far Ken.

I have forms 'Customers' and 'Orders'.

I also have forms 'Customers' and 'Orders'. From those I have created a select
query with all the fields that I require for an invoice as mentioned before.

Name
Contact Title
Address
City
County
Postcode
Product
Unit Price
Quantity
Order Date
Invoice Date
Order Number

I have created a report from the above, but quite naturally it presents all customer
orders rather than being specific to a unique order.

What's the best solution? Create a table from the select query and then a report
(invoice) from that?

I'm a bit confused when you mention putting a control button with code on a table.
Which table?

I do have the book 'Microsoft Office Access 2003' which contains a database with a
report invoice. It basically reproduces an invoice for every order.

Northwind, the custom database has something similar, i.e. it creates an invoice
with every order via a report.
--

 

[Ken Snell \(MVP\)]

I have the Microsoft Office Access 2003 InsideOut book -- on which page does
it discuss the invoice report? I can look at the book and likely tell you
how to modify the sample database's form.

In the meantime, I am going to assume that you want to open a form that will
let you print the invoice for a specific customer order number.

Create a form; let's call it frmPrintInvoice.

On that form, in the Detail section, put a command button and a combo box.
Name the button cmdPrint, and the combo box cboInvNumber.

Use this SQL statement as the RowSource property of cboInvNumber (if my
field and/or table names are wrong, change them to the correct names):
SELECT [Order Number]
FROM Orders
ORDER BY [Order Number] DESC;

We'll use this VBA programming for the Click event of the cmdPrint button (I
am assuming that the field [Order Number] is a numeric field in this code;
if it's a text field, a slight modification will be needed, and I've put
that modification as a separate step in the code, but commented out):

' Start of code
Private Sub cmdPrint_Click()
If Len(Me.cboInvNumber.Value & "") > 0 Then
DoCmd.OpenReport "NameOfYourInvoiceReportGoesHere", _
, , "[Order Number]=" & Me.cboInvNumber.Value
' if [Order Number] is a text field, use the next "two" code steps in
' place of the "two" steps above
' DoCmd.OpenReport "NameOfYourInvoiceReportGoesHere", _
' , , "[Order Number]='" & Me.cboInvNumber.Value & "'"
Else
MsgBox "Select an order number.", vbInformation, _
"Select Order Number"
End If
End Sub
' End of code


The above code will need to be assigned to the button's click event. Easiest
way to do that is click on the command button, open Properties window while
the form is open in design view, click on the Event tab, click in the box
next to On Click, click on "three-dot" button at far right of box, select
"event procedure" from the popup window (if it shows), and then paste the
code into the VB Editor window when it appears (note that the VBE will have
the "Private Sub cmdPrint_Click()" line, a blank line, and the "End Sub"
line showing. Paste all the code steps from above (without the Private Sub
and End Sub steps) into the blank line.

 

[Saxman]

The above code will need to be assigned to the button's click event. Easiest way
to do that is click on the command button, open Properties window while the form
is open in design view, click on the Event tab, click in the box next to On Click,
click on "three-dot" button at far right of box, select "event procedure" from the
popup window (if it shows), and then paste the code into the VB Editor window when
it appears (note that the VBE will have the "Private Sub cmdPrint_Click()" line, a
blank line, and the "End Sub" line showing. Paste all the code steps from above
(without the Private Sub and End Sub steps) into the blank line.

I'll try that, thanks. The Northwind sample database has an invoice feature which
works well, but once it is whittled down, one starts to lose relationships.

It's a pity there wasn't an English (European) sample of the database.

--

 

[Saxman]

The above code will need to be assigned to the button's click event. Easiest way
to do that is click on the command button, open Properties window while the form
is open in design view, click on the Event tab, click in the box next to On Click,
click on "three-dot" button at far right of box, select "event procedure" from the
popup window (if it shows), and then paste the code into the VB Editor window when
it appears (note that the VBE will have the "Private Sub cmdPrint_Click()" line, a
blank line, and the "End Sub" line showing. Paste all the code steps from above
(without the Private Sub and End Sub steps) into the blank line.

I managed to get this to work Ken. I'll fill you in on the details tomorrow. I am
very grateful.

--

 

[Saxman]

I managed to get this to work Ken. I'll fill you in on the details tomorrow. I am
very grateful.

I managed create a form 'FormPrintInoice' from my 'Invoice' query (from Customers
and Orders).

I got all the information onto the 'Invoice' report that I required. However, if a
customer orders several items on one order (in my case, probably no more than 3),
then only one item appears on the report, because there is only one field for it.

The FormPrintInoice in design view, quite naturally only shows one column 'Orders'
and a line for every different item ordered on one order.

Would it be possible to add additional Combo Boxes on the report to include other
items?
--

 

[Ken Snell \(MVP\)]

Sounds like a limitation of the query, not the form or report. Post the SQL
statement of the "Invoice" report's RecordSource query and let's see what
records it's returning.

 

[Saxman]

Sounds like a limitation of the query, not the form or report. Post the SQL
statement of the "Invoice" report's RecordSource query and let's see what records
it's returning.

The select query 'Invoice' table contains all orders including orders with several
items.

The report 'Invoice' contains those columns as well.

The form 'FrmPrintInvoice' contains these columns in addition to a combo box to the
select any order, in addition to a 'Print Invoice' control button.

I'm not sure about sourcing the SQL statement of the RecordSource query? Does it
need to come from the report? If so, from what property?

I need some guidance with this.

Thanks again.

--

 

[Saxman]

Sounds like a limitation of the query, not the form or report. Post the SQL
statement of the "Invoice" report's RecordSource query and let's see what records
it's returning.

I have solved this Ken. My fault entirely. During the formatting of the 'Invoice'
report, I moved the corresponding fields elsewhere on the report which caused the
data loss.

The invoice now displays all items on the invoice.

I have learnt a lot, thanks to your generous help. That's the best way to learn, by
getting involved. I am more familiar with the likes of Excel. Access I use when
somebody asks me a favour.

Office 2007 looks VG.

Thanks again.

--

 

[Ken Snell \(MVP\)]

Look at the report in Design view. Open Properties window and click on Data
tab. What is in the Record Source property box? If it's a statement
beginning with "SELECT " then copy the entire string and paste it into your
reply. If it's the name of a query, then go to that query, open it in Design
view, click on top left icon (Query View) to select SQL View, copy entire
text you see there, and paste into your reply.

 

[Ken Snell \(MVP\)]

Glad you made it work! Goodl luck.