Link two tables

B

Ben

I will try to keep this as simple as i can: I have a form where I enter data
called 'interface'. Every now and then I am unsure if the data I am entering
is accurate so I create an 'action' which reminds me why I am unsure. There
is a seperate form called 'actions' that I made to enter this data.
I then created another form where I select the 'interface' and 'action' from
listboxes and click 'link' which enters this linking into another table
through an append query.
I see this as too labour intensive, so I would like to have a button on the
'interface' which, when clicked, would open the actions form at a new record.
After I enter the action I would like to click 'link' and it will update the
table automatically. If you have any ideas I would love to know. I have
almost made a solution, but I need the 'interface' ID to be displyed on the
'action' form so I can change the SQL in my append query. How can I open the
form, go to a new record, and copy the 'interface' ID into a field???? thank
you.
 
T

tina

have you considered linking the Interface and Action tables directly?
if each action is unique to one specific interface, and each interface will
never have more than one action, then you have a one-to-one relationship.
because many of your Interface records will *not* have an associated Action
record, you have a sound reason for using two tables with a 1:1
relationship.
for a 1:1 relationship (or for a 1:n relationship) you don't need a third
"linking" table. just add the primary key field of the "1" table (Interface)
to the "1 or n" table (Action) as a foreign key field. if the primary key
field is an Autonumber data type, then the foreign key field needs to be
Long Integer; otherwise, the data type of the two fields must match.
in the Relationships window, link the two tables from the primary key field
to the foreign key field. create a subform (Action) in the main form
(Interface), so the foreign key field value will be entered automatically
when you enter a record in the subform. or use a separate form (Action) if
you want, and programmatically insert the foreign key field value, on the
form's BeforeInsert event or BeforeUpdate event.

hth
 
B

Ben

thank you tina, that makes sense. How can I go about inserting the foreign
key field in the seperate action form's beforeInsert updates?
 
T

tina

in the Action form's BeforeUpdate event procedure, add something like

Me!ForeignKeyField = Forms!OtherFormName!PrimaryKeyField

substitute the correct form and field names, of course. note: the code is
almost the same if you choose to use the form's BeforeUpdate event instead,
except that you should run the action only in a "new" record, as

If Me.NewRecord Then
Me!ForeignKeyField = Forms!OtherFormName!PrimaryKeyField
End If

hth
 
Top