Help ... Creating an invoice

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.

--

Ken Snell
MS ACCESS MVP

"Brook" wrote in message
...
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

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
-----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" wrote in
message
...
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



.

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
-----Original Message-----
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
-----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" wrote in
message
...
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



.

.

Comments inline...

--

Ken Snell
MS ACCESS MVP

wrote in message
...
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" wrote in
message
...
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



.

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]" wrote in message
...
Comments inline...

--

Ken Snell
MS ACCESS MVP

wrote in message
...
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" wrote in
message
...
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



.