Help ... Creating an invoice

  • Thread starter Thread starter Brook
  • Start date Start date
B

Brook

Here is what I have and what I am trying to accomplish:

I create orders for clients on a daily basis, but only
invoice the clients once a month. So what I have is an
Orders Table that has all my current orders that have been
placed by my clients.

What I am trying to accomplish to be able to possibly
have a pop-up box that I can choose what client I am
creating an invoice for, then select the orders that I
want to include on that invoice. However, I cannot figure
out how to accomplish this. I have the ideas in my head
but they are not working well when I try to get it into
the code. I have thought about using a subform, but am
unclear how to accomplish this. Would I need to create
another table that will house the invoice number and
orders associated with that invoice as well as for invoice
payments?

If anyone has any ideas, suggestions, tips they are all
greatly appreciated. Please feel free to email me if you
need more information: brook at karmaimports dot net

Thanks in advance!

Brook
 
What you will want is a query that will return the orders for a selected
customer, and then base a report on that query.

Create a form (name it frmCustomerChoose) that allows you to select the
customer in a combo box (name it cboCustomer), and have the CustomerID be
the bound column of that combo box. Put a command button on that form that
will open the desired report.

The query should have a criterion expression similar to this for the
customerID field in the query:
Forms!frmCustomerChoose!cboCustomer

The report will use the query, which will get its value from your form, to
build the invoice.
 
Thank you very much for the information.

This is a very helpful start!!

If you don't mind, I do have a couple other questions.

Will I need to create a new table for my invoices? That
will contain the invoice details for the orders?

Once I have my cbobox that I will use to choose my
customer, how do I set it up to choose just the orders for
that customer?

Will I need to just set up a Checkbox on my form to check
if I want that order to be placed on the invoice?

Thanks again!

Brook
 
Your orders table has to have a foreign key that points
back to the customer, otherwise you will never know which
order(s) belong to which customer(s).

In your subform then, use LinkMasterField and LinkChild
field to show which field links the two tables.

Once you can display this information on screen, create a
report in the same way which will then be used to print
that information. [forms are for viewing, reports are for
printing ... say it with me]

HTH
 
Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Thank you very much for the information.

This is a very helpful start!!

If you don't mind, I do have a couple other questions.

Will I need to create a new table for my invoices? That
will contain the invoice details for the orders?
Click to expand...

I assume that your order information is already stored in a table or tables.
So what you will want is a query that will return the required information
for a report to use. That query should use the customerID value to filter
for just the records for that customer. No need for a separate table.
Once I have my cbobox that I will use to choose my
customer, how do I set it up to choose just the orders for
that customer?
Click to expand...

The combo box should show list of all customers. When you select a customer
from the dropdown list, the combo box needs to hold the customerID for that
customer (this is what I mean by setting the bound column of the combo box
to the field in the combob box's Row Source that is the customerID value).
Your query then uses this combo box's value as the filtering criterion
value.
Will I need to just set up a Checkbox on my form to check
if I want that order to be placed on the invoice?
Click to expand...

Now you're getting a bit more fancy. My original answer was that you would
automatically generate a report including all orders for that customer (with
assumption that you might have a status field for the order info that would
be true if already invoiced, and thus be filtered out of the result from
this setup, or false if not invoiced and thus selected). It appears that
what you want is to display each of the orders on the form and then decide
which ones are to be invoiced.

Showing all the orders based on a selection in the combo box is fairly easy.
Put the combo box in the form's header, and then set the form's Record
Source to be the query that selects the orders for the chosen customerID.
Note that there must be a field in the query's source table that allows you
to select the order for invoicing (this is needed for the next part,
selecting the orders on the form) to work correctly -- you may need to add
such a field to one of the tables. The form should be continuous forms view
so that the orders will displayed on the form. You can put a checkbox on the
form (in the detail section) that should be bound to the "special" field
that I noted a few sentences back. This checkbox, when checked, then allows
you to select certain records and not other records.

To then print a report for just the selected orders, you must use VBA code
to loop through all the records and then generate an SQL statement as a
query that would be used by the report for knowing which orders are to be
printed on the invoice. How you set this up is a bit tricky, as the report's
controls need to be bound to fields in the resulting record source for the
report, but the report needs to not have a record source until your code
gives it one.

I do this by creating a temporary query that has all the fields that the
report will need, and then create a report that has that query as the record
source of the report. Design the report and bind the controls to the
appropriate fields. When all is done, delete the query's name from the
report's recordsource and save. You then would put code similar to this in
the report's OnOpen event procedure (this code assumes that you are using
ACCESS 2002 or 2003):

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Me.OpenArgs
End Sub

Save and close the report.

Then you would use VBA code in the form to open the report and to give the
report an SQL query to use as the recordsource. This code could be run from
a command button on the main form:

Private Sub cmdButtonName_Click()
Dim strSQL As String
' I don't know which tables and fields you'd use,
' so this code is very generic
strSQL = "SELECT FieldName FROM TableName WHERE "
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
If Me.CheckboxName.Value = True Then _
strSQL = strSQL & "OrderIDFieldName = " & _
Me.OrderID.Value & " AND "
.MoveNext
Loop
End With
strSQL = Left(strSQL, Len(strSQL) - 5) & ";"
DoCmd.OpenReport "ReportName", OpenArgs:=strSQL
End Sub

This is a bit complicated but very doable. Hope that it gives you some
ideas!
 
Sorry.... made an error in the last part of the last code snippet:

Private Sub cmdButtonName_Click()
Dim strSQL As String
' I don't know which tables and fields you'd use,
' so this code is very generic
strSQL = "SELECT FieldName FROM TableName WHERE "
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
If Me.CheckboxName.Value = True Then _
strSQL = strSQL & "OrderIDFieldName = " & _
Me.OrderID.Value & " OR "
.MoveNext
Loop
End With
strSQL = Left(strSQL, Len(strSQL) - 4) & ";"
DoCmd.OpenReport "ReportName", OpenArgs:=strSQL
End Sub
 
Back
Top