Access Query Update Question between two tables

[JohnnyO320]

Hello.

I am having a problem with updating from one table to another.   Maybe someone has seen this scenario somewhere.  Help is much appreciated.  Here it goes.  Table 1 is "Current" and is the source.  Table 2 is "All Holds" and is the destination.  The inner join is "SHPMT ID".  SHPMT ID mostly have one unique number but sometimes they are the same.  The field I want to update is "Filerentrynbr" from the source table (Current) over to the destination one (All Holds).

As such:

(Current)

SHPMT ID         FILERENTRYNBR

123433          2720-3335441-DR01

123321          2720-3335442-DR01

222222          2720-3335454-DR01

222222          2720-3335454-DR02

222222          2720-3335456-DR03

122343          2720-3335457-DR01

All Holds

123433          2720-3335441-DR01

123321          2720-3335442-DR01

222222          

222222          

222222          

122343          2720-3335457-DR01

Just say, I want to update the destination table that have empty fields.  The single SHPMT ID numbers have no problem updating each Filerentrynbr since they are single and unique.  The problem arises when it runs into similar ones like the "222222".  Instead of filling each one (222222) with separate Filerentrynbr as seen in "Current", access fills them with the first Filerentrynbr it sees.

The results:

All Holds

123433          2720-3335441-DR01

123321          2720-3335442-DR01

222222          2720-3335454-DR01

222222          2720-3335454-DR01

222222          2720-3335456-DR01

122343          2720-3335457-DR01

I have checked the source table.  There is nothing unique with each field.  That is unfortunate.  They are all the same and I can't use any of that to uniquely update the Filerentrynbr's correctly in the destination table.  Any help is appreciated it.  

[#2]

@JohnnyO320 

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 [FILERENTRYNBR] of [All Holds] table.

[#3]

Thanks very much.  Sorry for the late response.  Yes, your Module 1 does what it supposed to do in my access database when I run the module all by itself.  The module assigns each similar SHPMT ID's its unique FILERENTRYNBR what I wanted.  I renamed it to DRN in my access database.  There is one question though.  I am having a problem running the code in macro once I am downloading data into all holds table.  Maybe you have a suggestion.

Here is the photo of my macro download form named "go to download form 1"

   

The macro closes one form and then opens another once the data is transferred.  But I get an error trying to do it.  Don't know why.  Here are the photos.

Not sure what is going on.  All the data is transferred into All Holds table before running the DRN Code.  Then the final two updates go into play which are Update Pest ID and Update Quarantine Status.  Thanks.  

[#4]

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

[#5]

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?

[#6]

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!