Option Group Query

[SeaTigr]

Greetings all.



For my latest conundrum, I am trying to construct a query that utilizes an option group selection for its criteria.

So, on a form for entering new equipment into the inventory database, a user utilizes the option group to indicate who owns the piece of equipment.

When the query executes, the column governed by the option group simply returns a number, which I assume to be the ID key for the option selected. I would like to have it return the plain text name of the division associated with that number.

How do I do this?


Many thanks in advance.

[jzwp11]

In the underlying table, do you have the division field set up as a lookup field?

[SeaTigr]

It's set up as text.

[jzwp11]

Text is the datatype or field size, but do you have it set up as a lookup field in your table? To find out: open the table in design view, click on the field name, then go to the lower pane and click on the Lookup tab. If the display control property is set to anything other than text box, you have a lookup field.

How are you running the query from your form?
Can you provide a little more detail on your table structure (table names, field names and relationships)?

[SeaTigr]

The display control property for the field is set to "Text Box".

On the query form I recreated the option group from the "new equipment" form. The order of the options is identical. What I've done for my other queries is to create the queries, put in the various fields I want returned, and set the criteria for the field being queried to look for input from the form. For example: [Forms]![Queries & Reports]![TxtSerial#]

TxtSerial# is a text box on the Queries & Reports form that allows a user to enter the serial number of the equipment they are looking for. I have a command button created that the user clicks after entering the serial number, which is linked to the serial number query. I have a separate command button for every query (system name, equipment type, brand name, etc.).

The basic structure is I have an Equipment Table that has records added to it from the Equipment Form. The Equipment Form has multiple fields for entering pieces of information (system name, equipment type, brand name, model, operating system, amperage, etc.). There are combo boxes for fields I didn't want users typing in every possible spelling under the sun. The options for a combo box are drawn from another table (with each combo box having its own associated table to draw options from). So, for example, the system name field is a combo box. There is a System Name List, a table where I entered all the system names. The combo box references that table when providing options for the user to choose from.

In my Relationships I have linked each list to its corresponding field in the Equipment Table.

[jzwp11]

Everything you describe seems OK.

Going back to your initial post:

When the query executes, the column governed by the option group simply returns a number, which I assume to be the ID key for the option selected. I would like to have it return the plain text name of the division associated with that number.

Did you verify that the number returned is indeed the ID key of the table? Can you post the SQL text of that query?

[SeaTigr]

The option group is not associated with any table other than the Equipment Table. I created the option group on the Equipment Form, noted the options in the wizard, and chose the check mark style for the option group.


Here's the query as I've structured it.

Since this is a public forum, I blanked out any potentially identifying information. The criteria for the equipment ownership, OG_EquipmentOwner, means Option Group_Equipment Owner. This query does execute properly when using the option group on the Queries & Reports Form. It just returns the number instead of the plain text. I'm not sure if the plain text is recorded anywhere except the label/text box/whatever next to the check boxes in the option group.

[SeaTigr]

Here's the SQL:

SELECT [$$$ Systems List].System, [Unit Type List].[Computer Type], [$$$ Equipment Table].[Unit Brand], [$$$ Equipment Table].[Unit Model], [Operating Systems List].[Operating System], [$$$ Equipment Table].[Unit Serial #], [$$$ Equipment Table].[Unit $$$ #], [$$$ Equipment Table].[Service Tag #], [$$$ Equipment Table].[$$$ Equipment], [$$$ Equipment Table].[$$$ Number]
FROM [Unit Type List] INNER JOIN ([$$$ Systems List] INNER JOIN ([Operating Systems List] INNER JOIN [$$$ Equipment Table] ON [Operating Systems List].ID = [$$$ Equipment Table].[Unit OS]) ON [$$$ Systems List].ID = [$$$ Equipment Table].[System Name]) ON [Unit Type List].ID = [$$$ Equipment Table].[Unit Type]
WHERE ((([$$$ Equipment Table].[$$$ Equipment])=[Forms]![Queries & Reports]![OG_EquipmentOwner]));


"$$$" Represents where I've removed identifying information.

[jzwp11]

I created the option group on the Equipment Form, noted the options in the wizard, and chose the check mark style for the option group.

I am guessing that it is pulling the numbers you assigned when creating the option group which will not work to do the filtering correctly.

Out of curiosity are the system name, unit type and unit OS fields in your equipment table text field or number fields?

Similarly, what is the datatype of the 3 ID fields in the related tables?

[SeaTigr]

The system name, unit type, and unit OS fields in the equipment table are number fields. The ID fields in the related tables are AutoNumber. I got the plain text of those to display properly by having the queries display the text field from the related tables.

[SeaTigr]

Hmmmm...so it sounds like I can't set up a query to return text instead of a number. I suppose the solution is to create a separate list with the divisions and make it a combo box field instead of an option group.

[jzwp11]

You are correct, you should be using combo boxes based on the respective related tables. The bound field of the combo box should be the ID, you would set the column width of the ID column to 0 and the text name to something other than zero (then that is what the user will see). I recommend using the combo box wizard to set up the combo boxes.

[SeaTigr]

Thank you for your patient help on this matter.

[jzwp11]

You're welcome.