Show all columns of a lookup field on the form

[LDMueller]

In Access 2003 I have a table with several Lookup fields as follows:
Quality
Productivity
Efficiency
Cooperation

Each Lookup field is setup alike as follows:
Display Control = Combo Box
Row Source Type = Table/Query
Row Source = Point to another table which has two fields as follows:
RatingAmount RatingDetail
1 Does not meet expectations
2 Meets expectations
3 Exceeds expectations

When I use these fields in my table, my user is rating for Quality,
Productivity, etc., I'd like to see both columns of the drop down appear on
the form after they make their selection (e.g. RatingAmount and Rating
Detail) similar to below:

Field
Quality 1 Does not meet expectations
Productivity 3 Exceeds expectations
Efficiency 2 Meets expectations

I have to keep the rating amount separate from the RatingDetail in it's
table because I use the RatingAmount for calculations in another table.
Because of this, I can't figure out how to show RatingAmount and Rating
Detail of the form when a rating is selected.

I hope this makes sense. Can anyone help!

Thanks in advance.

LDMueller

 

[Linq Adams via AccessMonster.com]

In the Query Grid create a Query based on the table.
In a blank field create a calculated field, call it DisplayRating
Type in DisplayRating: [RatingAmount] & " " & [RatingDetail}

Now, back in your form, re-create your combobox with the first field being
DisplayRating. Now after making a selection, you'll see

1 Does not meet expectations
2 Meets expectations
3 Exceeds expectations

 

[Keith Wilby]

In Access 2003 I have a table with several Lookup fields as follows:

Don't use lookup fields. Here's why:

http://www.mvps.org/access/lookupfields.htm

Keith.
www.keithwilby.co.uk

 

[Linq Adams via AccessMonster.com]

Lookup fields in ***tables*** are, indeed, evil, but I assumed by the
references to Row Source and Row Source Type that the OP was actually
talking about a combobox, not a table lookup field.

 

[LDMueller]

Thank you both for the information you've given me. All of it was helpful to
me.

I ended up changing my fields from Combo Boxes to Text boxes. Then I
created the query with the calculated field. Then, in my form I created
combo boxes with lookups to the query and having it store the value in the
field of the other table.

Thank you so much to both of you!

LDMueller

 

[Keith Wilby]

Lookup fields in ***tables*** are, indeed, evil, but I assumed by the
references to Row Source and Row Source Type that the OP was actually
talking about a combobox, not a table lookup field.

I didn't get that far, I saw "In Access 2003 I have a table with several
Lookup fields as follows" and the alarm bell clanging in my head distracted
me from the rest of the thread. Kudos to you for reading further

 

[Chegu Tom]

Thanks for posting WHY we shouldn't use lookup fields. I have never
trusted them and now I know why.

 

[Janice Brown]

Many many thank yous for the post! Based on the expert advice, I am now redesigning a table that had three lookup fields (changing them to hold numeric values). Not sure, but I think they caused endless puzzlement as to inability to sort the drop downs. Will use lookup combo box on the forms, which will then pass the numeric ID value of the name selected to the appropriate table to hold. It all makes sense now! Thanks again!!!



Keith Wilby wrote:

Re: Show all columns of a lookup field on the form
03-Aug-09

Don't use lookup fields. Here is why:

http://www.mvps.org/access/lookupfields.htm

Keith.
www.keithwilby.co.uk

Previous Posts In This Thread:

Show all columns of a lookup field on the form
In Access 2003 I have a table with several Lookup fields as follows:
Quality
Productivity
Efficiency
Cooperation

Each Lookup field is setup alike as follows:
Display Control = Combo Box
Row Source Type = Table/Query
Row Source = Point to another table which has two fields as follows:
RatingAmount RatingDetail
1 Does not meet expectations
2 Meets expectations
3 Exceeds expectations

When I use these fields in my table, my user is rating for Quality,
Productivity, etc., I'd like to see both columns of the drop down appear on
the form after they make their selection (e.g. RatingAmount and Rating
Detail) similar to below:

Field
Quality 1 Does not meet expectations
Productivity 3 Exceeds expectations
Efficiency 2 Meets expectations

I have to keep the rating amount separate from the RatingDetail in it is
table because I use the RatingAmount for calculations in another table.
Because of this, I cannot figure out how to show RatingAmount and Rating
Detail of the form when a rating is selected.

I hope this makes sense. Can anyone help!

Thanks in advance.

LDMueller

In the Query Grid create a Query based on the table.
In the Query Grid create a Query based on the table.
In a blank field create a calculated field, call it DisplayRating
Type in DisplayRating: [RatingAmount] & " " & [RatingDetail}

Now, back in your form, re-create your combobox with the first field being
DisplayRating. Now after making a selection, you will see

1 Does not meet expectations
2 Meets expectations
3 Exceeds expectations

--
There is ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003



Re: Show all columns of a lookup field on the form
Don't use lookup fields. Here is why:

http://www.mvps.org/access/lookupfields.htm

Keith.
www.keithwilby.co.uk

Lookup fields in ***tables*** are, indeed, evil, but I assumed by
Lookup fields in ***tables*** are, indeed, evil, but I assumed by the
references to Row Source and Row Source Type that the OP was actually
talking about a combobox, not a table lookup field.

--
There is ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003




Thank you both for the information you've given me.
Thank you both for the information you have given me. All of it was helpful to
me.

I ended up changing my fields from Combo Boxes to Text boxes. Then I
created the query with the calculated field. Then, in my form I created
combo boxes with lookups to the query and having it store the value in the
field of the other table.

Thank you so much to both of you!

LDMueller

:

"Linq Adams via AccessMonster.
I did not get that far, I saw "In Access 2003 I have a table with several
Lookup fields as follows" and the alarm bell clanging in my head distracted
me from the rest of the thread. Kudos to you for reading further

Thanks for posting WHY we shouldn't use lookup fields.
Thanks for posting WHY we should not use lookup fields. I have never
trusted them and now I know why.

EggHeadCafe - Software Developer Portal of Choice
A Simple Encrypting and Decrypting class in .net using System.Security.Cryptography Namespace
http://www.eggheadcafe.com/tutorial...a-d2e0a5fc8990/a-simple-encrypting-and-d.aspx