Updating Foreign keys automatically

[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..