This may be an easy question, but for some reason the multiple table design
idea is throwing me.
I'm trying to avoid using one large, wide table - so I've got multiple
tables that hold different categories of information about each record -
address information in one table, contact information in another table,
order-related information in a third, and so on.
The "Customer" table has an AutoNumber "Cust_ID" as a PK, which is the main
ID used throughout the database. Each ancillary table has it's own
AutoNumber ID field (e.g. Address_ID) as a PK, with the main Cust_ID as a
foreign key. I believe this is pretty standard normalization. I have
one-to-many relationships with referential integrity and cascading deletes
enabled on all these tables (the "One" side being Cust_ID in the Customer
form).
The problem I'm having is getting data into the tables. I have a
"NewRecord" popup form that is used for data entry. The user types in all
the different information in this one popup form and clicks OK - and the
data is supposed to find it's way to the different tables - address info to
the address table, name and company to the Contact table, etc.
I assume I need to use a query for the recordsource NewRecord Form. I tried
using subforms in the NewRecord form with each subform bound to it's
respective table, but that seemed like a kludge. But can I join 3 or 4
tables as a recordsource for a data entry form? The only purpose of the
NewRecord form is data entry - it opens on a new record, and closes with the
OK button. I got this to work with 2 tables, but not with more that that.
The problem is populating the other tables with the Cust_ID foreign key.
For example, when I enter a new record, the Customer table AutoNumbers that
new record as, say, 981 - how do I get that into the Cust_ID (foreign key)
field of the other tables? Can I write the foreign key to multiple tables
when using a query as a recordsource?
Thanks in advance.