How to copy ID to another table when new record is created?

[behedwin]

Hey

This is what i have.
Profile_Table
Time_Table

In profile table I have these columns.
Profile_ID (primary key), FirstName, LastName, WorkTitle

In Time table i have these columns.
Time_ID (primary key), Profile_ID_SK (secondary key), Week1, Week2, Week3, Week4

When i create a new profile i get a new profile_ID.
How can i add the Profile_ID to the Profile_ID_SK field automatically so that all new Profiles also shows in the Time_Table?

 

[ranman256]

once the record is saved, the ID is created,
so you can put a 'save' button to:
save the record
run an append query,

(with some code to prevent it from adding > 1)

 

[behedwin]

What do you mean by "save the record"?
What record?

I have a save button when i create a new Profile
What code should i put in that button so that i also takes the newly created Profile and adds it´s ID to a new row in Time_Table?

 

[behedwin]

ok
i have been thinking about this for a bit.

i think i need to do something like this


When i open Time_Form
I now have a combobox with all values from Profile_Table

I want the user to select a Profile in the combobox.
And add a new row to the Time_Table.

How can i do this?

 

[xenou]

The simplest way is if you have a primary key to foreign key relationship defined between the tables (explicitly defined - in table relationships).
Then you can just create a parent form (main form) and a child form (subform). Access links up the records for you automatically (handles creating the values for the foreign key when you add records in the child form.

So, that's the easy way which I don't know if anyone ever uses properly.

You are complicating a little by wanting (or preferring) a combobox instead of using inbuilt MSAccess navigation and filtering. It should still work but you would have to roll your own navigation, which is not so easy. This link tells the ugly story (ugly unless you know what you are doing, anyway): http://www.allenbrowne.com/ser-03.html

There might be other ways, I don't know really.

Finally, you can just add sql or code to your form so that when a record is saved you get the parent ID and add it to records in the child table. That's not hard - you just intercept the Before_Save event and do the dirty work. If you have the information you need on a form or subform then it won't be hard to get that data just before saving, and update the record accordingly. Basically - you do have the information since it will be based on the combobox selection, so just go ahead and use that value.

As a an alternative, you can sort of do the same thing only when you save the Profile_Table records - each time you add a record, just intercept the save event and use it to populate the Time_Form table in advance. But you have to be sure you have control of the insertions - if you are doing this in a form, for instance, then that should be the only way a record is inserted. Then you don't need to worry about it later - records are already there. Or for the best of both worlds (worst of both worlds?) you can do both - insert Time_Form records at the earliest possible moment, and also check if they are there when you first use them, and insert them if for some reason they are not present.