matching up records from 2 recordsets but not in order

[secondangel]

Hi there

I have table1 - rs1 containing records(33,000). I want to loop through each record. Then i have to find the matching record from table2 - rs2 (also about 33,000). So starting at rs1 - position 1 check and see if rs2 - position 1 matches. If it matches then copy a field from rs2 into rs1. set found as true.
If not movenext until you get to the end of rs2. if still no match at the end then back out and return a false match. (becuase there are about 100 which dont match.) either way if found or not then move to rs1 next and start back at beginning of rs2 again

My code below works but doesnt as it does not return the correct field value. I.e it just keeps the field it found when it first found a match. Can anyone help or think of a better design code.

Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset



Set db = CurrentDb()
Set rst1 = db.OpenRecordset("WITHNEWCOST")
Set rst2 = db.OpenRecordset("WITHORIGINALCOST")




Dim checka As String
Dim checkb As String
Dim ccost As Double

Dim found As Integer


' OPEN original cost, take the first 3 letters and the qty as the check
' open the new cost, take the first 3 letters and the qty  - check if it macthes above. if so - copy the original cost price over and write it into the new table.
' if not - write a value of 0.001 - to show its unknown - or if it allows leave as a blank NULL value


'RST1 IS THE ENWLY WRITTEN FILE

 If rst2.BOF And rst2.EOF Then
    MsgBox "table is empty", vbCritical
  Else
    rst1.MoveFirst
    rst2.MoveFirst
  
    ' start at the top record
    
    
    
    checkb = Left(rst2.Fields(0), 4) & "" & Str(rst2.Fields(2))   ' original cost table
    
    
    Do Until rst1.EOF
    
    
    
    
        found = 0
        checka = Left(rst1.Fields(1), 4) & "" & Str(rst1.Fields(4))   ' new cost table
        checkb = Left(rst2.Fields(0), 4) & "" & Str(rst2.Fields(2))   ' original cost table
        
        
        
     Do Until found = 1 Or rst2.EOF
               
         
         If checka = checkb Then ' do records match
                  
         found = 1
         ccost = rst2.Fields("COST")
                  'else
                  ' move to next rs2 record
                  
         Else
              
                      
               rst2.MoveFirst
               
               Do Until rst2.EOF
                checkb = Left(rst2.Fields(0), 4) & "" & Str(rst2.Fields(2))   ' original cost read the line in rs2
                
                If checka = checkb Then  ' so we have rs1 record and we are checking every rs2 record for a match
                 found = 1
                 ccost = rst2.Fields("COST")
                Else
                 found = 0
                 ccost = 0
                End If
               rst2.MoveNext
               Loop
         End If
      
     ' either we have a match or eof.
        
     Loop
        
          rst1.Edit
          rst1.Fields(6) = ccost
          rst1.Update
        
          checka = ""
          checkb = ""
   
   
   
   
        rst1.MoveNext
        
       
       
       
       
    
    
     Loop
  End If
  
  
  
  MsgBox "all new records updated and matched", vbInformation

thanks a lot

 

[RuralGuy]

I would use Dynasets rather than tables. Why aren't you using .FindFirst instead of walking the recordset?

 

[secondangel]

I would use Dynasets rather than tables. Why aren't you using .FindFirst instead of walking the recordset?

becuase im not that great at coding. :-(

didnt realise .findfirst would work.

ill have a look at how to use this

 

[RuralGuy]

Post back to this thread if you need more help with this issue. It will run a lot faster as well.

 

[secondangel]

Dim checka As String


'try this method

rst2.MoveFirst
rst1.MoveFirst

Do Until rst1.EOF


 checka = Left(rst1.Fields(1), 4) & "" & Str(rst1.Fields(4))
[B] rst2.FindFirst (checka)[/B]
 
 If rst2.NoMatch Then
 ccost = o
 Else

 ccost = rst2.Fields("COST")

 rst1.Edit
 rst1.Fields(6) = ccost
 rst1.Update
 
 End If
 rst1.MoveNext
 
Loop


MsgBox "all records checked", vbOKOnly

unfortuneatly this doesnt like my checka

says operation is not supported

any ideas

 

[RuralGuy]

I'm working on a solution right now. Will post in a bit.

 

[RuralGuy]

This may not even work but here's what I came up with:

Public Sub Foo()

   Dim db As DAO.Database
   Dim RsNew As DAO.Recordset
   Dim RsOld As DAO.Recordset

   Set db = CurrentDb()
   Set RsNew = db.OpenRecordset("WITHNEWCOST", dbOpenDynaset)
   Set RsOld = db.OpenRecordset("WITHORIGINALCOST", dbOpenDynaset)

   Dim ckNew As String
   Dim ccost As Double

   ' OPEN original cost, take the first 3 letters and the qty as the check
   ' open the new cost, take the first 3 letters and the qty  - check if it macthes above. if so - copy the original cost price over and write it into the new table.
   ' if not - write a value of 0.001 - to show its unknown - or if it allows leave as a blank NULL value

   If RsOld.RecordCount = 0 Or RsOld.RecordCount = 0 Then
      MsgBox "Table is empty, nothing to do!", vbCritical
   Else
      Do Until RsNew.EOF
         '-- Create the record signature for the find
         ckNew = left(RsNew.Fields(1), 4) & Str(RsNew.Fields(4))
         RsOld.FindFirst left(RsOld.Fields(0), 4) & Str(RsOld.Fields(2)) = ckNew
         If Not RsOld.NoMatch Then
            ccost = RsOld.Fields("COST")
         Else
            ccost = 0
         End If
         RsNew.Edit
         RsNew.Fields(6) = ccost
         RsNew.Update
         RsNew.MoveNext
      Loop
   End If
   MsgBox "all new records updated and matched", vbInformation
   
   On Error Resume Next
   RsNew.Close
   RsOld.Close
   Set db = Nothing
   Set RsNew = Nothing
   Set RsOld = Nothing

End Sub

 

[RuralGuy]

BTW, if that does not work (and I don't think it will) we'll just use a query of the table and create a new field to search against. It looks like we only need a few fields from each table so we'll need their names to create the query.

 

[gemma-the-husky]

why not just have a number of queries

1. to match corresponding items
2. to find items in table1, not in table2
3. to find items in table2, not in table1