Form help

[troy_lee@comcast.net]

I have two tables. The PK from the parent table (Table 1) is a FK to
the child table (Table 2) with a one-to-many relationship.

On my form, I use a query to return some data to unbound fields from
the parent table for display purposes only. The other fields on the
form are editable and bound to Table 2.

My question is how do I get the data from Table 1's PK into Table 2's
FK field? I am using Table 1's PK as the primary search field for the
form. So, after I select a record, how do I pass only this field to
Table 2? Each record needs this data since it is a FK for the table.

I hope this is clear and thank in advance for the help.

Troy

[Rich P]

Re: Form help

Grettings,

I'm not clear if you want to enter data or display data. But maybe if I
explain about how the one-to-many thing works -- that might help answer
your question.

The primary table would contain this kind of data

mainID, Firstname, Lastname, Address, Phone, email

The second table (the detail data table) would contain the fields as
follows (usually orders for each member of the Primary table)

autonumfld, mainID, product, orderDate

So in the primary table you store customer names and give each customer
a uniqueID.

1, Bill, Smith, 123 xway, 123-4567, bill@bill.com
2, Sue , Jones, 456 yway, 234-4599, sue@sue.com

Detail table would store this

1, 1, paint, 10/01/2002
2, 1, hammer, 10/14/2002
3, 1, nails, 10/14/2002
4, 2, drapes, 1/15/2003
5, 2, lamps, 2/21/2003
6, 2, chairs, 3/3/2003

This is a one-to-many relationship between the primary table and the
Detail data table. In the detail data table Bill Smith is known as 1,
and Sue Jones is know as 2. If you need to add more detail data for
Bill smith, you would enter 1 in the foreign key field of the detail
data table. And for Sue Jones you would enter 2 in the FK field for
each of her orders.

Additionally, with this kind of setup -- the data in the detail table is
usually displayed in datasheet view in a subform on the mainform. You
could enter/edit/delete Detail data directly in the subform or you could
have an additional form for entering/editing detail data. It is usually
a better practice to separate data entry/editing of the primary and
detail data tables in separate forms (where a mainform and a subform are
separate forms) than to edit primary and detail data on the same form -
although it is obviously doable - but not as efficient for a one-to-many
scenario.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[troy_lee@comcast.net]

Re: Form help

Thanks Rich,

I figured out how to do what I was trying. I linked the child form and
parent form together through the field that is serving as the PK in
the parent and FK in the child. Fairly easy and obvious in retrospect.

However, I have another problem now. As soon as I select a record on
the main form from the parent PK field, it is creating a new record in
the child table. The rest of the data for this record is to be entered
on the subform which is bound to the child table.

I want to suppress this until the user clicks on the Add New Record
command button I created which adds the record to the child table. The
reason is because someone may accidentally choose the wrong unit in
the parent table PK field. If that is the case, I don't want the
record created yet because there will no other data associated with
this record. In other words, I don't want anything written to the
child table until the user is sure they are working on the correct
record, enter the appropriate data and press the Add Record button.

Any advice?

Thanks in advance.
Troy