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:
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.
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:
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.
Last edited: