If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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:

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.