Type mismatch error at certain iterations of a for loop... (1 Viewer)

[maysey]

Hi everyone, I am currently creating forms for a database where one function that adds up payment values and makes a label equal to the total

heres how it works to get up to this point:
1. The user enters a year from a drop down menu on a screen
2. after clicking the button below the button calls a function which opens up the total screen and populates a listbox on that screen with the valid DAta. This Works.
3. the same button calls a second function after the first is done, this adds up the total, here is the code:

Public Function YearCalc()
'this function is on the same button as payment vars, which has a if statement that closes and exits the function if the listbox is empty, therefore this if statement checks if this has
'happened, and exits YearCalc
If Forms!frmyearlytotal.Visible = False Then
    DoCmd.Close
    Exit Function
End If
Dim Total As Currency
Dim CtrlLst As Control
'VarRow is the variable that will be used to identify each row of the listbox
Dim VarRow As Long
'CurrentValue will hold each value in the loop that is about to be calculated inside a textbox
Dim CtrlCurrentValue As Control
'assigns currentvalue to a textbox hidden behind yearlytotal's listbox
Set CtrlCurrentValue = Forms!frmyearlytotal!txtvalue
 
'sets newly dimmed total variable and VarRow variable to zero ready for calculation
'and sets CtrlList to yearly totals listbox to make referring to it easier as I am referring to it multiple times
Total = 0
Set CtrlLst = Forms!frmyearlytotal!lstcalculations
'this makes varrow go on until the maximum listcount of the listbox is reached
For VarRow = 0 To CtrlLst.ListCount
    'a column value can only be assigned to a object, doing otherwise gets a object required error
    CtrlCurrentValue.ControlSource = Trim(" " & CtrlLst.Column(0, VarRow) & " ")
    Total = Total + CtrlCurrentValue.ControlSource
 
   On Error GoTo Err_Here
 
 
 
 
Next VarRow
 
 
Err_Here:
MsgBox "it happens here: " & VarRow & Err.Description
Forms!frmyearlytotal!lblvalue.Caption ="£" & Total

4. strangely enough, the for loop WORKS, it takes the data in the row of the lstbox defined by VarRow and Total adds it together just fine.

After sitting through each iteration using msgboxes in the code to show the values, I found it displayed the error after a certain amount of iterations which varyed depending on the year selected (i.e the values in the listbox)

for example, after putting in that error handling you see above I get, "it happens here: 45 Type mismatch" if I use payments from 2004. At which point it highlights the Total + the textbox value part. Additionally, before I put the trim function in, it would highlight " CtrlCurrentValue.ControlSource = CtrlLst.Column(0, VarRow)" instead

so yeah, the problem is "wwhy does it randomly slap a type mismatch in my face after it got to a certain iteration?"

major thank you in advance to the person who answers this thread, and not to be demanding or sound like an ass but I need a response ASAP thank you! Ignore most of the comments, they are for someone else I am giving this to, but they will tell you what I had intended to do.

 

[tehNellie]

Type Mismatch generally means you're trying to compare two values of an incompatible type.

you're iterating one row too many and trying to add NULL to your total.

For VarRow = 0 To CtrlLst.ListCount - 1

When you're getting problems like this, turn off your error handling so you can see what is going on.

a debug.print on CtrlCurrentValue.ControlSource when it breaks shows you exactly what the problem is.

 

[maysey]

Type Mismatch generally means you're trying to compare two values of an incompatible type.

you're iterating one row too many and trying to add NULL to your total.

For VarRow = 0 To CtrlLst.ListCount - 1

When you're getting problems like this, turn off your error handling so you can see what is going on.

YEEEEEES it worked, thanks so much

interestingly, the -1 thing didnt occur to me because I had a dry run where them loop crashed at 45, I manually counted the listbox and got 47

I dont do math... XD

thanks so much, it now works a treat!

a debug.print on CtrlCurrentValue.ControlSource when it breaks shows you exactly what the problem is.

For future reference where would I add Debug, Print to make it work? I tried adding one in various places before adding the -1 but to no success

 

[vbaInet]

For future reference where would I add Debug, Print to make it work? I tried adding one in various places before adding the -1 but to no success

About the Immediate Window:

http://www.learnaccessnow.com/chap20d.html

 

[tehNellie]

Ironically, your comment for that line acknowledges the issue (it's not an issue per sé, the count just begins at 0 rather than 1).

AS vbainet says you can put debug.print ..... in the immediate window while you're stepping through code or while you're writing it for that matter, which can be handy at times to test simple calculations etc.

You can also add it to your code while you're testing it.

ie

sub something
dim i as long

  For i = 1 to 10
    debug.print i^i
  next

End sub