Type Mismatch error when calculating value based on comboboxes and populating textbox

[jbeets]

Hello,
I have a form which has several comboboxes with the dropdown options of "N/A, 4,3,2,1". I have a text box (txtScore) which should populate when the user presses the btnCalculate. I am getting a Type Mismatch error however and I cannot figure out what the issue is.

In the table that the form is connected to the fields with the comboboxes are formatted as Short Text but I have also tried Number. The txtScore field is formatted as Number.

The code I am using I currently use in an Excel Userform and it works there. So maybe everything does not transfer over exactly in Access? This is the piece that highlights with the Type Mismatch error:

control_value = control_value + CInt(target_control.Value)

Any help would be greatly appreciated!

Private Sub btnCalculate_Click()
Dim target_control As Control
Dim control_value As Integer
Dim control_count As Integer
control_value = 0
control_count = 0

For Each target_control In Me.Controls
    If TypeName(target_control) = "ComboBox" Then
        If Not target_control.Value = "N/A" Then
            control_value = control_value + CInt(target_control.Value)
            control_count = control_count + 1
        End If
    End If
Next
txtPotential = control_count * 4
txtScore = Format((control_value / txtPotential), "Percent")

For Each c In Me.Controls
    If TypeName(target_control) = "ComboBox" Then
        If target_control.Value = "" Then na = MsgBox("All fields must be selected. Please choose 1-4 or N/A before continuing.", vbOKOnly, "Quarterly Audits")
    End If
Next
End Sub

[Bulzie]

Looks like it is not recognizing the "N/A" and dropping to that calculation. Maybe try for the line about it:

If target_control <> "N/A" then

You could put a breakpoint on that line and mouse over the fields to see what it is showing.

[pbaldy]

Hit debug when you get the error and hover over

target_control.Value

to see what its value is. If it's text or Null you'll get that error.

[pbaldy]

Geez, talk about slow typing.

[davegri]

Just to make sure something is in the combobox:

 If Not target_control.Value = "N/A" Or Not IsNull(target_control.Value) Then

[jbeets]

Hey Bulzie - Thank you for your quick response! I found the issue but am not 100% sure what I need to add in to fix it....On this form I have comboboxes that should not be apart of the calculation and are not numeric values. cboStatus and cboEmployee. How can I add into the code to ignore those comboboxes?

Thank you again for helping me!

[Bulzie]

Would think maybe there is an option like target_control.Name that will tell you the name of the control so you can exclude the combo boxes you don't want.

If target_control.name <> "cboStatus" and target_control.name <> "cboEmployee" then

Code....

end if

[pbaldy]

FYI, issue was resolved here:

https://www.accessforums.net/showthread.php?t=79791