Open two recordsets and populate field in one table based on criteria from another 1

[boomer1974]

Good Afternoon Experts,

I am in need of some help. I have not written any code for 8 years so I am a bit rusty to say the least I have an item table (70K records) that has 4 fields: Item, Cube, Weight, and Code (code not currently populated). I have a case code table with 8 records, each containing the criteria for determining a case code based on cube and weight. I need to open each record in my item table and cycle through the 8 case code records to find a match where item cube and weight are within the upper and lower threshold for both cube and weight then return the corresponding case code to my "code" field in the item table. I have experimented with several threads and can't seem to modify something successfully to get what I want so I thought I would ask. Any help is appreciated! Below are examples of my two tables (hopefully formatting comes through) Thanks!

Item Table
Item Number Cube Weight Code
12345 0.52 1


Case Code Table
ID case_code cube_low cube_high weight_low weight_high Type
1 S1 0.00 0.10 0 1.00 Easy
2 S3 0.00 0.30 1 4.00 Easy
3 S4 0.00 0.30 4 20.00 Average
4 H1 0.00 3.00 20 50.00 Hard
5 AP 0.00 10.00 70 9999.00 Team
6 H4 0.00 9,999.00 50 70.00 Vary Hard
7 S2 0.10 0.30 0 1.00 Easy
8 S5 0.30 3.00 0 20.00 Average
9 H2 3.00 20.00 0 50.00 Hard
10 TP 10.00 20.00 70 9999.00 Team
11 H3 20.00 9,999.00 0 50.00 Hard
12 T2 20.00 9,999.00 70 9999.00 Team

 

[LaurieHamlin]

I'm guessing that case_code is what you want to add to the Item Table and that there are 3 fields in the Item Table?
Item Number
Cube Weight
Code
You could also use an array. To use a recordset:

Private Sub GetCode()
Dim db as database
Dim rs, rst as recordset
Dim rscntr, rstcnt, rsLoopcnt, rstLpcnt as Long
Dim sqlstr as String

Set db = currentdb()
Set rs = db.openrecordset("Select * from [Item Table]", dbopendynaset) 'dynaset allows changes
Set rst = db.openrecordset("Select * from [Case Code Table]", dbsnapshot) 'snapshot because you're not making changes to this table
rscntr = rs.recordcount
rstcnt = rst.recordcount
Do While rsLoopcnt <= rscntr
Do While rstLpcnt <= rstcnt
If rs.Fields("Cube Weight") >= rst.Fields("weight_low") And rs.Fields("Cube Weight") <= rst.Fields("weight_high") Then
sqlstr = "Update [Item Table] set

 = '" & rst.Fields("case_code") & "' where [Item Number] =  '" & rs.Fields("Item Number")_
                   & "'"                                'the _ at the end means code line continued on the next line
              DoCmd.RunSql(sqlstr)
              rstLpcnt = rstcnt
         Else
              rst.moveNext
              rstLpcnt = rstLpcnt + 1
         End If
     Loop
     rsLoopcnt = rsLoopcnt + 1
Loop
End Sub

I hope I remembered all the little ' and ".  I should have typed it in Access so it would tell me if I forgot!
Good luck.
Laurie

 

[PWise]

You don't need records sets you can use a update Statement

UPDATE [Item Table] As I
INNER JOIN [Case Code Table] As C
ON (CUBE Between cube_low AND cube_high )
AND (Weight  Between weight_low AND weight_high )
SET Code = Type

DIM STR As String
Str = "UPDATE [Item Table] As I INNER JOIN [Case Code Table] As C ON (CUBE Between cube_low AND cube_high ) AND (Weight  Between weight_low AND weight_high ) SET Code = Type"
currentdb.execute str