Validation rule for length of txtBox

[cheuschober]

Here's a bit of a pickle I've been trying to wrap my noodle around.

Anyone have an idea what route to pursue (unbound form, or afterupdate, etc?) to force a validation rule on the number of characters in a text box based on the value of combobox in the same form.

Example:
If cboBox1 = 840, then len(txtBox2) = 6
But if cboBox1 = 820, then len(txtBox2) = 3
And so forth?

Thanks,
~Chad

 

[andrew93]

Hi Chad
How about a small function like this in the 'AfterUpdate' property of both the combo box and text box on the form?

Private Sub cboBox1 _AfterUpdate()

On Error GoTo Err_cboBox1

If Me.cboBox1 = 840 And Len(Me.txtBox2) > 6 Then
    Me.txtBox2 = Left(Me.txtBox2, 3)
Else
    If Me.cboBox1 = 820 And Len(Me.txtBox2) > 3 Then
        Me.txtBox2 = Left(Me.txtBox2, 6)
    End If
End If

Err_cboBox1 :
    Exit Sub

End Sub

You can nest additional 'If' statements before the first 'End If'.

HTH, Andrew

 

[cheuschober]

Thanks for the reply Andrew.

I think I've decided to make this an unbound form. I'll use ADO to add the record to my table. (It's a lookup table anyway so it shouldn't see many, if any new entries) and before the recordset updates I'm going to throw some checks for valid lengths based on the first combobox.

Of course, this raises a new issue I've never run into before:

Is there a vba shortcut for multiple cases with the same result (IE, an equivalent to the IN() sql function). I have about 200 different cases that I need to assign to 5 different lengths.

Any thoughts anyone? Many thanks.
~Chad

 

[andrew93]

Hi Chad
The 'Case' statement comes to mind but I'm not sure if that is what you are after. Can you explain what you are trying to do (i.e. why the differing lengths) and why there are 200 different cases?
Andrew

 

[Mile-O]

There's a function in the Code Repository for limiting the text entered into a control - you should be able to adapt it to refer to a combobox for the limit length.

 

[cheuschober]

Thanks SJ--caught that bit earlier but I don't think I understood it enough to implement properly. Or, maybe it just couldn't do what I wanted.

The table I was working on was a postal codes table for international cross-compatibility (or at least as much as can be afforded for an average American's use). Using the ISO Country codes as my first field, I wanted to ensure that the exact (not just limited) number of characters appeared for the region I was drilling down to. In the case of the US, it would be 5 as all postal codes are 5 digit zip codes, but in the case of, say, Canada, I would only want to allow the 3 character FSA code, no more, and no less.

The Case... method took care of what I needed (didn't realize I could use comma separated cases! ) and an unbound form that checks the len() of the postal code field based on the value of the ISO Country code and returns a msgbox instructing the user to enter a valid length or executes an ADO recordset operation seems to have solved that issue. So cheers.

Of course if you wanted to take a look at another issue that's cropped up... We can start talking about supressing the notinlist response that's driving me batty...

~Chad