How to link same fields from two different recordsets in VBA Access

[Jawad77]

I have two recordsets. Each one of them has the fields: "Product" and "Location" and some other fields shown below.

recordset 1:
Product Location LW WK2 WK3 WK4
1 10
2 20


recordset 2:
Product Location LW WK2 WK3 WK4
1 10
2 30



I like to perform some calculations which involves fields from both recordsets. But I need to link the "product" and "location" fields from recordset1 to the "product" and "location" fields from recordset2.


I can do my calculations in VBA using the Fields.Value property but I don't know how to link these two recordets before I can start my calculations. Once I have my calculations done, then I can simple copy them to another recordset3 (which I have figured out how to do). But I am still struggling with how to link my recordsets 1 and 2.

Any ideas/help will be highly appreciated. Thanks!

Jay

 

[PHV]

Wich sort of calculation ?
You can't do them in SQL ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jawad77]

This is the code I am using but I don't know how to link record sets 1 and 2 in the code with the same fields "product" and "location"

Thanks!

Public Function Comp()

Dim ObjMyDB As Database
Dim r1 As Recordset
Dim r2 As Recordset
Dim r3 As Recordset

Set ObjMyDB = DBEngine.Workspaces(0).Databases(0)


Set r1 = ObjMyDB.OpenRecordset("tbl_OAInb", DB_OPEN_DYNASET)
Set r2 = ObjMyDB.OpenRecordset("tbl_OAInv", DB_OPEN_DYNASET)

Set r3 = ObjMyDB.OpenRecordset("tbl_CalculatedOA", DB_OPEN_DYNASET)


Dim i As Integer
Dim J As Integer

i = r1.Fields.Count - 1


r1.MoveFirst




Do Until r1.EOF

r3.AddNew


For J = 0 To 1
r3.Fields(J).Value = r1.Fields(J).Value
Next J

For J = 2 To i

If i = 1 Then
Exit For
ElseIf J = i Then
r3.Fields(J).Value = r1.Fields(J).Value - r2.Fields(J).Value + 0
Else
r3.Fields(J).Value = r1.Fields(J).Value - r2.Fields(J).Value + r2.Fields(J + 1).Value
End If


Next J

r3.Update


r1.MoveNext

Loop




r1.Close
r2.Close
r3.Close

Set r1 = Nothing
Set r2 = Nothing
Set r3 = Nothing


End Function

 

[Jawad77]

I can do them in SQL but it would be very labor intensive/manual.

I could get the calculation done in a sec through VBA code if I could figure out how to link my recordsets. Do you know a way I could do that in VBA ?

Jay

 

[PHV]

Have tbl_OAInb and tbl_OAInv always the same number of columns ? the same collection of (Product,Location) pairs ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jawad77]

They have the same number of columns. Ideally they should have the same collection of (product,location) but it's not necessarily the case. That's why i thought that I should link my recordsets first so that I compare apples to apples when I perform my calculations.

 

[PHV]

And what should happen with the (product,location) only in tbl_OAInb and those only in tbl_OAInv ?
They have the same number of columns
A predetermined or fixed number of columns ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jawad77]

What do you think of this:

r1.MoveFirst

r2.FindFirst (criterion where product location are equal to product location of r1)

Should it work?

 

[Jawad77]

They both have predetermined fixed number of columns. I am not concerned about those records that are not in recordset 1 but are in recordeset 2.

 

[PHV]

Yes it would work except for tbl_OAInv pairs not in tbl_OAInb.
To get a full populated recordset you have to deal with an union of outer joins like this;
SELECT B.Product,B.Location,B.LW AS BLW,B.WK2 AS BWK2,B.WK3 AS BWK3,WK4 AS BWK4,V.LW AS VLW,V.WK2 AS VWK2,V.WK3 AS VWK3,V.WK4 AS VWK4
FROM tbl_OAInb B LEFT JOIN tbl_OAInv V ON B.Product=V.Product AND B.Location=V.Location
UNION ALL SELECT V.Product,V.Location,0,0,0,0,V.LW,V.WK2,V.WK3,V.WK4
FROM tbl_OAInb B RIGHT JOIN tbl_OAInv V ON B.Product=V.Product AND B.Location=V.Location
WHERE B.Product Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jawad77]

Thanks a lot PH. I will work this way!