Input Masking

[Sherry N.]

Hello,
I need to create a custom input masking code that would allow users to enter
any amount of characters and then a dash then a capital letter.
Examples:
they enter 555b and I want to store 555-B
they enter 55b and I want to store 55-B
they enter 5b and I want to store 5-B

Much appreciated
-- Sherry N.

 

[dymondjack]

I don't generally use the input mask, but instead do it on the control's
beforeupdate. If you want to do that, you can use a little function to do it.


Private Function pfSetFormat(str As String) As String
Dim Ret As String

Ret = Mid(str, 1, Len(str) - 1) & "-" & UCase(Mid(str, Len(str) - 1, 1)

pfSetFormat = Ret
End Function


Actually the function isn't even required, you could do it right in
beforeupdate.

Broken down, this concencates 3 strings into one return... 1st being all
the entered characters except the last one, second is the "-", and lastly it
uppercases the last character.

This could be modified some to include checking to see if the person already
enetered a - as the second-to-last value. Or you could use an actual input
mask, but you would have to wait for someone else to answer that, as I don't
use them.

hth

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Sherry N.]

Can you tell me how to enter it in the beforeupdate property?

 

[dymondjack]

To do it this way, you'll have to create an event procedure for the
BeforeUpdate event of the control. Open the control properties and go to the
events tab, find before update, and click the dropdown list on the side to
select Event Procedure. This should open the vba window, with these two
lines already there for you:

Private Sub YourControl_BeforeUpdate(Cancel As Integer)

End Sub


You'll put your code inside these two lines. There's a few things you
should probably add in here, keeping in mind scenarios such as no data being
entered, or a "-" already being in there, so we'll modify my previous example
a bit to account for this.

Here's what you'll end up with:

Private Sub YourControl_BeforeUpdate(Cancel As Integer)
Dim strValue As String

'Check and make sure the control has a value
If Nz(Me.YourControl, "") <> "" Then

'Get rid of any "-" already there
strValue = Replace(Me.YourControl, "-", "")

'Get all the characters except the last one
strValue = Mid(Me.YourControl, 1, Len(Me.YourControl) - 1)

'Add the "-" at the end
strValue = strValue & "-"

'Uppercase the last character of the control and add it
strValue = strValue & UCase( _
Mid(Me.YourControl, Len(Me.YourControl) - 1, 1))


'Check the value of strValue (it should be what you want)
'Open the Immediate Window to see what the statement is
Debug.Print strValue

'Apply this as the new value of the control
Me.YourControl = strValue

End If

End Sub


I haven't tested it, but I think it should be pretty close. Change any
YourControl you see to the name of your control.

For the record, using an input mask may be much easier if you are not
familiar with VBA and the various conditions to be aware of. I prefer not to
use input masks for my own reasons, but there's not real reason not to.

hth


Oh yea... don't forget to add at least some basic error handling
--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Sherry N.]

Thanks so much for your help but nothing happened. This is my code:

Private Sub RequestID_BeforeUpdate(Cancel As Integer)
Dim strValue As String

'Check and make sure the control has a value
If Nz(Me.RequestID, "") <> "" Then

'Get rid of any "-" already there
strValue = Replace(Me.RequestID, "-", "")

'Get all the characters except the last one
strValue = Mid(Me.RequestID, 1, Len(Me.RequestID) - 1)

'Add the "-" at the end
strValue = strValue & "-"

'Uppercase the last character of the control and add it
strValue = strValue & UCase( _
Mid(Me.RequestID, Len(Me.RequestID) - 1, 1))

End If
End Sub

 

[dymondjack]

I'm stuck on this one now. There's a few quick changes that can be made to
get it to work, but for some reason I'm coming up with an error on changing
the value of the field in beforeupdate.

For the code, I had a len calculation wrong and forgot to actually assign
the value to the control at the end (that helps).




Private Sub RequestID_BeforeUpdate(Cancel As Integer)
Dim strValue As String

'Check and make sure the control has a value
If Nz(Me.RequestID, "") <> "" Then

strValue = Me.RequestID

'Get rid of any "-" already there
strValue = Replace(strValue, "-", "")

'Get all the characters except the last one
strValue = Mid(strValue, 1, Len(strValue) - 1)

'Add the "-" at the end
strValue = strValue & "-"

'Uppercase the last character of the control and add it
strValue = strValue & UCase( _
Mid(Me.RequestID, Len(Me.RequestID), 1))

Debug.Print strValue

Me.RequestID = strValue

End If
End Sub



That almost works, except for the error. I tried adding this to the on exit
event, which works as long as it's not the last control in the tab order,
otherwise you cant tab out of the field.

I event took a look at custom lookups, but wasn't able to see how to create
one with a variable amount of characters.



--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Sherry N.]

I put the code in the AfterUpdate property and it seems to be working fine.
Thanks so much!