VBA to Limit Textbox Length Based On Decision from ComboBox

[nick404]

I need help getting a textbox to respond to what the user selects in a combo box. So far the combo box "substrate" has two option (65185 and 65410) with 65185 being the default. If the selected substrate from the cmb is "65185" I would like to limit the following textbox "RollNumber" to a length of 11 characters. But if the substrate "65410" is selected from the cmb I would like to limit the txt length to 13 characters. Here is the code I currently have:



Private Sub txtRollNumber_Click()
If Me.cmbSubstrate = "65185" Then
Me.txtRollNumber.MaxLength = 11
ElseIf Me.cmbSubstrate = "65140" Then
Me.txtRollNumber.MaxLength = 13
End If
End Sub

I know .MaxLength is not recognized by access but I was unable to find anything similar. I am sure my syntax is incorrect, I have only just started getting into coding by hand so I do not know too much...any help is appreciated! Here is a screenshot of the user form: The combo is dropped down from the substrate field, and covers up the roll number text box, but it is right below it.

[CJ_London]

take a look at this link

http://allenbrowne.com/ser-34.html

[ranman256]

you could perform the check in the AFTERUPDATE event

sub txtRollNumber_afterupdate()
select case cmbSubstrate 

 case  "65185" 
     if len(txtRollNumber) <> 11 then msgbox "Length must = 11"

  case  "65140" 
     if len(txtRollNumber) <> 13 then msgbox "Length must = 13"
End select

end sub

if you have more that just the 2 codes, in the combo query , you could add the max len setting as a 2nd column, then THAT would have the max length to check
if len(txtRollNumber) <> cmbSubstrate.column(2) then msgbox "Length must = " & cmbSubstrate.column(2)

[nick404]

Great! Worked like a charm. Now, can I put in a .Requery somewhere (maybe the after update for the cmbSubstrate) so that if they type in the incorrect amount into the text box but then switch to the other substrate in the combo box that it won't just allow them to proceed without verifying the correct character length is in the textbox?

Or in other words, I need a way to stop the user from simply proceeding to the next field if the number of characters is not valid. The message appears but still allows them to proceed.

[CJ_London]

you could put this code in the control change event and building on ranmans suggestion

if len(txtRollNumber.text)> cmbSubstrate.column(2) then 
    msgbox "max characters reached"
    txtRollNumber.text=left(txtRollNumber.text,len(txtRollNumber.text)-1)
end if

if the user changes the value in cmbSubstrate then presumably the value in txtRollNumber is completely wrong, so suggest in the afterupdate event of cmbSubstrate would be

txtRollNumber=null

[nick404]

Perfect guys, thank you both a lot!