aligning the corresponding rows in a recordSet

Question

Hi, I ve two recordsets which have the same primary key: combination of two columns called 'item_no' and 'Order_id'..... Both recordsets have this primary key(combination) and rest other different columns...

Now, I want to order the(those) rows in two recordsets such that the position of the records(with same primary key values) should be same...

E.g consider rset1 and rset2... having the above columns as primary key combination and row5 in rset1 has values for 'item_no' and 'order_id' as 2 300 and row 8 in rset2 has values for the above as 2 300 (which is same)....

1) Now i want to bring row5 (in rset1) to position 8 sucht that both records are same at position in the recordsets

2) if no common records found in both rsets... then i'll leave it blnk at tht position(in rset1) for corresponding row (in rset2) [meaning tht for a record in rset2 ther is no correspondin record existin in rset1]

It's always made sure tht no: of records in rset2 > tht in rset1...

Can u help how to go with the alignment of records in recordset in VB6.0 as i wod be binding these recordsets to Excel for displaying side by side??

Any in built method(to do alignment or anythin) in VB6.0??

Answer 1

There is no inbuild method in VB6 to do this (not that I know of), however, it's easy enough to do yourself.

What you need to do is create two false list of records, once you have them, you can spit them out how ever you want (to a csv file, to excel, to ms access). CAVEAT: The recordSets must be sorted by the primary key to work.

This code steps through both recordsets in order, creating ordered lists with either blanks or links to a record (While it looks very like VB code, consider this to be pseudo code, the logic is correct, it will not compile)

Dim rs1 As Recordset
Dim rs2 As Recordset

Dim rs1List As Collection
Dim rs2List As Collection

REM code here to initialise the collections to new Collection and fill the record sets

Do While Not rs1.EOF And Not rs2.EOF
    If rs1("PKey") = rs2("PKey") Then
        rs1List.Add rs1.Bookmark
        rs2List.Add rs2.Bookmark
        rs1.Movenext
        rs2.Movenext
    ElseIf rs1("PKey") < rs2("PKey") Then
        rs1List.Add rs1.Bookmark
        rs2List.Add Nothing
        rs1.Movenext
    ElseIf rs1("PKey") > rs2("PKey") Then
        rs1List.Add Nothing
        rs2List.Add rs2.Bookmark
        rs2.Movenext
    End If
Loop
Do While Not rs2.EOF
    rs1List.Add Nothing
    rs2List.Add rs2.Bookmark
    rs2.Movenext
Loop
Do While Not rs1.EOF
    rs1List.Add rs1.Bookmark
    rs2List.Add Nothing 
    rs1.Movenext
Loop

Assumes the primary key field is PKey, and that .Bookmark is a method you can use to go directly to that record (ordinal position might do if available).

Hope this helps

Edit

Just changed some bits in the last two loops, they weren't quite right.