Re: Help ... Creating an invoice

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 08/25/04 

Date: Tue, 24 Aug 2004 20:34:46 -0400

Comments inline... 

-- 
        Ken Snell
<MS ACCESS MVP>
<anonymous@discussions.microsoft.com> wrote in message
news:555001c48a19$9d04f5e0$a301280a@phx.gbl...
> 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?
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?
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?
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!
>
> Thanks again!
>
> Brook
> >-----Original Message-----
> >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.
> >
> >-- 
> >
> >        Ken Snell
> ><MS ACCESS MVP>
> >
> >"Brook" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:c82c01c48a14$82fa3070$a401280a@phx.gbl...
> >> 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
> >>
> >
> >
> >.
> >