Restrict characters in a control using VBA

[ascaife]

Hi All,

Need to restrict a control as follows:

Max number of chars = 5
Allowed chars = any combination of "QSTURV" or the letter Z on its own

Strange one I know but it's for a third party system.

Cheers,

Andy

 

[HiTechCoach]

Here is something to get you started.

I used two deferent events of a txt box named: Text7

Private Sub Text7_Change()


    If Len(Me.Text7.Text & "") > 5 Then

        Me.Text7 = Left(Me.Text7.Text, 5)

        Me.Text7.SelStart = 5

    End If

End Sub

Private Sub Text7_KeyPress(KeyAscii As Integer)

' "QSTURV" or the letter Z

Select Case KeyAscii
  Case Is = 81, 82, 83, 84, 85, 86, 90, 8
    ' accept input
  Case Else
     KeyAscii = 0

End Select

End Sub

 

[ascaife]

Thanks for the reply. Not sure what I'm doing wrong but I've copied that code, changed the control name, and now I can't enter any characters into my control.

Any ideas?

 

[HiTechCoach]

Without seeing your code, I'll SWAG* this: it could be a issue with your code logic.

Will you post the VBA code you are using?


* Scientific Wild A$$ Guess.

It's Friday and this week has been a heck of a month!

 

[ascaife]

No doubt I've made an error somewhere. Here's my code:

Private Sub TAC_Change()
If Len(Me.TAC.Text & "") > 5 Then
Me.TAC = Left(Me.TAC.Text, 5)
Me.TAC.SelStart = 5
End If
End Sub

Private Sub TAC_KeyPress(KeyAscii As Integer)
' "QSTURV" or the letter Z
Select Case KeyAscii
Case Is = 81, 82, 83, 84, 85, 86, 90, 8
' accept input
Case Else
KeyAscii = 0
End Select
End Sub

I do have another question though. From the code it looks like it will accept any five characters of QSTURVZ, whereas what I need is any combination of QSTURV OR Z by itself.

 

[HiTechCoach]

I created a test for and your code worked just fine.

It only accepts QSTURV or the letter Z. Just like what your original post said. Which are all upper case letters.

NOte: This does not allow qsturv or z (lower case) since You posted you wanted upper case characters only. You must be specific when programming. The computer only nows exactly what you tell it. Computer also see upper case and lower letters as being different. See http://www.asciitable.com

Are you typing upper case letters?

 

[ascaife]

Yep that's where I was going wrong. I added the lower case ascii codes too since I don't mind whether they go in as caps or not.

Still have the problem with Z though. I only want this accepted by itself, not including any other characters.

 

[Galaxiom]

In the On Change event procedure, test for the presence of the Z with:

If InStr(controlname, "Z") and Len(controlname)>1 Then: 'complain to the user with a Message Box or delete the Z.

This deletes the last character in a control:
controlname = Left(controlname, Len(controlname)-1)

Also rather than recording either case you could include the UCase function to convert everything to the same case. This can be applied in the AfterUpdate event.

 

[ascaife]

Sorry to keep stuffing this up. The results on this are pretty random. It lets me enter a z with other characters, then brings up the message box only when I go to delete the z. It will also let me enter zzz and brings up messagebox on the third z, then deletes the last two.

Private Sub TAC_Change()
If InStr(TAC, "Z") And Len(TAC) > 1 Then
Dialog.Box Prompt:="The letter Z implies that applications are not managed by a TAC" & vbCrLf & "Z may only be used on its own", _
Buttons:=(vbOKOnly + vbInformation), _
Title:="Information", _
ButtonDelay:=0
Me.TAC = Left(TAC, Len(TAC) - 1)
End If

 

[Galaxiom]

Don't be sorry. Learning programming is often a matter of trial and error. You will find you learn as much from what doesn't work that what does.

Much of what is contributed here is based on what we expect to work or at least hope it is heading in the right direction. As you orginally said, your problem is a little unusual.

The behaviour you describe is facinating and I just know I am going to learn something here.

I doubt it is the problem but we should include the full title of TAC.
Me!TAC or Me.TAC or perhaps even Me.TAC.Text as HiTextCoach used.

 

[ascaife]

Thanks for the support, I would be completely lost without the help of all the kind souls who contribute to this forum.

It seems that using Me.TAC.Text or just TAC.Text solves the problem.

Any ideas why?

 

[Galaxiom]

http://msdn.microsoft.com/en-us/library/aa173476(office.11).aspx

The Text property returns the formatted string. The Text property may be different than the Value property for a text box control. The Text property is the current contents of the control. The Value property is the saved value of the text box control. The Text property is always current while the control has the focus.

Once again I learnt from something that didn't work.

Value is the default property so that is what you get by just using the control's name.
BTW Best to include Me so Access doesn't have to guess.