Replace

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Hi

Using Access 2002, I have a unboard text box on a contacts form for the
contacts department. For uniformity I want to ensure the user always enter
data, for example, in the form 'Health and Safety' instead of 'Health &
Safety' i.e. replacing '&' with 'and'.

I would appreciate if someone can advise if this is possible and, if so, an
example of code.

TIA

Tom
 
Hi

Using Access 2002, I have a unboard text box on a contacts form for the
contacts department. For uniformity I want to ensure the user always enter
data, for example, in the form 'Health and Safety' instead of 'Health &
Safety' i.e. replacing '&' with 'and'.

I would appreciate if someone can advise if this is possible and, if so, an
example of code.

TIA

Tom
Click to expand...


A simple way ...
Code the form control's AfterUpdate event:
If Me![ControlName] = "Health & Safety" then
Me![ControlName] = "Health and Safety"
End If

Or you can use in place of the above:

If InStr([ControlName],"&") >0 Then
Me![ControlName] = Replace([ControlName],"&","and")
End If

Now what do you do if the user enters "Health & Saftey" or "Health and
Saftey"?

A better way is to use a Combo Box with the correct spelling of ALL
the departments. Set the Combo LimitToList property to Yes.
Only values from the combo can be selected.
You can use the combo's NotInList property to add additional values
when needed.
 
Tom said:
Using Access 2002, I have a unboard text box on a contacts form for the
contacts department. For uniformity I want to ensure the user always enter
data, for example, in the form 'Health and Safety' instead of 'Health &
Safety' i.e. replacing '&' with 'and'.
Click to expand...


Use the text box's AfterUpdate event to change it:

Me.thetextbox = Replace(Me.thetextbox, "&", "and")

BUT. be careful with this kind of thing. If you have a long
list of things to replace, it might become slower than your
users can tolerate. Also, it's conceivable that one change
can walk all over another change.
 
Many thanks Fred & Marshall for your assistance and advice.

The field in question holds job title description in respect of contacts in
numerous departments in different organisations. The variety in these
descriptions is so varied and one off and as such I consider a drop down box
to be impractical. The description is usually short so the replace speed is
not really an issue. It is mainly being done for visual consistency on
reports.

Tom
 
Of course, you could always "train" the users by not allowing them to enter,
for example, the ampersand!

Private Sub YourTextBox_KeyPress(KeyAscii As Integer)
If KeyAscii = 38 Then
KeyAscii = 0
MsgBox "You cannot enter an Ampersand! You must use the word 'And'"
End If
End Sub

This code physically prevents the user from entering an ampersand and pops up
a messagebox explaining that the word "and" has to be used. After doing this
a few times, most users will get the message.
 
Back
Top