Referencing data in a field within another field

[Rooksarii]

Hello folks,

Let me first apologize for any impropper terminology I may use as I am
presently trying to broaden my Office knowledge by diving into Access
head on.

My specific problem is this. I have a field in one of my tables that
references a value in another table via a list box. Next to it, instead
of manually creating another list box to go along with it, I would like
to have this other field acknowledge that the value in it's neighbor is
from "Table A, Column 2 Record 1" and display "Table A, Column 1,
Record 1".

Ultimately, I would like to create a Form to have users enter data
into, and this dropdown box represents data that all users should know.
The field I wish to autofill would be with data that further describes
the item selected from the list, but most users will not know this
info. I've tried digging through some form of expressions for this, but
to no avail, and being an absolute newbie in practice with this
application, I am not sure of my direction to resolve this quagmire
(use queries, expressions in data view, etc).

Any and all help is appreciated, and I will be more than glad to shoot
out more specifics if needed.

-Jon

[Larry Linson]

"Rooksarii" wrote

My specific problem is this. I have a field in one of my tables that
references a value in another table via a list box. Next to it, instead
of manually creating another list box to go along with it, I would like
to have this other field acknowledge that the value in it's neighbor is
from "Table A, Column 2 Record 1" and display "Table A, Column 1,
Record 1".
There is no inbuilt ability of quite this kind. For end-users who just work
from the Datasheet view of Tables, a Lookup field is a way of referring to a
value in another Table. However, I do not advise using that, except in that
very narrow situation. It obscures what is actually stored in the Field (the
foreign key to the other table, but you see the value from the other table),
and just from watching posts in the newsgroup, causes more problems than it
solves.

If you really mean by the example you want a "record number", that is not a
part of relational databases. By definition, records in a relational Table
are _unordered_. If you want them displayed in a particular order, you need
to use a Query with a Sort Field(s) specified, or the corresponding SQL
statement with ORDER BY clause, or the Sorting and Grouping Properties of a
Report.
Ultimately, I would like to create a
Form to have users enter data
into, and this dropdown box repre-
sents data that all users should know.
The field I wish to autofill would be
with data that further describes
the item selected from the list, but
most users will not know this
info.

If I understand what you want, you can accomplish this by using a Combo or
List Box with multiple Fields in the Row Source, and displaying the
"readable" data, but having available the key or internal data in additional
Fields.

I'm certain that there are examples of this in the Northwind.MDB example
database that comes with Access. There is also an excellent resource,
temporarily housed at http://www.mvps.org/access/downloads/bapp2000.exe that
has exellent help on things like using Combo and List Boxes. It is
originally from Microsoft, and is called the "Developer Solutions" database.
It uses Northwind for many of its examples.

Larry Linson
Microsoft Access MVP

[Rooksarii]

Thank you so much Larry!

Any and all resources will prove invaluable to me. Hopefully the long
term goal of all this is to get as comfortable with Access as I am with
Excel (not a guru in any terms, but I can get what I need done and find
what I need 99% of the time).

Basically what I have is users entering information off documents, and
one of the bits of info is a department number. The users will easily
be able to enter that into the form, but little if any of them will
know the name of the department that goes with the each number. If they
selected the departnemt number from the list, I was imagining the field
(so tempted to say cell, but will refrain) for department name auto
filling the the information based off the department number selected.
Ultimately it is not the most dire of issues, but when presented with a
problem, I always like to attempt to find a solution!

Many thanks again!

-Jon