Reference to Lookup Table

[bw]

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

 

[Allen Browne]

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.

 

[Douglas J Steele]

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.

 

[bw]

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

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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).

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