Access Query Update Question between two tables

Question

Hello.&nbsp;I am having a problem with updating from one table to another.&nbsp; &nbsp;Maybe someone has seen this scenario somewhere.&nbsp; Help is much appreciated.&nbsp; Here it goes.&nbsp; Table 1 is "Current" and is the source.&nbsp; Table 2 is "All Holds" and is the destination.&nbsp; The inner join is "SHPMT ID".&nbsp; SHPMT ID mostly have one unique number but sometimes they are the same.&nbsp; The field I want to update is "Filerentrynbr" from the source table (Current) over to the destination one (All Holds).&nbsp;As such:(Current)SHPMT ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILERENTRYNBR123433&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335441-DR01123321&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335442-DR01222222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335454-DR01222222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335454-DR02222222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335456-DR03122343&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335457-DR01&nbsp;All Holds123433&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335441-DR01123321&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335442-DR01222222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;222222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;222222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;122343&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335457-DR01&nbsp;Just say, I want to update the destination table that have empty fields.&nbsp; The single SHPMT ID numbers have no problem updating each Filerentrynbr since they are single and unique.&nbsp; The problem arises when it runs into similar ones like the "222222".&nbsp; Instead of filling each one (222222) with separate Filerentrynbr as seen in "Current", access fills them with the first Filerentrynbr it sees.&nbsp;The results:All Holds123433&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335441-DR01123321&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335442-DR01222222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335454-DR01222222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335454-DR01222222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335456-DR01122343&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2720-3335457-DR01&nbsp;I have checked the source table.&nbsp; There is nothing unique with each field.&nbsp; That is unfortunate.&nbsp; They are all the same and I can't use any of that to uniquely update the Filerentrynbr's correctly in the destination table.&nbsp; Any help is appreciated it.&nbsp;&nbsp;

arnel_gp · Answer

JohnnyO320&nbsp;you will need vba (imo) to accomplish that.see the two tables in this demo first.next see and run the function in Module1 to update&nbsp;[FILERENTRYNBR] of [All Holds] table.

johnnyo320 · Answer

Thanks very much.&nbsp; Sorry for the late response.&nbsp; Yes, your Module 1 does what it supposed to do in my access database when I run the module all by itself.&nbsp; The module assigns each similar SHPMT ID's its unique FILERENTRYNBR what I wanted.&nbsp; I renamed it to DRN in my access database.&nbsp; There is one question though.&nbsp; I am having a problem running the code in macro once I am downloading data into all holds table.&nbsp; Maybe you have a suggestion.&nbsp;Here is the photo of my macro download form named "go to download form 1"&nbsp;&nbsp; &nbsp;The macro closes one form and then opens another once the data is transferred.&nbsp; But I get an error trying to do it.&nbsp; Don't know why.&nbsp; Here are the photos.&nbsp;&nbsp;Not sure what is going on.&nbsp; All the data is transferred into All Holds table before running the DRN Code.&nbsp; Then the final two updates go into play which are Update Pest ID and Update Quarantine Status.&nbsp; Thanks.&nbsp;&nbsp;

johnnyo320 · Answer

I did place fncUpdateOnHold() in the runcode Function Name instaed of DRN for the macro I am downloading. It's basically the same error.

arnel_gp · Answer

if you are willing to upload your db, i will look into it.is it possible to rename all your macro, without space in them, and much shorter name?

johnnyo320 · Answer

Hello. I figure it out. I had to move the CloseWindow download form 2 function all the way down on the list right before CLoseWindow download form 1 and it worked like a charm. Don't know why. Maybe because I am using "download form 2" as a window of what I am about to download which is data from the "Current" table. This really helps. Now to test it out on larger scale and everyday use. You don't know how grateful I am. Thank you!

Forum Discussion

Access Query Update Question between two tables

Share

Resources