Reference to Lookup Table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bw - 28 Nov 2005 11:56 GMT

The subform in my main form has a record source which is a table that only
has numbers in it (references to other tables).

MsgBox
Forms![frmTravelRequest]![frmHotels]![frmHotelLocationsSubform1]![LocationID]
displays a number for the record selected.

Row Source for the Lookup table for LocationID is:
SELECT tblLocations.LocationID, tblLocations.txtLocation FROM tblLocations;

So in my MsgBox example above, How do I display txtLocation instead of
LocationID?

Thanks,
Bernie

Reply to this Message

Allen Browne - 28 Nov 2005 12:08 GMT

Try the Column() property of the combo, i.e.:

Forms![frmTravelRequest]![frmHotels].Form![frmHotelLocationsSubform1].Form![LocationID].Column(1)

Note that Column() is zero based (i.e. the first column is 0, so the second
one is 1.)

If the .Form bit is new, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

I am assuming that:
a) The main form is named "frmTravelRequest".
b) It contains a subform control named "frmHotels".
c) That control contains a form which has another subform control named
"frmHotelLocationsSubform1".
d) This inner subform contains a form where the LocationID combo is found.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> The subform in my main form has a record source which is a table that only
> has numbers in it (references to other tables).
[quoted text clipped - 9 lines]
> So in my MsgBox example above, How do I display txtLocation instead of
> LocationID?

Reply to this Message

Douglas J Steele - 28 Nov 2005 12:12 GMT

MsgBox DLookup("txtLocation", "tblLocations", "LocationID =" &
Forms![frmTravelRequest]![frmHotels]![frmHotelLocationsSubform1]![LocationID
])

BTW, most of us do not recommend using Lookup fields. Take a look at
http://www.mvps.org/access/lookupfields.htm for some of the reasons why not.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> The subform in my main form has a record source which is a table that only
> has numbers in it (references to other tables).
>
> MsgBox

Forms![frmTravelRequest]![frmHotels]![frmHotelLocationsSubform1]![LocationID
]

> displays a number for the record selected.
>
[quoted text clipped - 6 lines]
> Thanks,
> Bernie

Reply to this Message

bw - 28 Nov 2005 16:40 GMT

Thank you both (Allen and Douglas)!

I appreciate all the help I receive. It seems the more I learn Access, the
less I know. I'm working on my most difficult project so far, and it's just
throwing me into a loop so that I can barely think straight anymore. The
comments by Douglas regarding lookup tables is the second I've received
within a week, but the first time I have heard about this previously. I
haven't been able to stop to analyze what this will do to how I make Access
do what I want, but to me, it is very foreign (to live without lookup
tables, as it is how I've taught myself (with all the help from this
newsgroup).

I will rethink lookup tables when I'm through with this project (almost
done). I'll probably be back to inquire about the ramifications of not
using them at a later date.

Thanks again for your help.
Bernie

> The subform in my main form has a record source which is a table that only
> has numbers in it (references to other tables).
[quoted text clipped - 12 lines]
> Thanks,
> Bernie

Reply to this Message

Allen Browne - 28 Nov 2005 17:00 GMT

Lookup tables are not a problem. They are an essential part of any database.

OTOH, the lookup wizard that runs in the context of table design is a horrid
beast with many undesirable side effects.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thank you both (Allen and Douglas)!
>
[quoted text clipped - 31 lines]
>> Thanks,
>> Bernie

Reply to this Message