Solved Reference Combobox Column in VBA, breaks IF statement.

[Poco_90]

I am really stuck and can't find an answer for this and would appreciate some guidance. I have a field in a form txtQtyTray and if I have a numeric value in that field my VBA code below works.

If Me.txtQtyTray.Value = Me.txtCount.Value Then
MsgBox "Full " & Me.txtQtyTray & " is equal to " & Me.txtCount

ElseIf Me.txtCount >= Me.txtQtyTray Then
MsgBox "Too much " & Me.txtCount & " is greater than or equal to " & Me.txtQtyTray

ElseIf Me.txtQtyTray >= Me.txtCount Then
MsgBox "Not Enough " & Me.txtQtyTray & " is greater than or equal to " & Me.txtCount

Else
MsgBox "Something is Wrong"
End If

When I change the control source on the txtQtyTray field to =[cboPartNumber].[column](2) my code stops working and I get "Not Enough" for every permutation. When I output the value to the screen(like below), it is showing a numeric value, so cant figure out the issue.

MsgBox "Number of items in Tray " & Me.cboPartNumber.Column(2)

From what I am seeing online referencing the combobox in the VBA is what I should do but this doesn't work, i.e. I change txtQtyTray to Me.cboPartNumber.Column(2). Other post reference using .value in Me.cboPartNumber.Column(2) but that seems to break the code completely no matter where I put the .value into.

Thanks in advance.


P.S> My end code wont be showing messages like above, it will trigger something else, but I put the Msgbox in to try and troubleshoot.

 

[Minty]

What if you change your code to

If Me.cboPartNumber.Column(2) = Me.txtCount.Value Then
    MsgBox "Full " & Me.txtQtyTray & " is equal to " & Me.txtCount

Also, you need to remove the = from your other >= and <= checks, they make no sense as you have already checked for the equal value in the first statement.

 

[Josef P.]

Are you comparing text instead of numbers?
=> Make sure that only numbers are compared:

dim QtyTray as Double ' or Long?
dim Cnt as Double ' or Long?

QtyTray = Me.txtQtyTray.Value ' Nz(..) required?
Cnt = Me.txtCount.Value       ' Nz(..) required?

If QtyTray = cnt Then
   MsgBox "Full " & QtyTray & " is equal to " & Cnt
ElseIf Cnt > QtyTray Then
   MsgBox "Too much " & Cnt & " is greater than or equal to " & QtyTray
ElseIf QtyTray > Cnt Then  ' Or : Else only
   MsgBox "Not Enough " & QtyTray & " is greater than or equal to " & Cnt
End If

 

[The_Doc_Man]

if I have a numeric value in that field my VBA code below works.

When I change the control source on the txtQtyTray field to =[cboPartNumber].[column](2) my code stops working

You have a syntax error in that cboPartNumber.Column(2) is probably what you want. You must have warnings disabled, because the syntax you used should refer to something that doesn't exist and that should trigger an error message.

You might also consider this approach, because ElseIf works but too many of them in sequence become clunky.

SELECT CASE Me.txtQtyTray - Me.txtCount
    CASE 0
        MSGBOX .... whatever you say for equal
    CASE IS > 0
        MSGBOX ....whatever you say when txtQtyTray is greater than txtCount
    CASE IS < 0
        MSGBOX .... whatever you say when txtQtyTray is less than txtCount
    CASE ELSE
        MSG BOX .... whatever you say when the impossible happens
    END SELECT

 

[Poco_90]

Thanks for all the quick responses.

@Minty I had tried that but it didn't work.
@Josef P. Thank you your code, it works.
@The_Doc_Man You are correct warnings are disabled (I do an insert into table ). I like the idea of the case but it didn't work for me.

I think the reason that Mintys and The_Doc_Man suggestions didnt work is becase as Josef P suggested I must be comparing text insted of numbers some how.

Thanks again.

 

[Minty]

Turn back on the warnings whilst you are debugging.
Try forcing the issue using a CDec() around each of your comparison values or a CInt if they are only small integer values.

 

[Josef P.]

The return type of Combobox.Column(x) is usually/always (?) a string (embedded in Variant).

Simple test:

MsgBox VarType(Me.txtQtyTray.Value) & " | " & VarType(Me.cboPartNumber.Column(2)) & " vs " & VbVarType.vbString

 

[Poco_90]

Thanks for the additional information, good to know how to test for again.