DLookUp Returning Primary Key

[channell]

I have the following DLookup Function:

=DLookUp("[PRELOAD POSITION]","[tEMPLOYEES]","[Employee ID] = " & [Employee
ID])

[PRELOAD POSITION] is a FK in my tEMPLOYEES. This function is returning the
Number (AutoNumber) for Position ID, and not the actual text (Which I need).

If you could help, I greatly appreciate it very much!!

-Scott Channell

 

[Ken Sheridan]

Scott:

If you merely want to show the position in an unbound text box in a form or
report then you should look up the value from the (text) position column in a
query which joins the tEmployees table to the positions table. Your
expression will be the same, but with the query name in place of the
tEmployees table name. Another way would be to use nested DLookup function
calls, but that's very inefficient.

If, on the other hand, you are getting the position as a default and
assigning it to a column in another table (which, if I recall rightly was
what you were doing in an earlier thread) then you should be storing the
numeric value as a foreign key in that table in the same way as you are in
the tEmployees table, not the text value. If you use a surrogate numeric
primary key, as you have here, non-key columns should not be referenced by a
foreign key. The alternative would be to dispense with the autonumber column
and use the text column as the positions table's 'natural' primary key. That
way you don't need to bring the positions table in at all when looking up the
value as the value will of the foreign key in tEmployees will be the text
value. The positions table is then there to enforce referential integrity by
allowing only valid position values to be entered into foreign key columns in
tables which reference it.

Ken Sheridan
Stafford, England

 

[channell]

Thanks guys. I appreciate it. Yeah, I just don't like to display
information from a combo box. If I was going to have the combo box used, I
like it, but just for displaying information, I think a combo box looks
unprofessional.

-Scott Channell

Scott:

If you merely want to show the position in an unbound text box in a form or
report then you should look up the value from the (text) position column in a
query which joins the tEmployees table to the positions table. Your
expression will be the same, but with the query name in place of the
tEmployees table name. Another way would be to use nested DLookup function
calls, but that's very inefficient.

If, on the other hand, you are getting the position as a default and
assigning it to a column in another table (which, if I recall rightly was
what you were doing in an earlier thread) then you should be storing the
numeric value as a foreign key in that table in the same way as you are in
the tEmployees table, not the text value. If you use a surrogate numeric
primary key, as you have here, non-key columns should not be referenced by a
foreign key. The alternative would be to dispense with the autonumber column
and use the text column as the positions table's 'natural' primary key. That
way you don't need to bring the positions table in at all when looking up the
value as the value will of the foreign key in tEmployees will be the text
value. The positions table is then there to enforce referential integrity by
allowing only valid position values to be entered into foreign key columns in
tables which reference it.

Ken Sheridan
Stafford, England

I have the following DLookup Function:

=DLookUp("[PRELOAD POSITION]","[tEMPLOYEES]","[Employee ID] = " & [Employee
ID])

[PRELOAD POSITION] is a FK in my tEMPLOYEES. This function is returning the
Number (AutoNumber) for Position ID, and not the actual text (Which I need).

If you could help, I greatly appreciate it very much!!

-Scott Channell

 

[Ken Sheridan]

Scott:

Looking up the value from a query which joins the tables will do that, but
basing the form on a query which joins the tables and binding the text box to
the text column in the positions table would more efficient. If you set the
text box's Locked property to True (Yes) and its Enabled property to False
(No) then the user won't be able to move focus to the text box, and try to
edit the value, which might otherwise be a temptation.

Ken Sheridan
Stafford, England

Thanks guys. I appreciate it. Yeah, I just don't like to display
information from a combo box. If I was going to have the combo box used, I
like it, but just for displaying information, I think a combo box looks
unprofessional.

-Scott Channell

Scott:

If you merely want to show the position in an unbound text box in a form or
report then you should look up the value from the (text) position column in a
query which joins the tEmployees table to the positions table. Your
expression will be the same, but with the query name in place of the
tEmployees table name. Another way would be to use nested DLookup function
calls, but that's very inefficient.

If, on the other hand, you are getting the position as a default and
assigning it to a column in another table (which, if I recall rightly was
what you were doing in an earlier thread) then you should be storing the
numeric value as a foreign key in that table in the same way as you are in
the tEmployees table, not the text value. If you use a surrogate numeric
primary key, as you have here, non-key columns should not be referenced by a
foreign key. The alternative would be to dispense with the autonumber column
and use the text column as the positions table's 'natural' primary key. That
way you don't need to bring the positions table in at all when looking up the
value as the value will of the foreign key in tEmployees will be the text
value. The positions table is then there to enforce referential integrity by
allowing only valid position values to be entered into foreign key columns in
tables which reference it.

Ken Sheridan
Stafford, England

I have the following DLookup Function:

=DLookUp("[PRELOAD POSITION]","[tEMPLOYEES]","[Employee ID] = " & [Employee
ID])

[PRELOAD POSITION] is a FK in my tEMPLOYEES. This function is returning the
Number (AutoNumber) for Position ID, and not the actual text (Which I need).

If you could help, I greatly appreciate it very much!!

-Scott Channell

 

[Howard Burgman]

Thanks guys. I appreciate it. Yeah, I just don't like to display
information from a combo box. If I was going to have the combo box used,
I
like it, but just for displaying information, I think a combo box looks
unprofessional.

-Scott Channell

Scott:

If you merely want to show the position in an unbound text box in a form
or
report then you should look up the value from the (text) position column
in a
query which joins the tEmployees table to the positions table. Your
expression will be the same, but with the query name in place of the
tEmployees table name. Another way would be to use nested DLookup
function
calls, but that's very inefficient.

If, on the other hand, you are getting the position as a default and
assigning it to a column in another table (which, if I recall rightly was
what you were doing in an earlier thread) then you should be storing the
numeric value as a foreign key in that table in the same way as you are
in
the tEmployees table, not the text value. If you use a surrogate numeric
primary key, as you have here, non-key columns should not be referenced
by a
foreign key. The alternative would be to dispense with the autonumber
column
and use the text column as the positions table's 'natural' primary key.
That
way you don't need to bring the positions table in at all when looking up
the
value as the value will of the foreign key in tEmployees will be the text
value. The positions table is then there to enforce referential
integrity by
allowing only valid position values to be entered into foreign key
columns in
tables which reference it.

Ken Sheridan
Stafford, England

I have the following DLookup Function:

=DLookUp("[PRELOAD POSITION]","[tEMPLOYEES]","[Employee ID] = " &
[Employee
ID])

[PRELOAD POSITION] is a FK in my tEMPLOYEES. This function is
returning the
Number (AutoNumber) for Position ID, and not the actual text (Which I
need).

If you could help, I greatly appreciate it very much!!

-Scott Channell