Combobox and Fields

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I have a table with (eg) 8 fields and about 200
numberical records in each field. I want to be able to
have the field names in a combobox so that I can select a
particular field and then output a table query with all
the values in the field multiplied by another value.

for example:

Field Names in [TableA]: FA, FB, FC, FD

First, how do I get Access to list the filed names in the
table?

Second, using the combobox, I select field [FA]. so How
would I structure the query so that I could calculate

temp:sum(10*[TableA]![combobox selected field])

or

temp:sum(10*[TableA]![FA])

Help and thanks in advance
Mark
 
Mark said:
I have a table with (eg) 8 fields and about 200
numberical records in each field. I want to be able to
have the field names in a combobox so that I can select a
particular field and then output a table query with all
the values in the field multiplied by another value.

for example:

Field Names in [TableA]: FA, FB, FC, FD

First, how do I get Access to list the filed names in the
table?
Click to expand...

set rs= server.CreateObject("ADODB.recordset")
rs.open "select * from table",source
for each rs.fields().name
x+x=1
fieldname=rs.Fields(x).Name
''put in combo
next
something like that



Second, using the combobox, I select field [FA]. so How
would I structure the query so that I could calculate

temp:sum(10*[TableA]![combobox selected field])

or

temp:sum(10*[TableA]![FA])

Help and thanks in advance
Mark
Click to expand...
 
Hi,


In general, it is not suggested (and not directly supported) to have a
"variable" field name. You can try something like:

SELECT CHOOSE(myIndex, field1, field2, field3, field4, field5)
FROM myTable

and it would return field1, ... field5 dependant on the value of myIndex.

I would not dream to use that myself... unless I would be designing a tool
for databases. In general, I would change the table design to use more
standard SQL statements.


Hoping it may help,
Vanderghast, Access MVP
 
Ok, this is turing out to be quite difficult. What about
a mixture of VBA?

If I declare X as a string and X is assigned the name of
the field, after the user selects the value in the
combobox. Is it possible to structure a query so that the
string becomes the name of the field?

(following from the other example)

query: sum(10*[tableA]!X)

with X being string that holds the name of the field?

Cheers and thanks in advance
Mark
-----Original Message-----

Mark said:
I have a table with (eg) 8 fields and about 200
numberical records in each field. I want to be able to
have the field names in a combobox so that I can select a
particular field and then output a table query with all
the values in the field multiplied by another value.

for example:

Field Names in [TableA]: FA, FB, FC, FD

First, how do I get Access to list the filed names in the
table?
Click to expand...

set rs= server.CreateObject("ADODB.recordset")
rs.open "select * from table",source
for each rs.fields().name
x+x=1
fieldname=rs.Fields(x).Name
''put in combo
next
something like that



Second, using the combobox, I select field [FA]. so How
would I structure the query so that I could calculate

temp:sum(10*[TableA]![combobox selected field])

or

temp:sum(10*[TableA]![FA])

Help and thanks in advance
Mark
Click to expand...


.
Click to expand...
 
Ok, this is turing out to be quite difficult. What about
a mixture of VBA?

If I declare X as a string and X is assigned the name of
the field, after the user selects the value in the
combobox. Is it possible to structure a query so that the
string becomes the name of the field?

(following from the other example)

query: sum(10*[tableA]!X)

with X being string that has been assigned the name of
the field?

Cheers and thanks in advance
Mark
-----Original Message-----
Hi,


In general, it is not suggested (and not directly supported) to have a
"variable" field name. You can try something like:

SELECT CHOOSE(myIndex, field1, field2, field3, field4, field5)
FROM myTable

and it would return field1, ... field5 dependant on the value of myIndex.

I would not dream to use that myself... unless I would be designing a tool
for databases. In general, I would change the table design to use more
standard SQL statements.


Hoping it may help,
Vanderghast, Access MVP


Mark said:
I have a table with (eg) 8 fields and about 200
numberical records in each field. I want to be able to
have the field names in a combobox so that I can select a
particular field and then output a table query with all
the values in the field multiplied by another value.

for example:

Field Names in [TableA]: FA, FB, FC, FD

First, how do I get Access to list the filed names in the
table?

Second, using the combobox, I select field [FA]. so How
would I structure the query so that I could calculate

temp:sum(10*[TableA]![combobox selected field])

or

temp:sum(10*[TableA]![FA])

Help and thanks in advance
Mark
Click to expand...


.
Click to expand...
 
Hi,



Use switch rather than choose, but again, a proper table design would
more elegantly solve that problem. It seems you need the NAME of the field
as data, but the field name is NOT data accessible by the database. So,
instead of


ClientID, Jan, Feb, Mar, Apr ' Fields name
1010, 1, 2, 3, 4 ' data


have


ClientID, TheName, TheValue 'Fields name
1010 Jan 1
1010 Feb 2
1010 Mar 3
1010 Apr 4


