how to have a form that generate invoice no.

  • Thread starter Thread starter Revned
  • Start date Start date
R

Revned

hi,
sorry to disturb anyone again
i have 2 tables in my database 1 for tblCustomer and 1 for tblOrders
for my tblCustomer i have the following fields
CustId: (Autonumber) PK
CustName: Text
HomeAddress
Contacts
Remarks

for mytblOrders i have the following fields
OrderId (Autonumber) OK
Custid
CustName
Item
Qty
Price
Tax
OrderDate
InvoiceNumber

i want to accomplish that every new orders from a customer access will
generate Invoice No, like

Invoice No. 802
CustID 6
CustName Revned Cruz

OrderItem Qty Price
4 2 2.75
1 6 1.67

then the second customer takes another order

Invoice No. 803
CustID 4
CustName Norman Athan

OrderItem Qty Price
8 7 12.50
1 6 1.67

i need to accomplish like from this example that access generates Invoice no.

1. Do my table structures are on the right way or maybe it will just make me
complicated? how to make it easy? Do I need to add more tables, if yes can
anyone show me what fields the table may have?

2. How do i construct my Query?
3. What do my form be look like, like the controls and property settings for
new order data entry?

thanks for understanding and patience i really appreciate your help
 
In order to do what you want, you need to "normalize" your order structure:

mytblOrderHeader
OrderID autonumber PK
CustID long integer FK -- points to an entry in tblCustomer
InvoiceNbr long integer (Set this to Unique, no duplicates, no nulls)
OrderDate, etc. -- DO NOT include fields for Cust Name. That's part of
tblCustomer

mytblOrderDetail
OrderDetailID autonumber PK
OrderItem
OrderQty
OrderPrice
etc.

To get the next invoice number, use:

NewInvoiceNbr=dMax("InvoiceNbr","mytblOrderHeader") +1

HTH

Jim
OrderID FK -- Points to mytblOrderHeader
 
Back
Top