Re: Help ... Creating an invoice

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

Date: Tue, 24 Aug 2004 20:50:55 -0400

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 

-- 
        Ken Snell
<MS ACCESS MVP>
"Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
news:eKJ4ItjiEHA.3264@tk2msftngp13.phx.gbl...
> 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
> > >>
> > >
> > >
> > >.
> > >
>
>
Loading