Table design question

[deko]

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.

[Rick Brandt]

Re: Table design question

"deko" <deko@hotmail.c om> wrote in message
news:fPYjc.2832 $C%6.2009@newss vr27.news.prodi gy.com...[color=blue]
> This may be an easy question, but for some reason the multiple table[/color]
design[color=blue]
> 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[/color]
main[color=blue]
> 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[/color]
to[color=blue]
> 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[/color]
tried[color=blue]
> 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[/color]
the[color=blue]
> 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[/color]
that[color=blue]
> 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?[/color]

Getting multi-table queries to be updateable is tough with a couple tables,
almost impossible with three or more (as you have found), The subforms idea
is not a kludge. It is the standard and recommended way to do this.

[deko]

Re: Table design question

> Getting multi-table queries to be updateable is tough with a couple
tables,[color=blue]
> almost impossible with three or more (as you have found), The subforms[/color]
idea[color=blue]
> is not a kludge. It is the standard and recommended way to do this.[/color]

Thanks for the tip.

I suppose the only other alternative would be to use something like:

DoCmd.RunSql ("INSERT INTO tblOrders ( [Cust_ID] ) VALUES (" & Me!Cust_ID &
")")

In any case, I've reworked the form in question so that it uses subforms to
populate the different underlying tables. I've tried to make it look like
it's only one form by aligning the input fields using a transparent border
on the subform. The problem is Tab Order - how do I get the cursor to go to
and from the subform according to the intended design of the form?

[subForm_frmAddr ess]
Private Sub Country_KeyDown (KeyCode As Integer, Shift As Integer)
Forms!frmNewRec ord.cmdOK.SetFo cus
End Sub

This doesn't seem to work correctly... is there a better way to control Tab
Order between forms?