Form help

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

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 ***

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

Re: Form help

Hi Troy,

Yes on the advice.

Note: it appears that someone is aliasing me as a chinese spammer. The
message above with the chinese is not my spam.

As for the advice,

I would make the subform ReadOnly - you can lock the textfields in the
subform to make them readonly. Then a user can only delete rows from
the subform (if it is in datasheet view).

In order to control when a new detail record gets submitted to the
detail table - you should create a separate data entry form for entering
detail data. This form will be unbound and only contain the same text
fields that the detail table contains. You can auto populate the FK
textfield a few ways - use an application global variable (not
recommended but easiest to implement). A more reliable method is to add
a Get Property to the main form

dim currentPK As Integer

Public Property Get mainPK() As String
mainPK = currentPK
End Property


which would hold the current PK on display, and when the Detail data
entry form is opened - read mainPK. You can store the current PK in
currentPK var on the main form's current event. Now the user can enter
data and submit it when ready or cancel it.

Or, forgo autopopulating the FK field on the Data Entry form and just
have the User manually input the currentPK - not as reliable, but the
easiest to implement.

Rich

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