Creating global preferences in Access database

[Guest]

I am working on an existing database that has over 500 forms in the front-end
and is linked to various back-end databases (some Access, some SQL Server,
some MSDE, etc.) I have a customer who has bought the application, but wants
all the data to be entered in uppercase automatically for all forms. I know
that I could set input masks on the controls to do this OR set the following
code in the keypress event of the form or control:

Private Sub Form_KeyPress(KeyAscii As Integer)
Dim strCharacter As String

' Convert ANSI value to character string.
strCharacter = Chr(KeyAscii)
' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))

End Sub

However, with all the forms and controls, I am wondering if there is a way
to write code that will do this at the application level (i.e. at Startup or
via a Splash form). Ideally, at startup, the program would look at a
"preferences" table, and set the entire project to use uppercase or normal
case depending on the preference setting.

Does anyone have an idea how to do this in Access VBA?

Dan Joosten

 

[Guest]

There is a way, i just tested it.

You have the spelling Tab page under Tools>Options

There you click on the button autocorrect options

At the bottom you have a function replace Text as you type

Just add there all the translations e.g. a -> A, b -> B

- Raoul

 

[Guest]

Thanks for your suggestion. I tried the auto correct feature, but this
method doesn't do what I need it to. If you type a single letter in lower
case it will change it to upper case. However, autocorrect seems to evaluate
the entire string rather than just one letter at a time. Therefore, if I
type the letter more than once it doesn't change it because the entire string
is not in the autocorrect list.

Also, this method is PC dependent and would need to be changed on every PC
that uses the database.

I am looking for a solution that would be part of the database and that
could be turned on or off by changing a value in a preferences table. I
would like this to occur when the database is opened or when the user logs in
to the main form.

I have found a method to do this by adding code to the KEYPRESS event of
each form, but there are 635 forms in the database and adding and maintaining
the code would be tedious.

Thanks again for trying to help!

 

[Guest]

how many tables are there?
Because you i would suggest 2 options

- detect textbox or combobox and then add the code automated. So the code is
generated by a procedure which opens each form and creates the events.
- you could update also only 2 events afterupdate and afterinserts whereby
you update the text/memo fields in the database with a query for that record.

- Raoul

 

[Guest]

There are 625 forms and hundreds of linked tables in this database. I can
add the following code to each form and set 'KeyPreview' on and it will work:

Private Sub Form_KeyPress(KeyAscii As Integer)
Dim strCharacter As String

' Convert ANSI value to character string.
strCharacter = Chr(KeyAscii)
' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))

End Sub

What I need is a way to do the same thing without having to add this code
for all 625 forms. The client requires that the data be converted to
uppercase as it is entered and it must be stored in uppercase as well. I was
just wondering if there was a method or property for the Application object
or if there are other ways to cause uppercase entry without having to
actually add code to forms or controls - something that would run as the
application opens.

Thanks!