and it is then just a matter of a very general simple criteria:

SELECT TheValue FROM myTable WHERE TheName='Apr'

because the data is now just that, data.




Hoping it may help,
Vanderghast, Access MVP

Mark said:
Ok, this is turing out to be quite difficult. What about
a mixture of VBA?

If I declare X as a string and X is assigned the name of
the field, after the user selects the value in the
combobox. Is it possible to structure a query so that the
string becomes the name of the field?

(following from the other example)

query: sum(10*[tableA]!X)

with X being string that has been assigned the name of
the field?

Cheers and thanks in advance
Mark
-----Original Message-----
Hi,


In general, it is not suggested (and not directly supported) to have a
"variable" field name. You can try something like:

SELECT CHOOSE(myIndex, field1, field2, field3, field4, field5)
FROM myTable

and it would return field1, ... field5 dependant on the value of myIndex.

I would not dream to use that myself... unless I would be designing a tool
for databases. In general, I would change the table design to use more
standard SQL statements.


Hoping it may help,
Vanderghast, Access MVP


Mark said:
I have a table with (eg) 8 fields and about 200
numberical records in each field. I want to be able to
have the field names in a combobox so that I can select a
particular field and then output a table query with all
the values in the field multiplied by another value.

for example:

Field Names in [TableA]: FA, FB, FC, FD

First, how do I get Access to list the filed names in the
table?

Second, using the combobox, I select field [FA]. so How
would I structure the query so that I could calculate

temp:sum(10*[TableA]![combobox selected field])

or

temp:sum(10*[TableA]![FA])

Help and thanks in advance
Mark
Click to expand...


.
Click to expand...
Click to expand...
 
Michael,

Unfortunately this would not be possible. The reason why
I need to select the field name, is that this table has
200 fields and 6237 'rows' of data.

The calcuation I am trying to undertake is: all the
values in the table of the select field multiplied by a
value in a related table and then grouped based on values
from a third table (related). In as standard it would be
as follows:

SELECT [Food Consumption].Country_Name, Sum([FAOstat]!
[Prot_g/100g]*[Food Consumption]![GMPerPPerDayRefuse]
*0.01) AS ProJSupply
FROM (FAOstat INNER JOIN [Food Consumption] ON
FAOstat.Food_code = [Food Consumption].Food_code) INNER
JOIN [Country Total] ON ([Food Consumption].Food_code =
[Country Total].Food_Code) AND ([Food
Consumption].Country_Name = [Country Total].Country)
GROUP BY [Food Consumption].Country_Name;


