Problems wih record source

[Guest]

Hi,

I am designing a sales invoice form using access 2002, l have a combo box
created that links to my customer details form and pulls accross my customer
names, when l select the customer name it fills in the invoice details
automatically using the information from the customer details section. To get
this to work l set the record source of my sales form to customer details.
Now when l click the new order button on my S.I.F there are no new records
being created. I think this may have something to do with not having a table
created specifically for the S.I.F as l don't have an ID for this page yet,
is there any way l can create a different ID for the S.I.F without changing
the record source?

Any help/ideas would be much appreciated.

Colin Anderson.

 

[Guest]

Colin:

The usual model for this sort of thing would be to have tables along these
lines:

Customers
Invoices
InvoiceDetails
Products

Customers relates to Invoices on CustomerID (the primary key of Customers
and a foreign key in Invoices). Invoices relates to InvoiceDetails on
InvoiceID, and similarly Products relates to Invoices on ProductID.
InvoiceID and ProductID are thus the composite primary key of InvoiceDetails.

The Customers table has columns for the customer name, address etc.
The Invoices table has columns for invoice date etc.
The Products table has columns for product name, unit price etc.
The InvoiceDetails table actually models the many-to-many relationship
between Invoices and Products and in addition to the InvoiceID and ProductID
columns would have columns for quantity, unit price etc.

For data input of an invoice you would have a form based on the Invoices
table and a subform within it based on the Invoice details table, the two
being linked on InvoiceID. You'll find a similar arrangement in the sample
Northwind database in the case of the Orders Form and its Orders Subform.
Note in this how the UnitPrice is looked up from the Products table in code
in the ProductID combo box's AfterUpdate event procedure and assigned to the
UnitPrice control. It is essential that the unit price is assigned to the
column in the InvoiceDetails table in this way rather than being looked up
via a query on the fly each time. As the unit prices change over time you
want the prices in the InvoiceDetails to remain static, not to reflect the
current unit price of the product.

You'll notice that the Northwind database does not have an Invoices form,
only the Orders form. This is because it generates its invoices as reports
based on the orders data. This is fine if the invoices mirror the orders
one-for-one, but if it is necessary to raise an invoice against part of an
order, or against multiple orders as a single invoice then the simplified
approach of the Northwind database is not adequate and a separate Invoice
form and subform would be required. It would be prudent to design this in
such a way that invoices can only be raised for ordered items, and that the
same items cannot be invoiced twice of course.

Ken Sheridan
Stafford, England