Access VBA loop through Recordset with If/Else

[noisepoet]

The code below is a combo box Event Procedure. When I select a fund number from the combo box, the vba compares the code against a table containing a series of fund categories that exist within different ranges of numbers and displays the corresponding fund category in a separate text box (e.g. Fund Category A has a numeric range of 100-200, so if I select fund number 123 on the combo box, my fund category text box should display "Fund Category A").

The "Else" statement below is meant to display "Not Assigned" in the fund category text box if a fund number is selected in the combo box that does not exist within any of the fund category numeric ranges. Unfortunately, it is overriding the preceding "If" logic, and "Not Assigned" is displaying as the category for every selection, regardless of whether or not it exists within an established numeric range. What am I doing wrong?

  ' txtCategory based on cboFund_Number
  Dim fundCategoryMap As DAO.Recordset
  Set fundCategoryMap = CurrentDb.OpenRecordset("tblFund_Category_Map")
   
  fundCategoryMap.MoveFirst
  Do While Not fundCategoryMap.EOF
    If (Me.cboFundNumber.Value >= fundCategoryMap.Fields("FromRange")) And _
      (Me.cboFundNumber.Value <= fundCategoryMap.Fields("ToRange")) Then
        Me.txtCategory.Value = fundCategoryMap.Fields("Category")
    Else
      Me.txtCategory.Value = "Not Assigned"
    End If
    fundCategorytMap.MoveNext
  Loop

 

[Micron]

Unfortunately, it is overriding the preceding "If" logic,

Maybe not; maybe the first IF never executes. Put a break point at the start of the code and use the combo as before. Code will stop at the break point. Step through with F8 and watch what happens. Perhaps you are referring to the wrong combo column (it is zero based) so every record gets changed.

EDIT - forgot to mention that after a line executes you can mouse over the variables and references to see what they evaluate to. Or you can open a watch or locals window.

 

[RoryA]

You are processing every record regardless. You'd need an Exit Do after assigning the category. I suspect it would be more efficient to filter your recordset rather than looping though.

 

[noisepoet]

.

 

[welshgasman]

I am with RorayA on this, you need to exit the loop when your record is found?
It makes sense that the next record and any following are going to be outside your range?
If you put a Debug.Print of the any field that identifies the record, that would be obvious?
Even a Debug.Print in the Else part would show that?

 

[noisepoet]

Maybe not; maybe the first IF never executes. Put a break point at the start of the code and use the combo as before. Code will stop at the break point. Step through with F8 and watch what happens. Perhaps you are referring to the wrong combo column (it is zero based) so every record gets changed.

EDIT - forgot to mention that after a line executes you can mouse over the variables and references to see what they evaluate to. Or you can open a watch or locals window.

When I comment out the Else line, the code works.

 

[noisepoet]

You are processing every record regardless. You'd need an Exit Do after assigning the category. I suspect it would be more efficient to filter your recordset rather than looping though.

Exit Do does the trick. Thank you so much for your help.

 

[welshgasman]

When I comment out the Else line, the code works.

Yes, because no other record would fit the criteria, and you have already set it from the correct record?

 

[noisepoet]

Yes, because no other record would fit the criteria, and you have already set it from the correct record?

What I meant to say is that when I comment out the Else statement and choose among fund numbers that are valid (exist on the Category table), it works.

 

[welshgasman]

Put a debug.print "Not Assigned" in place of your Not Assigned setting of the control, leave the else in and run it. Then inspect the immediate window.