Combo Box Validation

[heprox]

I have an application that connects to a DSN and generates a report based on a combobox for "Item Number" and a set of list controls for date range. The combobox is populated by a recordset:

Private Sub PopulateItemNumbers()
On Error GoTo errhandler
Set rs = New ADODB.Recordset
rs.Open "select distinct ITM_CD from GM_ITM ", cn, adOpenForwardOnly, adLockOptimistic
cboItemNo.Clear
Do While Not rs.EOF
cboItemNo.AddItem rs(0)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Exit Sub
errhandler:
lblProcessing.Caption = ""
MsgBox "Error occured while populating Item Codes List control" & vbCrLf & _
Err.Number & ":" & Err.Description, vbCritical
If rs.State = adStateOpen Then
rs.Close
End If
Exit Sub
End Sub


...the combobox has "CausesValidation" set to true, I'm using a routine to validate that the box is not left empty called cboItemNo_Validate that looks like:

Private Sub cboItemNo_Validate(Cancel As Boolean)
If cboItemNo.Text <> "" Then
Cancel = ValidateCombo(cboItemNo)
If Cancel = True Then
MsgBox "Invalid Selection, Select Item from the List"
cboItemNo.SetFocus
Exit Sub
End If
Else
MsgBox "Item Number cannot be Empty"
Cancel = True
cboItemNo.SetFocus
Exit Sub
End If
End Sub

...even when I enter a valid entry the combobox gives me the message box,

"Invalid Selection, Select Item from the List"

...the strange thing is that this application worked for years and then all of the sudden stopped working? Is there some limit on the number of records that can be brought up in a combobox? We currently have 30K+.... Inside the form, whenever I go to select another control to genrerate the report the combobox attempts to validate, and I get the message?

 

[jebenson]

What's the code in the ValidateCombo function?

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[heprox]

Oops sorry, I forgot that one,

Public Function ValidateCombo(ctrl As Control) As Boolean
Dim inti As Integer
For inti = 0 To ctrl.ListCount - 1
If ctrl.Text = ctrl.List(inti) Then
ValidateCombo = False
Exit Function
End If
Next
ValidateCombo = True
End Function

...I did a little research and found that you can only have 32K records in a combobox in VB6, so that is the problem. I decide to just replace the combobox with a simple textbox instead, forget about listing all of those records, and try to figure out how to validate a textbox for only allow nine numeric digits, no more and no less (i.e. 000000898)?

 

[jebenson]

Well, to check for only numeric entries, use the IsNumeric function in the Change event:

Private Sub Text1_Change()
If Not IsNumeric(Text1.Text) Then
MsgBox "Only enter numeric values for Item Number."
Text1.SelStart=0
Text1.SelLength = Len(Text1.Text)
End If

If Len(Text1.Text) < 9 Then
'code to disable search function
ElseIf Len(Text1.Text) = 9 Then
'code to enable search function
End If
End Sub

Set the text box's MaxLength property to 9. Note that I also included some code to test if the input value is less than 9 characters.

Hope this helps.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[heprox]

Interesting, when I place a maxlength = 9 on the textbox it only lets me enter 8 digits in the form at runtime? Also when I change it to maxlength = 10, I can enter all 9 digits however I always get an error about no records found even though I know the Item Number is good?

 

[jebenson]

Hmmm...that is strange. Are you sure that the text box is completely empty when you start typing - no spaces which would take up one of your characters? Because I set a textbox to MaxLength=9 and I can enter 9 characters.

Also, when you check that the entry is a valid Item Number, try doing a Trim on the value from the text box to remove any leading or trailing spaces:

If Trim(Text1.Text) = ItemNum

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson