Jul 27 2022 08:40 PM
Jul 27 2022 08:40 PM
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).
SHPMT ID FILERENTRYNBR
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.
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.
Jul 27 2022 11:16 PM
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.
Jul 30 2022 01:10 PM
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.
Jul 30 2022 05:47 PM
Jul 30 2022 09:40 PM
Jul 31 2022 05:53 AM