Using a form as a field name

[Guest]

I have a table that has multiple fields Z-1, Z-2, Z-3,......Z-21. I know you
can rename a field by Zone:Z-1. I would like to be able to use a form so I
can use a combo box to change to any of the Z-1 threw Z-21. I tried using
Zone: Forms![frmbyPlant]![Combo0] and this did not work. All I get is the
number in the combo box and not as a variable to make it the field name. Can
anyone help please? Thanks! Jeff

 

[Guest]

It looks like your table structure is un-normalized. If you want to pull a
specific field based on a selection with a combo box, you should first
normalize your table with a union query:

SELECT ID, [Z-1] as Z, 1 as ZField
FROM tblNoName
UNION ALL
SELECT ID, [Z-2], 2
FROM tblNoName
UNION ALL
SELECT ID, [Z-3], 3
FROM tblNoName
UNION ALL
SELECT ID, [Z-4], 4
FROM tblNoName
UNION ALL
....
SELECT ID, [Z-21], 21
FROM tblNoName;
You can then use the combo box to query again the ZField

 

[Guest]

Thank you! I will do that. I did not set up the database I was just asked to
help get some reports out of it. They did everything wrong that you could
possibly do in setting up a database. Everything is in one table and the have
more than one copy of the database floating around. Thanks again!

It looks like your table structure is un-normalized. If you want to pull a
specific field based on a selection with a combo box, you should first
normalize your table with a union query:

SELECT ID, [Z-1] as Z, 1 as ZField
FROM tblNoName
UNION ALL
SELECT ID, [Z-2], 2
FROM tblNoName
UNION ALL
SELECT ID, [Z-3], 3
FROM tblNoName
UNION ALL
SELECT ID, [Z-4], 4
FROM tblNoName
UNION ALL
...
SELECT ID, [Z-21], 21
FROM tblNoName;
You can then use the combo box to query again the ZField
--
Duane Hookom
Microsoft Access MVP

I have a table that has multiple fields Z-1, Z-2, Z-3,......Z-21. I know you
can rename a field by Zone:Z-1. I would like to be able to use a form so I
can use a combo box to change to any of the Z-1 threw Z-21. I tried using
Zone: Forms![frmbyPlant]![Combo0] and this did not work. All I get is the
number in the combo box and not as a variable to make it the field name. Can
anyone help please? Thanks! Jeff