Ideally, I want to select the field name with a combobox.
So insted of ([FAOstat]![Prot_g/100g]. I want to select a
any one of the fields in the FAOstat table. I have
created a combobox and bounded the combobox to the fields
of the table. The select field in the combobox is then
assigned to a module variable of "X".

Now, I want to preform the calcualtion using the selected
field. (eg)

SELECT [Food Consumption].Country_Name, Sum([FAOstat]!
[SELECTED COMBOBOX VALUE]*[Food Consumption]!
[GMPerPPerDayRefuse]*0.01)...

Is this possible?

cheers
Mark

-----Original Message-----
Hi,



Use switch rather than choose, but again, a proper table design would
more elegantly solve that problem. It seems you need the NAME of the field
as data, but the field name is NOT data accessible by the database. So,
instead of


ClientID, Jan, Feb, Mar, Apr ' Fields name
1010, 1, 2, 3, 4 ' data


have


ClientID, TheName, TheValue 'Fields name
1010 Jan 1
1010 Feb 2
1010 Mar 3
1010 Apr 4


and it is then just a matter of a very general simple criteria:

SELECT TheValue FROM myTable WHERE TheName='Apr'

because the data is now just that, data.




Hoping it may help,
Vanderghast, Access MVP

Mark said:
Ok, this is turing out to be quite difficult. What about
a mixture of VBA?

If I declare X as a string and X is assigned the name of
the field, after the user selects the value in the
combobox. Is it possible to structure a query so that the
string becomes the name of the field?

(following from the other example)

query: sum(10*[tableA]!X)

with X being string that has been assigned the name of
the field?

Cheers and thanks in advance
Mark
-----Original Message-----
Hi,


In general, it is not suggested (and not directly supported) to have a
"variable" field name. You can try something like:

SELECT CHOOSE(myIndex, field1, field2,
Click to expand...
field3,
field4, field5)
FROM myTable

and it would return field1, ... field5 dependant on
Click to expand...
the
value of myIndex.
I would not dream to use that myself... unless I would be designing a tool
for databases. In general, I would change the table design to use more
standard SQL statements.


Hoping it may help,
Vanderghast, Access MVP


I have a table with (eg) 8 fields and about 200
numberical records in each field. I want to be able to
have the field names in a combobox so that I can select a
particular field and then output a table query with all
the values in the field multiplied by another value.

for example:

Field Names in [TableA]: FA, FB, FC, FD

First, how do I get Access to list the filed names
Click to expand...
in
the
table?

Second, using the combobox, I select field [FA]. so How
would I structure the query so that I could calculate

temp:sum(10*[TableA]![combobox selected field])

or

temp:sum(10*[TableA]![FA])

Help and thanks in advance
Mark





.
Click to expand...
Click to expand...
Click to expand...
 
Hi,


The easiest and fastest way would be to write the text of the SQL statement,
as someone already proposed:


str= "SELECT CountryName, SUM(YourTableName." & Me.MyComboBoxName
& " ) As SumOfSomething, ... "

Debug.Print str

Set daoRecordset = CurrentDb.OpenRecordset(str)



The Debug.Print statement allow you to see the final SQL statement, as
text, to see if it is ok (and to cut and paste it in the query designer, if
you want really test it, to further "debug" it, if required...). Here, you
should be able to see the field name hold in the control Me.MyComboBoxName
"im-printed" in the string.

Note that we generally use a dot between the table name and its field,
in SQL, not a bang.




Hoping it may help,
Vanderghast, Access MVP



Mark said:
Michael,

Unfortunately this would not be possible. The reason why
I need to select the field name, is that this table has
200 fields and 6237 'rows' of data.

The calcuation I am trying to undertake is: all the
values in the table of the select field multiplied by a
value in a related table and then grouped based on values
from a third table (related). In as standard it would be
as follows:

SELECT [Food Consumption].Country_Name, Sum([FAOstat]!
[Prot_g/100g]*[Food Consumption]![GMPerPPerDayRefuse]
*0.01) AS ProJSupply
FROM (FAOstat INNER JOIN [Food Consumption] ON
FAOstat.Food_code = [Food Consumption].Food_code) INNER
JOIN [Country Total] ON ([Food Consumption].Food_code =
[Country Total].Food_Code) AND ([Food
Consumption].Country_Name = [Country Total].Country)
GROUP BY [Food Consumption].Country_Name;


Ideally, I want to select the field name with a combobox.
So insted of ([FAOstat]![Prot_g/100g]. I want to select a
any one of the fields in the FAOstat table. I have
created a combobox and bounded the combobox to the fields
of the table. The select field in the combobox is then
assigned to a module variable of "X".

Now, I want to preform the calcualtion using the selected
field. (eg)

SELECT [Food Consumption].Country_Name, Sum([FAOstat]!
[SELECTED COMBOBOX VALUE]*[Food Consumption]!
[GMPerPPerDayRefuse]*0.01)...

Is this possible?

cheers
Mark

-----Original Message-----
Hi,



Use switch rather than choose, but again, a proper table design would
more elegantly solve that problem. It seems you need the NAME of the field
as data, but the field name is NOT data accessible by the database. So,
instead of


ClientID, Jan, Feb, Mar, Apr ' Fields name
1010, 1, 2, 3, 4 ' data


have


ClientID, TheName, TheValue 'Fields name
1010 Jan 1
1010 Feb 2
1010 Mar 3
1010 Apr 4


and it is then just a matter of a very general simple criteria:

SELECT TheValue FROM myTable WHERE TheName='Apr'

because the data is now just that, data.




Hoping it may help,
Vanderghast, Access MVP

Mark said:
Ok, this is turing out to be quite difficult. What about
a mixture of VBA?

If I declare X as a string and X is assigned the name of
the field, after the user selects the value in the
combobox. Is it possible to structure a query so that the
string becomes the name of the field?

(following from the other example)

query: sum(10*[tableA]!X)

with X being string that has been assigned the name of
the field?

Cheers and thanks in advance
Mark

-----Original Message-----
Hi,


In general, it is not suggested (and not directly
supported) to have a
"variable" field name. You can try something like:

SELECT CHOOSE(myIndex, field1, field2, field3,
field4, field5)
FROM myTable

and it would return field1, ... field5 dependant on the
value of myIndex.

I would not dream to use that myself... unless I would
be designing a tool
for databases. In general, I would change the table
design to use more
standard SQL statements.


Hoping it may help,
Vanderghast, Access MVP


I have a table with (eg) 8 fields and about 200
numberical records in each field. I want to be able to
have the field names in a combobox so that I can
select a
particular field and then output a table query with all
the values in the field multiplied by another value.

for example:

Field Names in [TableA]: FA, FB, FC, FD

First, how do I get Access to list the filed names in
the
table?

Second, using the combobox, I select field [FA]. so How
would I structure the query so that I could calculate

temp:sum(10*[TableA]![combobox selected field])

or

temp:sum(10*[TableA]![FA])

Help and thanks in advance
Mark





.
Click to expand...
Click to expand...
Click to expand...
 
Michael,

This is working fine (I think) I have taken the text and
put it into a module. However, and this shows my
ignorance, how do I display the query? the last command

Set daoRecordset = CurrentDb.OpenRecordset (str)

does not seem to hang, but how can I get it to present
the results of the query.

Very grateful so far

kind regards
Mark

-----Original Message-----
Hi,


The easiest and fastest way would be to write the text of the SQL statement,
as someone already proposed:


str= "SELECT CountryName, SUM(YourTableName." & Me.MyComboBoxName
& " ) As SumOfSomething, ... "

Debug.Print str

Set daoRecordset = CurrentDb.OpenRecordset(str)



The Debug.Print statement allow you to see the final SQL statement, as
text, to see if it is ok (and to cut and paste it in the query designer, if
you want really test it, to further "debug" it, if required...). Here, you
should be able to see the field name hold in the control Me.MyComboBoxName
"im-printed" in the string.

Note that we generally use a dot between the table name and its field,
in SQL, not a bang.




Hoping it may help,
Vanderghast, Access MVP



Mark said:
Michael,

Unfortunately this would not be possible. The reason why
I need to select the field name, is that this table has
200 fields and 6237 'rows' of data.

The calcuation I am trying to undertake is: all the
values in the table of the select field multiplied by a
value in a related table and then grouped based on values
from a third table (related). In as standard it would be
as follows:

SELECT [Food Consumption].Country_Name, Sum([FAOstat]!
[Prot_g/100g]*[Food Consumption]![GMPerPPerDayRefuse]
*0.01) AS ProJSupply
FROM (FAOstat INNER JOIN [Food Consumption] ON
FAOstat.Food_code = [Food Consumption].Food_code) INNER
JOIN [Country Total] ON ([Food Consumption].Food_code =
[Country Total].Food_Code) AND ([Food
Consumption].Country_Name = [Country Total].Country)
GROUP BY [Food Consumption].Country_Name;


Ideally, I want to select the field name with a combobox.
So insted of ([FAOstat]![Prot_g/100g]. I want to select a
any one of the fields in the FAOstat table. I have
created a combobox and bounded the combobox to the fields
of the table. The select field in the combobox is then
assigned to a module variable of "X".

Now, I want to preform the calcualtion using the selected
field. (eg)

SELECT [Food Consumption].Country_Name, Sum([FAOstat]!
[SELECTED COMBOBOX VALUE]*[Food Consumption]!
[GMPerPPerDayRefuse]*0.01)...

Is this possible?

cheers
Mark

-----Original Message-----
Hi,



Use switch rather than choose, but again, a proper table design would
more elegantly solve that problem. It seems you need
Click to expand...
the
NAME of the field
as data, but the field name is NOT data accessible by the database. So,
instead of


ClientID, Jan, Feb, Mar, Apr ' Fields name
1010, 1, 2, 3, 4 ' data


have


ClientID, TheName, TheValue 'Fields name
1010 Jan 1
1010 Feb 2
1010 Mar 3
1010 Apr 4


and it is then just a matter of a very general simple criteria:

SELECT TheValue FROM myTable WHERE TheName='Apr'

because the data is now just that, data.




Hoping it may help,
Vanderghast, Access MVP

Ok, this is turing out to be quite difficult. What about
a mixture of VBA?

If I declare X as a string and X is assigned the
Click to expand...
name
of
the field, after the user selects the value in the
combobox. Is it possible to structure a query so
Click to expand...
that
the
string becomes the name of the field?

(following from the other example)

query: sum(10*[tableA]!X)

with X being string that has been assigned the name of
the field?

Cheers and thanks in advance
Mark

-----Original Message-----
Hi,


In general, it is not suggested (and not directly
supported) to have a
"variable" field name. You can try something like:

SELECT CHOOSE(myIndex, field1, field2, field3,
field4, field5)
FROM myTable

and it would return field1, ... field5 dependant
Click to expand...
on
the
value of myIndex.

I would not dream to use that myself... unless I would
be designing a tool
for databases. In general, I would change the table
design to use more
standard SQL statements.


Hoping it may help,
Vanderghast, Access MVP


I have a table with (eg) 8 fields and about 200
numberical records in each field. I want to be
Click to expand...
able
to
have the field names in a combobox so that I can
select a
particular field and then output a table query
Click to expand...
with
all
the values in the field multiplied by another value.

for example:

Field Names in [TableA]: FA, FB, FC, FD

First, how do I get Access to list the filed
Click to expand...
names
in
the
table?

Second, using the combobox, I select field [FA].
Click to expand...
so
How
would I structure the query so that I could calculate

temp:sum(10*[TableA]![combobox selected field])

or

temp:sum(10*[TableA]![FA])

Help and thanks in advance
Mark





.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top