Copy one record to another record on the same table

[MunroeM]

Hello,

Im trying to make a query that will copy data from one record to another already created record. As you can see in the picture I have all the records pre filled out with the tag numbers so they can pick them from a drop down list.

The user will select Update Tag Number, Then select the Curent tag number they want to change, then they will select the new tag number they want it to be. In this case we are changing tag number 1204 to tag number 1206. Now i need a query that will take the Info highlighted in red from the 1204 record and copy or move it down to the 1206 record once the save and exit button is clicked.

This is the current Query im using but its not working and im not sure what else i can do. If there is another way to do this that might be easier im open for suggestions also.

Thanks

UPDATE USTagT SET USTagT.TemmisNum = [Forms]![USTagInOutF]![TemmisNum], USTagT.Description = [Forms]![USTagInOutF]![Description], USTagT.NSN = [Forms]![USTagInOutF]![NSN], USTagT.PartNum = [Forms]![USTagInOutF]![PartNum], USTagT.SerialNum = [Forms]![USTagInOutF]![SerialNum], USTagT.Location = [Forms]![USTagInOutF]![Location], USTagT.Notes = [Forms]![USTagInOutF]![Notes]+[Forms]![USTagInOutF]![Combo120], USTagT.SignedOut = Yes, USTagT.USTagNum = [Forms]![USTagInOutF]![Combo231]
WHERE (((USTagT.USTagNum)=[Forms]![USTagInOutF]![Combo231]));

Thanks for the help

[June7]

"not working" means what - error message, wrong results, nothing happens?

Don't see need for the expression to change USTagNum value but should not hurt.

Once 1206 is populated with 1204 data, does 1204 need to be all set to Null?

This is odd process. I suspect data structure is not optimal. Sounds familiar - think I've worked with your other threads.

[MunroeM]

Sorry, Not working means nothing is happening in this case. You have helped me out a number of times already on different sections of this project im working on.

Correct once tag 1206 is populated with the 1204 data i will be clearing all the data from tag 1204 except for the tag number. So in the photo of the table above 1204 would look just like all the other tags you see there after it has been moved to 1206.


The reason i am doing this is because once you sign out a tag for a tool, sometimes you need to change the tag you signed out with a different one. Instead of making them sign the tag in, then sign out and re enter all the info again for the new tag. I want to give them the option to just change the tag number. But because i have all my tags pre entered i cant just change the tag number as i will have duplicate tag #'s and missing tag #'s.

[June7]

And that's what a 'junction' table is for - to associate records in a many-to-many relationship.

Regardless, I cannot see reason for the query failure. Are both tag combos UNBOUND?

Might help if you gave controls meaningful names.

If you want to provide db for analysis, follow instructions at bottom of my post.

[MunroeM]

Here is a cut down version of my data base. The U/S tool tag register is the one i need to have the update for

Thanks for helping

[June7]

I would not run a query object. I would run SQL statement in VBA. Regardless your query should work.

I don't set Modal and Popup during development - gets in the way - and seldom use these settings in deployed db.

I clicked U/S Tag Register button. I put a breakpoint in the Save button code. The code always goes to the first ElseIf Me.SignInOut.Value = 3 and never runs the query in the second ElseIf Me.SignInOut.Value = 3 at the end of the conditional code. Fix the code so you have only one test on this condition.

Suggest you learn more about how to debug code. I would go nuts if I couldn't set breakpoint. Review link at bottom of my post.

[MunroeM]

I know exactly what part you are talking about. I definitely overlooked that when i wrote that part of the code. I was trying to make it so if the update button is selected and one or both of the combo boxes has nothing entered it would tell you to pick a tag number from the combo boxes.

Thanks for the link to the Debugging. This is something I really need to learn and im sure it will be a valuable tool to know

thanks for the help