I have three tables setup in a 1 to 1 relationship using a Autonumber field as their primary keys. I created the seperate tables to group related data together and reduce the tables sizes in general. So think of it as one main table and two tables of extended data.
I need to be able to add a record to table 1 and have it use the same autonumber to reserve that row for data in tables 2 & 3 so that my relationships stay in sync. I want to update the data in tables 2 & 3 later when that information is available.
I have been breaking the database testing some things out like building a form from a select query using all three tables. When I add the record to table 1 nothing is added to the other two. Things get messy on subsequent attempts to add data to any of the tables.
I have avoided this problems in other tables by not using the autonumber datatype, however, I don't have a good unique key that can be manually entered and kept accurate, so autonumber solves that problem in this case.
My VB is very rusty but I'm thinking there should be a way, before update, to capture the autonumber being used for the new record and write it to the other tables. I'm thinking that would be one solution, but I can't seem to get started on that code. Any help or examples would be great.
Thanks,
Jim
I need to be able to add a record to table 1 and have it use the same autonumber to reserve that row for data in tables 2 & 3 so that my relationships stay in sync. I want to update the data in tables 2 & 3 later when that information is available.
I have been breaking the database testing some things out like building a form from a select query using all three tables. When I add the record to table 1 nothing is added to the other two. Things get messy on subsequent attempts to add data to any of the tables.
I have avoided this problems in other tables by not using the autonumber datatype, however, I don't have a good unique key that can be manually entered and kept accurate, so autonumber solves that problem in this case.
My VB is very rusty but I'm thinking there should be a way, before update, to capture the autonumber being used for the new record and write it to the other tables. I'm thinking that would be one solution, but I can't seem to get started on that code. Any help or examples would be great.
Thanks,
Jim