Updating Foreign keys automatically (1 Viewer)

[sbrown106]

Hi there,

I have 2 tables in Access, the primary table is called tbl_Clients, with a form frm_Clients, which is used by the user to create a new client record,
tbl_Clients has a primary key ClientID.
The second table is called tbl_Reports with a form, frm_Reports, this table uses ClientID as its foreign key.
When the user creates a new record by entering data into frm_Clients a new primary key (ClientID) is created, the user then needs to make use of this key in the frm_Reports. I s there a way to do this automatically, I know Access doesnt do this automatically.

As a workaround to try and to this I created a query that when run displays the last entry in the ClientID column in the primary table(tbl_Clients) when the user enters a new record . The trouble is I need to run the query every time to display the result, is there some way of doing this without having to run the query every time? Ive tried looking at the some SQL threads but couldnt get them to work

Thanks a lot for any guidance

 

[theDBguy]

Hi. As an alternative, would you consider using frm_Reports as a subform to frm_Clients? If you do, then Access will automatically add the foreign key for you.

 

[sbrown106]

Hi, thanks - the trouble is there are going to be several forms that need to reference the primary key, so trying work arounds but not having much luck

 

[bob fitz]

Hi, thanks - the trouble is there are going to be several forms that need to reference the primary key, so trying work arounds but not having much luck

So why not use a main form with subform as suggested by theDBguy earlier

 

[theDBguy]

Hi, thanks - the trouble is there are going to be several forms that need to reference the primary key, so trying work arounds but not having much luck

And you cannot have these several forms used as subforms?

 

[plog]

the trouble is there are going to be several forms that need to reference the primary key,

Form based on tbl_Clients with all the tables using ClientID as a foreign key as subforms is the way to go. For multiple subforms use a tab control so that each subform is on its own tab..

 

[Pat Hartman]

It is generally poor practice to force the creation of records for which you have no data except the FK so you don't want to create the Report record at the same time you create the Client record. Using a subform as the others suggested is the best solution. Setting the Master/Child links allows Access to automatically populate the FK in the subform. This link is created automatically when you add a subform if you have defined a relationship between the two tables in the Relationships window. If you haven't defined a relationship (you should), you will need to create the Master/Child links yourself.