Ensure only letters and numbers are stored

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
 
N

Nikos Yannacopoulos

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
 
G

Gina

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
 
N

Nikos Yannacopoulos

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
 
G

Gina

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 : ) :) :)
 
N

Nikos Yannacopoulos

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!
 
N

Nikos Yannacopoulos

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
 
G

Gina

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
 
N

Nikos Yannacopoulos

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 :)
 
N

Nikos Yannacopoulos

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!
 
S

SteveS

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.
 
Top