Since there is always only one invoice item per invoice, you can consolidate
your Invoice table and invoice item tables into one Invoice table that looks
like:
TblInvoice
InvoiceID
InvoiceDate
CustomerID
InvoiceAmount
Your Customer Account table needs revised to:
TblCustomerAcct
CustomerAcctID
CustomerID
BaseRate
AccountBalance
1. Build a query named CreateInvoices based on TblCustomerAcct and include the
fields CustomerID and BaseRate. Convert the query to an append query to append
to TblInvoice. Access will know to append CustomerID to CustomerID but you will
have to tell the query to append baserate to InvoiceAmount.
2. Build an unbound popup form named NewInvoiceDate with a single textbox named
InvoiceDate and labelled "Enter Invoice Date".
3. Build a second query named UpdateInvoiceDate based on TblInvoice and only
include the field InvoiceDate. Make the criteria for the field Is Null. Convert
the query to an Update query and where it says update to put:
Forms!NewInvoiceDate!InvoiceDate
4. Put this code in the AfterUpdate event of InvoiceDate on your popup form:
DoCmd.SetWarnings False
DoCmd.OpenQuery "CreateInvoices"
DoCmd.OpenQuery "UpdateInvoiceDate"
DoCmd.SetWarnings True
When the code runs, it first appends 1500 new records to TblInvoice, one record
for each customer. The InvoiceDate field for all 1500 records will be empty
after the append. The second query then updates the blank InvoiceDate field of
the 1500 records to the date you entered on the popup form.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com
"Jeremy Weiss" <jw*****@hotmail.com> wrote in message
news:vo************@corp.supernews.com...
The company that I'm putting this together for is a services company that
charges their customers a monthly fee. I'm needing something to generate the
usual monthly invoice for all 1500 of their customers. They only have one
service, so the description/product will be the same each month, thus no
need for a product table.
I'm just wanting a button they can click to 'bill' their customers. When
they click the button I want Access to read the baserate value from one
table and create an invoice in the invoice_tbl and a line item in the
invoicedetail_tbl using that baserate value as the amount, then go to the
next customer and do it over.
I did leave out a table though, just not the one you were expecting.
Table: CustomerAccount_tbl
- id (PK & FK Customer_tbl)
- baserate
- accountbalance
The problem is, I have no idea where to start, so if anyone has a few
minutes to explain to me whether I use a module or macro or whatever and
maybe throw in some psudocode I'd really appreciate it.
TIA,
-jeremy
P.S. Thanks for the naming convention suggestions
----- Original Message -----
From: "PC Datasheet" <sp**@bellsouth.net>
Newsgroups: comp.databases.ms-access
Sent: Thursday, October 09, 2003 12:22 AM
Subject: Re: creating invoices for all customers
Two things ---
1. Invoices are created each time an order is received. Why do you want
to cycle through your customers table and create an invoice for each?
2. You missing a Products table.
TblProduct
ProductID
Description
Price
DiscountRate
Inventory
ReorderPoint
NoLongerAvailableForSale (Yes/No)
Then your InvoiceItems table should be
TblInvoiceItem
InvoiceItemID
InvoiceID
ProductID
Quantity
Price
Two suggestions:
1. For the PK of each table use the tablename + ID (See above tables)
2. Your database will be easier to read if you don't use spaces or
underlines in names.