Creating records automatically

  • Thread starter Thread starter Bernard
  • Start date Start date
B

Bernard

I have two simple tables in Access 2000

Companies Address
CompanyID* AddressID*
CompanyName Street
City
CompanyID

How do I automatically create a new Address record
when a new Company record is created?

I setup a relationship between both tables (on CompanyID).
Referential integrity is enforced.
I can then use a Query to combine both, then in the Form View
I can enter something for CompanyName. This creates a new
Company record but not an Address record. I need an address
record to be created with default values as soon as the
company record is created.

Also, I need a solution that works with three or more tables.
Creating a record in table1 creates a record in table2 which
in turns creates a record in table3... etc

Thanks.
 
The form-subform arrangement does this perfectly. Create a main form for
Company, and a subform on it for Address, set the child-master links to the
linked fields ( CompanyID ) and when you enter a new record on the Company
form, the ID will be carried to the Address table as well. And you can
extend to a sub-sub form if you have to.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Adrian Jansen said:
I dont see the need to create a blank record in a table. If no data exists
for Address ( yet ) why create a record ?
Click to expand...

because other queries or forms elsewhere may assume that each company has
an address record, even if it's an empty one.
The alternative is to put checks everywhere to avoid getting errors at run
time, but that's making queries/forms code much more complicated and
it's prone to errors whenever you forget to add checks. The code is a lot
simpler if you can safely assume that each company has an address record
which itself has a telephone record. These can be empty as I said, but they
exist.
You start at the top level, enter a new Company, that establishes its ID,
then as soon as you start entering address data, the ID is copied into the
Address FK, as it should be. Likewise for any other sub forms.
Click to expand...

The forms will be used by administrative clerks. I need to make them absolutely
fool proof and unfortunately forms/subforms doesn't cut it here. A clerk could enter
the address or telephone first before a company name. It's all going to be on the
same page for them. You see that table records could be created in any order if
I rely on the bound property of forms. That's no good since some foreign keys
would then be set to 0 as I explained in my previous post. If reference integrity
checks are enabled, this even causes a run time error (I like ref integrity since
it does the clean up code when records are updated or removed)
Starting at the other end seems the wrong way around to me, but its your
design.
Click to expand...
Again, company, address and telephone would be on one form as far as an
input clerk is concerned. They are in separate tables for normalization purposes
(they're used elsewhere). The clerk can start entering from any point in the form.
If I stick with bound data, this means that table records can be created in any
order... and foreign keys won't be set correctly. It's easy to test. I did it.

Please note that my tables aren't really named Companies, Addresses and Telephones.
I've used those as example here. They're easier to understand.
 
Yes, I see your points, but I still wonder whether its an organisational
problem, rather than a database problem.

Do you have 1_M relations between say Companies and Address tables, or is
the relation 1_1 ? If its 1_1, then you can set the Address tables to have
Required fields, then your users must fill them in. If not, then your forms
must allow for multiple addresses, so it ought to be obvious that you have
to fill in the Company details first.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Hi Adrian, thanks for your comments.

The relation is currently 1:1. One Address for each Company. However, addresses are
also used elsewhere (for employees for instance) so it makes sense to put them in their
seperate table. That's simply the first normal form of a normalized database (no repeating groups
across tables). This also simplifies things when Addresses become more complicated (countries,
states, telephones with contact hours info, and others could be spawned into seperate tables).

As for the 1:M relation (addresses of various departments for the same company), it makes
sense to you and me but mistakes are human. I must absolutely make sure that data is consistently
entered in the db. I can't rely on what makes sense to most people. If one person screws it up,
either by incompetence or by pressing a key by error, the database will not be consistent which will
lead to an error somewhere down the line, maybe weeks later with someone else at the control.
That's what I'm trying to avoid at all cost. Everything is much simpler if I can assume that ALL
address, company and other table entries exist.

This is my first Access project but I've designed and built databases in other systems (Sybase,
MySQL, ...).
 
Back
Top