Table lookup value on a form - text or interger field?

  • Thread starter Thread starter Bob Richardson
  • Start date Start date
B

Bob Richardson

In Table Design I've used a table lookup to define a field...TimeSlot. In
datasheet view it correctly uses a drop down list with the correct text
values, yet it stores a number in the file...the record's key. This appears
to be correct.

On a form I have a drop down list for TimeSlot, as well as a 2nd drop-down
list that needs to use the text value of the TimeSlot to determine it's
values. The name of the TimeSlot field, on the form, is TS.

This part of the query for the 2nd drop-down list isn't working.

WHERE Left(TS,1)=Left(Q_Classes.ClassCode,1)

Is TS a numeric value, since a numeric value is stored in the file, OR is TS
a text value, since that's what is visible on the form? How can I get the
text value for TS?
 
TimeSlot will be whatever data type as the primary key of the table where
TimeSlot gets its description (numeric value. This confusion is one of the
reason why many people feel Lookup FIelds are a very bad idea (see
http://www.mvps.org/access/lookupfields.htm at "The Access Web")

Since you should never be working directly with tables, use a combobox on a
form, so that you know what's going on "under the covers".
 
I've taken your advice and switched the Table field from a lookup table to a
Text field. Then on the form, I use a ComboBox.

I'm working with a subform2 where the control name of col A is TS, which is
now a text field that can be taken from a combobox. In col B I have another
combo box which needs to see the text value of Col A. My SELECT query on
col B is still not working.

I'm trying to refer to the value as Me!TS but something is wrong. I thought
I could use this simple syntax since Col A and Col b are both on the same
form...subform2.
 
It seems that my query can refer to the Col A value with just TS (not
Me!TS). However, it's not finding the correct value. Is there something I
should do with the bound column?
 
Me is a short form for the current form or report. It replaces
[Forms]![FormName] or [Reports]![ReportName]. It can only be used from
within code on that form or report. Therefore, any other reference, even on
the property sheet or in a query, must use the long form as shown above. I
hope that solves your problem.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top