Referential integrity

[Derek Brown]

Hi all

I have an Invoicing form that consists of 1.main form, (customers details)
2. Subform Invoice details (invoice number delivery address etc. and 3.
Sub-subform, Invoice items.(Products prices etc).

The Problem. The form has three tables one for each form linked with
referencial integrety. When a customer calls, the user finds the customers
details. The user usualy tries to skip entering details in the first
subform (2) and tries to enter information straight into the item subform
(3) without entering information into the first subform (2)causing a gap in
the link between the three tables. Is it possible to automatically enter a
value linking the field between (1) and (2) so that they can go straight to
the Item or SuSubform (3) without causing a referencial integrity error?

 

[Larry Linson]

Perhaps your business needs are different, but this is not the way I have
created invoices -- my main form would be the invoice (Invoice Table), which
might have a subform containing customer data (Customer), and another
subform containing item data (Invoice Lines or Invoice Items). Most likely
the relationship between customer and invoice would be one customer - many
invoices, and the relationship between item and invoice would be one
invoice - many items. But they would not be shown in a Form containing a
Subform which contains a Subform.

Larry Linson
Microsoft Access MVP

 

[Derek Brown]

Thank you Larry

It's amasing how we all do stuff different.

In my early years in business (manufacturing) I got involved with writing
databases because I could not stand the confusing way that everything I
purchased (especialy accounts software) was writen. I absoluteley hated
forms that lead to another form and then another. By the time you get to the
form you need the all life has left your body and the bloody purpose of it
has gone out of your mind. And god forbid a customer wanting information
whilst in the middle of this process. What I try to do is have a form that
shows the customers details always on the main form and everything works
around this. Trouble is I have got so parranoid about it that I sometimes do
not perceive another way.

How do you find one invoice out of a group of a dozen for that client that
may be away from his office or has lost an invoice. Can you still find it
using a form where the invoice is the main form?