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)
If Val(RS("CardNumbers").Value) >= Val(RS1("BinRange1").Value) And Val(RS("CardNumbers").Value) <= Val(RS1("BinRange2").Value) Then
RS("CommCard").Value = True
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:
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.