You can convert input to upper case as its typed into a control by putting
the following in the control's KeyPress event procedure:
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))
You can convert any existing data to upper case by means of an update query
e.g.
UPDATE Addresses
SET State = UCASE(State)
WHERE State IS NOT NULL;
However to ensure data integrity you should really have a separate table
States with a column State defined as its primary key. The abbreviations for
all states would be entered into this column in advance (use the above code
for the KeyPress event of the State control in the form bound to the States
table to force the input to upper case). You can add another column for the
full name of the state if you wish.
In a form bound to your main addresses table you can then bind the State
column to a combo box with a RowSource of:
SELECT State FROM States ORDER BY State;
and its LimitToList property set to true (Yes in the properties sheet). The
user can then select the state from the list with the mouse or by entering
the characters (ensure the control's AutoExpand property is set to True) and
the control will go to the first match as each character is entered.
To muddy the waters further, however, if you have columns for both City and
State for instance in the main addresses you are introducing redundancy as
the State is determined by the City, so you should only have a CityID column,
a unique number not the city name as city names are duplicated, which will
reference the primary key CityID of a Cities table which in turn will
reference the States table. Update anomalies which could result in an
address being entered with a city in the wrong state are thus avoided. With
this normalized structure you can still select the state first then select
from a list of cities in that state if you wish, by using an unbound combo
box for the state. You'll find a demo of how to handle this sort of data
(using the local administrative areas in my location) at:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
Ken Sheridan
Stafford, England
