Pushing PK data to another form causes Duplicate Record Error 1

[RobertIngles]

Firstly, thanks to all of you for your help over the last few days. Your input and suggestions have helped tremendously with my project.
Secondly, sincere apologies to DHookom for inadvertenly asking the same question in two threads.

I am not sure if what I want to do can be done.

"FRMCreateNewLaptopandUser" is made up of queries from two tables: "TBLLaptop" and "TBLUser". The goal is for the person unputting to be able to create a new record in both tables at the same time. PK fields are [LaptopID] and [UserID] with {UserID} being the common field for join.

In some cases the [UserID] may already exist in "TBLUser" so once the [UserID]field is completed in "FRMCreateNewLaptopandUser" I have code set in AfterUpdate that looks for a match in TBLUser and if none is found "FRMCreateNewUser" opens for the inputer to add the user details. When the form is closed, the new data populates the vacant user fields in the "FRMCreateNewLaptopandUser".

Works great however if I try to save the record I get an error saying that saving would create duplicate record - I would think because I just created the new user and "FRMCreateNewLaptopandUser" is in add mode. To try and solve the conflict I changed the OnClose data push to push the new [UserID] data only to the TBLLaptop.UserID field in the "FRMCreateNewLaptopandUser" form and set the TBLLaptop.UserID to Null. When I tried to save I got the error that The primary key cannot be null.

I understand the problem, I am just wondering if there is a workaround without having to use two different forms to enter the new laptop profile and the new user profile.

 

[dhookom]

I would not attempt to add records to two separate tables at the same time. Can you share the significant fields from the two tables as well as the SQL view of the form's record source?

I'm not sure I would even have tblUser in the record source.

Duane
Hook'D on Access
MS Access MVP

 

[RobertIngles]

Thanks Duane, I am accomplishing the tasks with one form for each table I need to update.

Thanks again
Robert