Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21

    Unhappy Unanswered: No Current Record

    I want to compare each account number in tbl_CardNumbers to the Ranges in tbl_BinRange. If an account equals or falls between a bin range, mark CommCard true in tbl_CardNumbers.

    I want to grab the first number in the CardNumbers table and compare it to each bin range in the BinRange table. If it is a match, update the CommCard value to true in the record. When it is done comparing the first account number, move to the second account number and compare it to the bin ranges, if there is a match, update the CommCard value to true...and so on until there are no more card numbers to compare.

    When I run the following code, I recieve this message: Run-time error '3021': No Current Record.

    The If statement is highlighted and Val(RS1("BinRange1").Value) & Val(RS1("BinRange2").Value) show no current record.

    Private Sub cmdBinRange_Click()
    On Error GoTo Err_cmdBinRange_Click


    Dim dbase As Database
    Dim RS As Recordset, RS1 As Recordset

    Set dbase = CurrentDb()
    Set RS = dbase.OpenRecordset("tbl_CardNumbers", dbOpenDynaset)
    Set RS1 = dbase.OpenRecordset("tbl_BinRange", dbOpenDynaset)

    RS.MoveLast
    RS.MoveFirst
    RS1.MoveLast
    RS1.MoveFirst

    Do While Not RS.EOF

    If Val(RS("CardNumbers").Value) >= Val(RS1("BinRange1").Value) And Val(RS("CardNumbers").Value) <= Val(RS1("BinRange2").Value) Then
    RS.Edit
    RS("CommCard").Value = True
    RS.Update
    RS.MoveNext
    Else
    RS1.MoveNext
    End If

    Loop

    RS.Close
    RS1.Close

    Exit_cmdBinRange_Click:
    Exit Sub
    Err_cmdBinRange_Click:
    MsgBox Err.Description
    Resume Exit_cmdBinRange_Click

    End Sub

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi

    Consider the case where your cardnumber doesn’t belong to any range. The loop will continue to loop and on each loop you will movenext for each record in your BinRange table. And when it gets beyond the last record it will crash on the next loop run. The reason is you have checked for eof in the CardNumbers table but not in the BinRange table.

    By the way, this seems a bit of an effort to do what can be done in a query:

    Code:
    UPDATE BinRange, CardNumbers SET CardNumbers.CommCard = True
    WHERE (CardNumbers.CardNumbers)>=[BinRange1] And (CardNumbers.CardNumbers)<=[BinRange2];
    Note there is no join defined so the above query basically considers every combination of CardNumber record and BinRange record. This is called a Cartesian join. Then applying the filter it is able to update the records only where the criteria is true i.e. records where the CardNumber is between the bin ranges.

    Hth
    Chris

  3. #3
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21
    Chris,

    The query worked. I do seem to take the long way in trying to get to an answer. I guess that's why I find myself seeking the help of those more experienced. Thanks for you help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •