auto fill based on another field

I have two fields in my database "Nurses Name"and "Nurses
Code". I would like to fill in Nurses Name and when I tab
to Nurses Code it would auto fill the assign code to that
Nurses Name. Some reports run from this database are run
with just the code for privacy purposes. This would save
some keystrokes and manual lookup to see what code goes
with what name.
There is actually two other instances in this database I
could use this function. If possible?

bill wrote:

I have two fields in my database "Nurses Name"and "Nurses
Code". I would like to fill in Nurses Name and when I tab
to Nurses Code it would auto fill the assign code to that
Nurses Name. Some reports run from this database are run
with just the code for privacy purposes. This would save
some keystrokes and manual lookup to see what code goes
with what name.
There is actually two other instances in this database I
could use this function. If possible?


You can look up the Code value in the Name text box's
AfterUpdate event procedu
txtNursesCode = DLookup("[Nurses Code]", "Nursestable", _
"[Nurses Name] = """ & txtNursesName & """"

But, how about using a combo box so users can just select a
nurse from the list? This eliminates the issue of mistyping
the name and provides autocomplete as soon as the user types
just enough of the name to identify the nurse. The combo
box's RowSource can get both fields so the code will be
available as soon as the name is selected. In the combo
box's AfterUpdate event:
txtNursesCode = cboNursesName.Column(1)
--
Marsh
MVP [MS Access]

Thank you for your quick reply. I'm sorry to say your
explanation is a little beyond my experience so I have
some questions:
The "Nurses Name" and Nurses Code" combo boxes are named
the same should they be renamed to txtNursesName and
txtNursesCode?
Question on the second paragraph:
When I entered In the "Nurses Name RowSource
txtNursesCode = cboNursesName.Column(1)
I got a message stating it can't find the Macro
txtNursesCode????
For further explanation Name and Code have to be seperate
fields because some reports have just Name with the code
hidden and some reports have just the code.

-----Original Message-----
bill wrote:

I have two fields in my database "Nurses
Name"and "Nurses
Code". I would like to fill in Nurses Name and when I
tab
to Nurses Code it would auto fill the assign code to
that
Nurses Name. Some reports run from this database are run
with just the code for privacy purposes. This would save
some keystrokes and manual lookup to see what code goes
with what name.
There is actually two other instances in this database I
could use this function. If possible?


You can look up the Code value in the Name text box's
AfterUpdate event procedu
txtNursesCode = DLookup("[Nurses
Code]", "Nursestable", _

"[Nurses Name] = """ & txtNursesName & """"

But, how about using a combo box so users can just select
a
nurse from the list? This eliminates the issue of
mistyping
the name and provides autocomplete as soon as the user
types
just enough of the name to identify the nurse. The combo
box's RowSource can get both fields so the code will be
available as soon as the name is selected. In the combo
box's AfterUpdate event:
txtNursesCode = cboNursesName.Column(1)
--
Marsh
MVP [MS Access]
.

wrote:

Thank you for your quick reply. I'm sorry to say your
explanation is a little beyond my experience so I have
some questions:
The "Nurses Name" and Nurses Code" combo boxes are named
the same should they be renamed to txtNursesName and
txtNursesCode?

There are times when using the same name for a control and a
field can lead to problems so I change the names of the
controls to use a three character prefix that indicates the
type of the control. E.g. txtNursesName for a text box
bound to the NursesName field. Or cboNursesName if the
nurses name field is bound to a combo box control.

Note that it is a pain to use spaces or other funky
characters in a name. Stick to just alphanumeric characters
to keep it simple. While we're on the topic of naming
things, don't use any standard english words (Name and Date
are especially bad choices for the name of anything) since
there is a possible conflict with Access use of the word.


Question on the second paragraph:
When I entered In the "Nurses Name RowSource
txtNursesCode = cboNursesName.Column(1)
I got a message stating it can't find the Macro
txtNursesCode????

Check Help on RowSoure.

This property can be the name of a table/query, an SQL
statement or a value list. This is where you specify the
data for each column in the combo box. I think you want to
set the RowSource to something like:
SELECT NursesName, NursesCode From Nurses Order By
NursesName

The VBA code I posted should be included in the combo box's
AfterUpdate event procedure.

For further explanation Name and Code have to be seperate
fields because some reports have just Name with the code
hidden and some reports have just the code.

Of course. One of the rules of relational database
normalization prohibits packing more than one value into a
single field. Fields should have what's called an atomic
value, i.e. indivisible (in terms of use in your
application).

Be careful, the clarity of your problem explanation is going
downhill. First you say the field/control is named [Nurses
Code] or NursesCode, but now you just use Name and Code and
I can;t tell if you're refering to a field or a control.
Please try to be consistent and accurate so we know what
we're talking about.
--
Marsh
MVP [MS Access]



-----Original Message-----
bill wrote:

I have two fields in my database "Nurses
Name"and "Nurses
Code". I would like to fill in Nurses Name and when I
tab
to Nurses Code it would auto fill the assign code to
that
Nurses Name. Some reports run from this database are run
with just the code for privacy purposes. This would save
some keystrokes and manual lookup to see what code goes
with what name.
There is actually two other instances in this database I
could use this function. If possible?


You can look up the Code value in the Name text box's
AfterUpdate event procedu
txtNursesCode = DLookup("[Nurses
Code]", "Nursestable", _

"[Nurses Name] = """ & txtNursesName & """"

But, how about using a combo box so users can just select
a
nurse from the list? This eliminates the issue of
mistyping
the name and provides autocomplete as soon as the user
types
just enough of the name to identify the nurse. The combo
box's RowSource can get both fields so the code will be
available as soon as the name is selected. In the combo
box's AfterUpdate event:
txtNursesCode = cboNursesName.Column(1)
--
Marsh
MVP [MS Access]
.