Field Name Query Help

[TJ]

Access 2K. I need the field name in a Query to be the result of selecting
a name from a Combo Box on a Form. The form name is Main Form and the
Combo Box name is ToBrand [Forms]![Main Form]![ToBrand]. Is this possible
to set up in a Query grid?

Thanks

 

[John Spencer (MVP)]

No, it is not possible. You could use VBA to build the query string and then
use that as a query.

What are you trying to accomplish with this? If you just need the title as a
label on a form or report, that is possible.

 

[TJ]

John,

I am trying to get the Form Combo Box selection (ToBrand) into a query
field so the selection picks a column in a table when the query runs.

Thanks

No, it is not possible. You could use VBA to build the query string and then
use that as a query.

What are you trying to accomplish with this? If you just need the title as a
label on a form or report, that is possible.

Access 2K. I need the field name in a Query to be the result of selecting
a name from a Combo Box on a Form. The form name is Main Form and the
Combo Box name is ToBrand [Forms]![Main Form]![ToBrand]. Is this possible
to set up in a Query grid?

Thanks

 

[John Vinson]

Access 2K. I need the field name in a Query to be the result of selecting
a name from a Combo Box on a Form. The form name is Main Form and the
Combo Box name is ToBrand [Forms]![Main Form]![ToBrand]. Is this possible
to set up in a Query grid?

Thanks

If you're storing data - brand names, or whatever values the combo box
returns - in fieldnames, you're WAY down the wrong track! Are you?
What's the structure of the table you're querying?

John W. Vinson[MVP]

 

[TJ]

John,

I am storing Brand Names in a table called ToBrand. On a Form called Main
Menu, there is a Combo Box linked to ToBrand. The user selects the ToBrand
in the Combo Box, i.e. Brand1, Brand2, etc.

The idea is that when a certain query runs, one of the fields in the query
grid gets the ToBrand value from the Combo Box. The query queries a Table
called Cross Detail which has 220K records and 12 fields of data. Some of
the fields would be Brand1, Brand2, etc. The query would use the field in
the Cross Detail Table based on the selection in the Combo Box.

Hope this helps. Thanks

Access 2K. I need the field name in a Query to be the result of selecting
a name from a Combo Box on a Form. The form name is Main Form and the
Combo Box name is ToBrand [Forms]![Main Form]![ToBrand]. Is this possible
to set up in a Query grid?

Thanks

If you're storing data - brand names, or whatever values the combo box
returns - in fieldnames, you're WAY down the wrong track! Are you?
What's the structure of the table you're querying?

John W. Vinson[MVP]

 

[John Vinson]

John,

I am storing Brand Names in a table called ToBrand. On a Form called Main
Menu, there is a Combo Box linked to ToBrand. The user selects the ToBrand
in the Combo Box, i.e. Brand1, Brand2, etc.

The idea is that when a certain query runs, one of the fields in the query
grid gets the ToBrand value from the Combo Box. The query queries a Table
called Cross Detail which has 220K records and 12 fields of data. Some of
the fields would be Brand1, Brand2, etc. The query would use the field in
the Cross Detail Table based on the selection in the Combo Box.

Then as John Spencer says, your only choice is to build a SQL string
in code.

If you ever have to handle a thirteenth or fourteenth brand, what will
you do? Redesign your tables, all your queries, all your forms, all
your reports!? This design is simply incorrect; if you have a Many
(whatever) to Many (brands) relationship, you should have one RECORD
for each brand rather than one field.

John W. Vinson[MVP]

 

[TJ]

John,

I think I can describe things a little better.

The Cross Detail table is used to cross over a competitor's part number to
my part number. The first field in this table is called "Comp Part". Next
is "RayPart" which is my part number. Next is "Raymold" which is an
alternate part number for me. Next is "Box', then "BoxRay", then a product
group call "ProductLine", then which competitive brand called "Brand".


Table Fields:

Comp Part | RayPart | Raymold | Box | BoxRay| ProductLine | Brand | Price


Currently, the queries take "Comp Part" to "RayPart" and have worked just
fine. The company now wants to be able to choose which of our brands to
interchange the competitor's part to. I was trying to add another Field in
the Cross Detail table for another one of our brands named "Aimco". In
this way, I could take the "Comp Part" to either "RayPart" or "Aimco" based
on a user selection on the Combo Box ToBrand.

New Table Fields:

Comp Part | RayPart | Raymold | Aimco | Box | BoxRay| ProductLine | Brand |
Price

I am quite lost using most VBA code and not very good on SQL language
either.

Any suggestions?

Thanks

 

[John Vinson]

I was trying to add another Field in
the Cross Detail table for another one of our brands named "Aimco". In
this way, I could take the "Comp Part" to either "RayPart" or "Aimco" based
on a user selection on the Combo Box ToBrand.

Again:

Storing data, such as "RayPart" or "AimCo" in fieldnames is WRONG. It
will make your database *much more complex* and inflexible.

If you have a brand name - store it *AS DATA* in a field in the table,
rather than proliferating new fields for each brand.

John W. Vinson[MVP]

 

[TJ]

John,

Thx.....will do!

Tom

Again:

Storing data, such as "RayPart" or "AimCo" in fieldnames is WRONG. It
will make your database *much more complex* and inflexible.

If you have a brand name - store it *AS DATA* in a field in the table,
rather than proliferating new fields for each brand.

John W. Vinson[MVP]