Still have a Relationship problem

[Rock]

Hi,

To make it simple I have 2 tables..each with a form for data input.

The first table is the main contact table with the Primary key
'ContactID' (AutoNumber). Data is fed using the Contact form with a
hyperlink button to the second form.. Transactions.

Transactions is the second table and the relationship will be many
transactions to each Contact. I have the Primary Key here as
'TransactionsID' (AutoNumber) and it also includes a field I have named
ContactID thinking I need this for the relationship. This is where I am
not sure I have done it correctly as I don't see the ContactID in that
field in the form.

When I have entered data into the ContactID form then taken the link to
the Transactions form, I see *all* the data I have added for all the
Contacts *not* just the ones for that Contact.

I have set the relationships as ContactID to TransactionID (One to Many).

In Summary I want to enter data into a Contact form then go from that
form to a Transaction form and enter all the transactions for that
Contact. I assume the only data I will see when I go from Contact to
Transaction will be ONLY for that Contact.

What am I doing wrong here? Confusion has set in!!

Rock

 

[Jeff Boyce]

Rock

One Contact, many Transactions, right? Sounds like a perfect setup for a
main form (Contacts) - subform (Transactions) design. Instead of
hyperlinking, try embedding the transaction form as a subform. Use the
ContactID as the Parent-Child links.

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

As Jeff pointed out, with an embedded subform you won't need any code at all.
If you don't have enough screen real estate for a subform and want to go
with a popup form, use a query as the RecordSource for the popup and in its
WHERE clause you can reference the ClientID on the first form to restrict
what the popup shows:
Select ...
From ....
Where ClientID = Forms!frm1!ClientID;

Then in the BeforeInsert event of the popup form, you need to populate the
ClientID for the transaction table:
Me.ClientID = Forms!frm1!ClientID

Subforms handle the filtering and foreign key population automatically as
long as the master/child links are properly set.