VBA Update Recordset Loop

[creativefusion]

All,

I have a table of data containing 30 fields updated every morning by operations. For the purpose of providing some background to my problem, I will provide samples for 3 of these fields.

Feild 1: ITEM
Field 2: STATE
Field 3: UOM

My problem is that some of the UOM's recorded by the system are blank. For example, ITEM: 23, STATE: 1, UOM: "EA", ITEM: 23, STATE: 2, UOM: "BLANK", etc..

When I track back to the source data, I find that they (the UOM's) are not recorded in the system. As a result, I need to update all the blank records with the first valid record. Problem is, I need to do this for like 30,000 records. So it is very time consuming.

I want to know how I can do the following using open recordset loop method in VBA:

1. Open the table and find a UOM for each ITEM
2. Update all the instances of "NULL" with the UOM

Notes:

I have enclosed a sample db with data for reference.

Any help would be appreciated.

Regards,
CF

 

[Banana]

I wouldn't loop a recordset when it can be done much simply with a SQL query. I don't download files as a rule but here's a query to get you started... Assume the table is named "tblOrderDetails", and the UOM are defined in another table named "tblItems"

UPDATE tblOrderDetails AS o LEFT JOIN tblItems AS i ON o.ITEM = i.ITEM
SET o.UOM = i.UOM
WHERE o.UOM IS NULL 
   OR o.UOM = "";

(the "OR o.UOM ="" " part may actually be overkill but I've been a suspender-and-belt sort of guy...)

HTH.

 

[creativefusion]

Hi Banana,

Thanks for the reply to my problem. I tried to use your method but unfortunately it appears it will not work.

As I said in my first post, I have a table that looks like this.

REF | STATE | UOM

233 1 " "
233 4 "CA"
233 7 " "

I need to find the first record that holds a valid UOM. i.e. the second line. Then, I need to take this UOM and update the first and third records with it.

Sorry to be a nuisance. I am new at this.

CF

 

[Banana]

If the " " are just spaces, then change the last line to this:

OR Trim(o.UOM) = ""

PS I missed the other part about finding the first record that contains the valid UOM.... This is much bigger problem... Ideally, you would force the users to input in a valid value for the UOM instead of cleaning up after them which is much more likely to be error-prone. Even if we did solve this one requirement you can't be sure that it was in fact the correct answer. Few more examples:

Suppose we have this:

233 | CA | EA
233 | CA | 
233 | CA | HR
233 | CA |

which should win? The each UOM or hourly UOM? You say the first record, which begs the question... first of what? Access provides a First() function that returns a record that was entered first even though when you open the table, it's actually 2nd or 3rd in the list owning to the fact that there is no guarantee of order when Access reads the data off the disk. So, the "First" may not actually what you expect.

I'll reiterate - it's much better to force the users to complete the data entry correctly than to clean up and thus introduce further errors. It's also cheaper to just turn on a property or add validation rule than to write an update query that will work correctly.

With that in mind, I think this may do the trick:

UPDATE tblOrderDetails o
SET o.UOM = (SELECT UOM FROM tblOrderDetails WHERE ID = o.ID AND STATE = o.ID)
WHERE o.UOM IS NULL
   OR Len(Trim(o.UOM)) = 0;

Caveat! There is no guarantee of which UOM you will get! If you want to use First(), you would probably need to use a temporary table for that.

 

[creativefusion]

The records are captured daily (automatically) from a query that extracts the data from another system.

When I say first record, I mean that for each ITEM, there is only ever 1(one) UOM. Never anything else to your reference. So, the way I was intending on making this happen was firstly finding the UOM, then updating all the records for that item only. Then moving onto the next one, and so forth.

Thanks for your help by the way.

Regards,
CF

 

[Banana]

Oh, okay, so that's out of your control (the importing). Too bad.

The query I gave you should work since as you say there is only one UOM for each ID + State so it will the job just fine. However, I realized I forgot a important WHERE, so here's it again, fixed up:

UPDATE tblOrderDetails o
SET o.UOM = (
  SELECT UOM 
  FROM tblOrderDetails 
  WHERE ID = o.ID 
    AND STATE = o.ID
    AND UOM IS NOT NULL
    AND Len(Trim(UOM))>0
)
WHERE o.UOM IS NULL
   OR Len(Trim(o.UOM)) = 0;

That should do the trick, I think.

Be sure to do this on a backup as a test.

 

[creativefusion]

Banana,

Your suggestions are great. Managed to do it with two steps. Part 1 to get the unique UOM for the item and Part 2 to Update.

All is well.

Thanks once again.

Kind regards,
CF