Value from combo box?

[tmtompkins]

I know this has to be simple and I'm just not seeing it but, I have a combo box in a form from a table called classes. The table has an ID field and the class name field. My problem is I get the form combo box populated with the text of the class name but the table the form is based on populates the field with the ID number. By changing the combo box's bound column number I can change that around but I would like both form and table class fields to be populated with the class name text. What am I missing? Thanks, Terry

 

[JeremyNYC]

Terry,

The whole idea is that you populate tables with the ID numbers, not the text value. That's what makes working with lookup tables a valuable thing to do when you have a one-to-many relationship. The combo box simply translates the numbers into the text for you.

Jeremy ==
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

(See the Developers' section)
AlphaBet City Dataworks

Please post in the appropriate forum and include 1) a descriptive subject 2) code and SQL, if referenced,
and 3) expected results. thread181-473997

 

[tmtompkins]

Jeremy, thanks for responding. OK, my original problem was I was needing to make a subreport from the table and when I do I end up with the ID numbers, which won't help my users much. Have I got something set wrong? Thanks, Terry

 

[JeremyNYC]

Terry,

Here's an example, with made up object names

tblWhatever
WhateverID (autonumber)
ClassID (numeric)
whateverOtherFields

tblClass
ClassID (autonumber)
ClassName
ClassOtherFileds

The query on which you'd base that report would look something like this:
SELECT WhateverID, WhateverOtherFields, ClassName
FROM tblWhatever LEFT JOIN tblClass ON tblWhatever.ClassID = tblClass.ClassID

That way you'll get all the details about the Whatever, and you'll get the class name.

When you do the query for the form, you don't need to include tblClass, because you can just grab the ClassID from tblWhatever, and have that be the bound column in a two-column combo box.

Jeremy ==
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

(See the Developers' section)
AlphaBet City Dataworks

Please post in the appropriate forum and include 1) a descriptive subject 2) code and SQL, if referenced,
and 3) expected results. thread181-473997

 

[tmtompkins]

Jeremy,

Pardon my thick headedness, but I need a little further help. I have the report doing what I want except for this text/number problem. In reading your instructions I got confused. If I bring up the report in design view and then bring up the properties of the class text box, what do I need to change to show the class text instead of the ID #. Can you point me in the right direction?
My combo box comes from a table named ClassesTbl. Its fields are,

ID (auto number) and
Class.

My data goes to a table named GradesTbl. Its fields include,

ID (auto number),
SchoolYear
Semester
Class
Teacher
etc.

My class field in GradesTbl is populated with the id numbers from ClassesTbl. My report comes from the GradesTbl. Do I need to be working on the query or report? What do I need to change to make the report show the class text instead of the ID number? Do I need to link the grades table with the class’s table? Bear with me I’ll get this yet. Thanks, Terry

 

[JeremyNYC]

Base your report on a query that pulls together all of the data you need. If you want to show the text value, include the table with that in it and then include that field. Again, forms and reports should almost always (always in my experience, though there could be exceptions) be based on queries, not tables.

Also, don't do any sorting on a query that will be used in a report--it will have to be resorted when run by the report in any case (sorting and grouping menu item takes care of this).

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.