Comparing combo box value.

[abzalali]

Dear All,
I have form where some or more field exist. I want to compare two field as :

1. cboBatchID As Combo box
2. txtBillNum As Text Box

Private Sub cboBatchID_AfterUpdate()
If Me.cboBatchID.Column(4) <= 0 Then
Me.txtBillNum = 1
Else
Me.txtBillNum = CLng(Me.cboBatchID.Column(4)) + 1
End If
End Sub

I mean, if cboBatchID.Column(4) <=0 then txtBillNum start from 1 automatically or cboBatchID.Column(4) >=0 then txtBillNum = cboBatchID.Column(4)+1

I'm already trying with the code above. But does not work. Generate run time error.

Please help me.

 

[MarkK]

But does not work. Generate run time error.

We need to know what error.

 

[abzalali]

Dear Sir,

Run-time error '13'

 

[MarkK]

Ok, so you are getting a Type Mismatch error at what line?

 

[missinglinq]

What is the Datatype of the Field that txtBillNum is Bound to?

Linq ;0)>

 

[abzalali]

Dear Sir,
I'm getting error with the following line:

Me.txtBillNum = CLng(Me.cboBatchID.Column(4)) + 1

And txtBillNum is unbound.

 

[MarkK]

Then most likely the data at Me.cboBatchID.Column(4) cannot be converted by CLng(). What happens in the immediate pane if you do this . . .

Private Sub cboBatchID_AfterUpdate()
   debug.print Me.cboBatchID.Column(4)
[COLOR="Green"]'   If Me.cboBatchID.Column(4) <= 0 Then
'      Me.txtBillNum = 1
'   Else
'      Me.txtBillNum = CLng(Me.cboBatchID.Column(4)) + 1
'   End If
[/COLOR]End Sub

In other words, what is the actual value of Me.cboBatchID.Column(4)?

 

[abzalali]

Dear Sir,
Actually the value of Me.cboBatchID.Column(4) is Integer Number

When I select a BatchID from Me.cboBatchID then BillNum is print on Immediate Window if Me.cboBatchID.Column(4) exist a number.

on the other hand where Me.cboBatchID.Column(4) is null then Immediate Window show nothing.

But I want where Me.cboBatchID.Column(4) is null txtBillNum start from 1.

Thank you Sir for the best cooperation.

 

[MarkK]

If you get a type mismatch error with this code . . .

Me.txtBillNum = CLng(Me.cboBatchID.Column(4)) + 1

. . . and txtBillNum is an unbound textbox then the only thing that can fail is the CLng() function, and it will only fail if you pass it a non-numeric, non-null value. If you pass it a null you would get error 94, invalid use of null error.

You realize that the first column in the combo is cboBatchID.Column(0), right? So .Column(4) is the 5th column.

 

[abzalali]

Dear Sir,

But When Me.cboBatchID.Column(4) is not null, Immediate window show that value in column(4)

and when Me.cboBatchID.Column(4) is null then Immediate Window show nothing. showing run-time error'13'

 

[MarkK]

I don't know how to help further. I don't understand why you are getting that problem. The situation you describe, to my knowledge, should not yield that result, so I don't know what else to suggest.

 

[Mihail]

Let's increase a little bit the debug starting from what lagbolt suggested.

Private Sub cboBatchID_AfterUpdate()
  Me.txtBillNum = 1 'Ensure that is no problem with the textbox
Dim i As Long
  For i=0 to 4
    Debug.Print i & ")  " & Me.cboBatchID.Column(i)
  Next i

 '   If Me.cboBatchID.Column(4) <= 0 Then
'      Me.txtBillNum = 1
'   Else
'      Me.txtBillNum = CLng(Me.cboBatchID.Column(4)) + 1
'   End If
 End Sub

Copy what you see in the Immediate window then paste in a new post

 

[abzalali]

Dear Sir,
I see in the Immediate window after paste that code:

0) IC-CF/BASE-01M/R18/01
1) 140
2)
3) 112
4) 2

 

[Mihail]

Seems like CLng function itself is the problem not the argument.
I'll give you an "work around" to avoid using this function:

Private Sub cboBatchID_AfterUpdate()
Dim n As Long
   n = Me.cboBatchID.Column(4) 'The value is passed to a variable with [B]Long [/B]as type
   If n <= 0 Then
      Me.txtBillNum = 1
   Else
     Me.txtBillNum = n + 1
   End If
End Sub

Inform us if this is an working code.

 

[abzalali]

Dear Sir,

Where me.cboBatchID.column(4) is null then show it show run-time error '13' with this line :

n = Me.cboBatchID.Column(4) 'The value is passed to a variable with Long as

Where me.cboBatchID.column(4) is not null then it work fine.

 

[Mihail]

A little bit on Google and I find this page.
CLng do not accept Null as argument.
Any way the error message must be Run-time error 94, Invalid use of Null, not 13, so I don't change my mind: the CLng function is the problem.

For now replace in your code this line

n = Me.cboBatchID.Column(4)

with this one:

n = NZ(Me.cboBatchID.Column(4),0)

Now will do the job, but another question is raised:
If Me.cboBatchID.Column(4) = Null , n will be 0 (ZERO)
Is this acceptable for you ?

And now let's hunt the reason that CLng don't work. Ok ?
I assume that there are more other functions that don't work if this one refuse to do the job.

 

[way2bord]

Check the table.field that combobox.columns(4) is based on. I'm guessing it's a text field containing some values which are numbers and some values which are strings (have letters).

This would explain your error 13. It will work for some of the time, until it hits a string, which would -> error 13.

 

[Mihail]

Indeed w2b. It's possible.