Field from Combobox

[El-d]

Hi All,

I'm probably being stupid (new to this) but is there a way to select a query field from a combobox?

I have a Table (History)that contains

Record_date , Comp1 , Comp2 , Comp3 etc
1/1/13 , 1234, 2345, 3456 etc
2/1/13 , 1214, 2355, 3466 etc

Basically a table of values for each Company for a set date.

If I select my field as Comp1, comp2 etc in my query it will return the values by date for the selected field. Which is what I need.

I've created a form that has a combo with a dropdown of all the companies but I can't get the field to use the selected value.

I run the query from my form After Update and my query field is an expression
Expr1: History.[Forms]![Chart]![Combo3].[AfterUpdate]

The idea being that I select a company on my dropdown that runs a query giving me a history of prices that will be plotted as a simple line chart.

Sure its very simple but I just can't seem to find what I'm doing wrong/ example of it working.

Thanks,

EL-d

 

[Cronk]

The problem is that your database is not normalised.

There should be two tables, tblCompanies and tblReadings
tblCompanies
CompanyID Autonumber PK
CompanyName Text
..fields for other company paramaters

tbReadings
ReadingID Autonumber PK
CompanyID Long FK into tblCompanies
Reading

Then it is easy to extract data by setting the criteria to the particular company ID.

To get the results you want with your current set up is possible but far too difficult. (And I personally like easy.)

 

[El-d]

Thanks.
Think I need to look into design a bit more but does seem bizarre to me that its hard to query from a drop down list.

El-d

 

[Cronk]

Indeed it is easy to search any column of a drop down box. The issue which your table design, is determining which column to search.

 

[El-d]

Not going to get a chance to look until later but could I pass the selection as a variable from the combo box into the query via script?