making fields on a table required

  • Thread starter Thread starter Devon
  • Start date Start date
D

Devon

I have two tables I am working with.
Table X = customer information
Table Y = Account information

I am doing data entry by having a form (Main form) linked
to Table X. Withing this main form, I have a subform,
which is a form linked to table y. How can I make fields
in the subform required, so that for each customer, you
must enter at least one account number, and its relevent
information?

I know how to make the fields required for a single table,
but don't know how to do it on a linked table so if there
is data in Table X, there must be at least one record
linked to Table X in Table Y.

Please let me know if you have any questions.

Thanks in advance.
 
Devon said:
I have two tables I am working with.
Table X = customer information
Table Y = Account information

I am doing data entry by having a form (Main form) linked
to Table X. Withing this main form, I have a subform,
which is a form linked to table y. How can I make fields
in the subform required, so that for each customer, you
must enter at least one account number, and its relevent
information?

I know how to make the fields required for a single table,
but don't know how to do it on a linked table so if there
is data in Table X, there must be at least one record
linked to Table X in Table Y.
Click to expand...

This is 'enforced integrity' upside down, right? That is, editing a
record in table x must be allowed to finish (update), and inserting must
be allowed, only if subrecords exist.

That is not possible; the main record must exist before you can enter
subrecords at all, and so the main record must be saved before a
subrecord can exist.

You could write a little procedure that upon insertion of a main record
immediately adds a 'bogus' subrecord, which the user is allowed to
change, but you put a brake on Delete (by using the Cancel parameter for
the Delete event in the subform) halting when there is only 1 subrecord
left.
 
Why do you want to do that? If you delete the last or only order for a
customer, do you really want to delete the customer record as well? What is
logically or practically wrong with having a customer who does not have any
orders yet?

HTH,
TC
 
How can I make fields
in the subform required, so that for each customer, you
must enter at least one account number, and its relevent
information?
Click to expand...

In fact, there is a logical reason why what you want is not a good thing --
it's a thing called an Insert Anomaly.

I assume you don't want to have an account without a customer: therefore
the foreign key Accounts.CustomerNumber must be a NOT NULL (i.e.
Required=True) field.

Now, until you have created the customer record, you won't have a
CustomerNumber value to put in that field, so you won't be able to save the
Accounts record.

But your rule would mean that you could not create the customer record
unless there was an account pointing to it first, but the account could not
point to it because it wouldn't exist! Taken all the way back, you would
never be able to add the first customer to the database because you
wouldn't be able to add the first account. Therefore, your database would
have to stay empty forever. Not good.

Hope that helps


Tim F
 
Tim said:
Now, until you have created the customer record, you won't have a
CustomerNumber value to put in that field, so you won't be able to save the
Accounts record.

But your rule would mean that you could not create the customer record
unless there was an account pointing to it first, but the account could not
point to it because it wouldn't exist! Taken all the way back, you would
never be able to add the first customer to the database because you
wouldn't be able to add the first account. Therefore, your database would
have to stay empty forever. Not good.
Click to expand...

True, but doesn't my approach handle this? Upon insertion of a main
record, force a subrecord with bogus values.
 
Tim Ferguson said:
In fact, there is a logical reason why what you want is not a good thing --
it's a thing called an Insert Anomaly.

I assume you don't want to have an account without a customer: therefore
the foreign key Accounts.CustomerNumber must be a NOT NULL (i.e.
Required=True) field.
Click to expand...

But he says: "for each customer, you must enter at least one account
number". So he doesn't want to have a customer without an account.

Now, until you have created the customer record, you won't have a
CustomerNumber value to put in that field, so you won't be able to save the
Accounts record.

But your rule would mean that you could not create the customer record
unless there was an account pointing to it first, but the account could not
point to it because it wouldn't exist! Taken all the way back, you would
never be able to add the first customer to the database because you
wouldn't be able to add the first account. Therefore, your database would
have to stay empty forever. Not good.
Click to expand...

Au contraire! It would vastly simplify lots of the code, no? :-)

TC
 
TC said:
But he says: "for each customer, you must enter at least one account
number". So he doesn't want to have a customer without an account.
Click to expand...

Well, either she has an account without a customer, or a customer without
an account. The first is garbage, the second is liveable-with. But there's
nothing in between.
Au contraire! It would vastly simplify lots of the code, no? :-)
Click to expand...

Wouldn't life be so easy if it weren't for (a) users and (b) data? <g>

All the best


Tim F
 
Back
Top