Ensure only letters and numbers are stored

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi.

me again ;) problems don't seem to run out today

can I do this on access form level ?
tried the formatting thing .... but failed :(

here's my problem:
a user types in a car reg. number (germany) and only big letters and numbers
should be left there
these input masks .... well, don't seem to be able to handle them

no spaces or "-" or any other key should be in the field - all should be
taken out apart from letters and numbers as already mentioned
the num of letters or numbers could vary but the reg number always starts
with a big letter

should I better use vba ??

please, give me a hand on this
Gina
 
Gina,

Yes, VBA is indeed the way to go. Paste this code in the control's
Before Update event:

Private Sub Text0_BeforeUpdate(Cancel As Integer)
vInvalid = False
vInput = Me.ActiveControl
For i = 1 To Len(vInput)
Select Case Asc(Mid(vInput, i, 1))
Case Is <= 47
vInvalid = True
Case 58 To 64
vInvalid = True
Case 91 To 96
vInvalid = True
Case Is >= 123
vInvalid = True
End Select
If vInvalid = True Then
msg = "Your input contains invalid characters!"
typ = vbExclamation
ttl = "Input Error"
MsgBox msg, typ, ttl
Cancel = True
Exit Sub
End If
Next
End Sub

and this in the After Update event:

Private Sub Text0_AfterUpdate()
Me.ActiveControl = UCase(Me.ActiveControl)
End Sub

The before update code will check for invalid characters, warn if one
exists and keep the focus to the field; it will allow lower case
letters, which the code in the after update event will capitalize.

HTH,
Nikos
 
Nikos ... thanks soooo much
had to slightly amend it .... you know the germans and their exceptions ;-)

Select Case Asc(Mid(vInput, i, 1))
Case Is <= 47
vInvalid = True
Case 58 To 64
vInvalid = True
Case 91 To 96
vInvalid = True
Case 123 To 195
vInvalid = True
Case 197 To 213
vInvalid = True
Case 215 To 219
vInvalid = True
Case Is > 221
vInvalid = True
End Select

That's for ÄÜÖ

I am so grateful .... you saved my day !!!
I would have never been able to find something clever like your stuff!!

Gina
 
Gina,

Glad to have helped, and even more so that you had to amend it and did
so successfully; this goes to show you understood how it works, which in
my view is far more important than just getting the job t hnd done.

Nikos
 
Nikos,
..... what I did was just looking up something extremely easy ... and I am
glad the access help opened up this time!!!

I had to amend it further .... in my first dizzyness about your help &
fantastic code I forgot to include the small äöü

Thanks again !!!
Gina : ) :) :)
 
Gina said:
Nikos,
.... what I did was just looking up something extremely easy ... and I am
glad the access help opened up this time!!!
Click to expand...

I know what you mean, I get this all the time throughout Office. Quite
frustrating, isn't it? In the beginning I used to resort to Task Manager
to kill the Help application, which of course killed the whole app
(Access or whatever) with it, and that only after repeated attempts.
Then I found out it's easier to go to Processes instead and kill
MSOHELP.EXE - actually two instances of it that always seem to open the
first time - which is faster and doesn't close the main app with it.
Thought you might want to check it out!
 
I'm afraid it's MS who have to check it; must be a bug. By the way, what
version of Access/Office/Windows are you on? Just checking this Help
issue. I have WinXP Prof SP1, Access (and Office) 2000.

Nikos
 
Nikos,

I use W2K SP6 !? ... if I remember correctly
Office 2000 .... think I have to check this SP stuff again ... when the help
refused to open up again and again :( !!! I deinstalled office and
reinstalled it - I know ... the hard way ... but simply didn't come anything
else to my mind then .... so the sp stuff on office 2k I may have to repeat
I guess :-|

Gina
 
Actually it may be related to IE more than to Office itself. Office help
is actually IE with a different shell. A long time ago I was having a
different problem (hyperlinks not working in Help), and after
de-istalling and re-istalling Office a couple of times I finally fixed
it by re-installing IE!!! Although it had been working correctly as a
browser the whole time!
No wonder I no longer use IE for browsing :-)
 
No. I'm not full of good ideas, I'm full of traumatic experiences! If
you find the solution to this, please share it with us!
 
Gina said:
Hi.

me again ;) problems don't seem to run out today

can I do this on access form level ?
tried the formatting thing .... but failed :(

here's my problem:
a user types in a car reg. number (germany) and only big letters and numbers
should be left there
these input masks .... well, don't seem to be able to handle them

no spaces or "-" or any other key should be in the field - all should be
taken out apart from letters and numbers as already mentioned
the num of letters or numbers could vary but the reg number always starts
with a big letter

should I better use vba ??

please, give me a hand on this
Gina
Click to expand...

Have you solved the problem??

If you haven't, code (VBA) will/can do the validation. Please post a
couple of examples of the car reg. numbers.
 
Back
Top