An expression used in a to reference a value in a combobox

[Evon]

I would like to write an expression in a control on a report that references a particular value in a combobox column/list. Please, can someone help, it is holding up my project.

I want to filter each like value and total each set.

 

[Isskint]

To reference a control on an open form use:

Forms!FormName!ControlName

if the control is on a subform then Forms!FormName!SubformName.Form!ControlName

Now with a combobox (or Listbox) where you want to return a value other than the bound column, then use the Columns() property and count the columns from 0. So to reference the third column of a combobox, use:

Forms!FormName!ControlName.Columns(2)

 

[Evon]

Thanks Isskint, but let me explain some more: I have a combobox with just 1 column and 11 rows of values in the list. When I select a row/value, it is stored ln a field in a table. This table is used with another to create a query. This query is used to create a report.

Now, I need the report to count all the like values in the field/column where the combo stores them, to get a total count for each value. But because a combobox only stores values in one field, I cannot use the count value by itself, because if I did I would just get the total of all the records in that column. That is not what I want. I want the report to be able to count only the like values in the column and get a total for each set.

 

[Isskint]

DCount("Field","TableName","Field=" & ValueToCount)

Or for a text field

DCount("Field","TableName","Field='" & ValueToCount & "'")

ValueToCount can either be a fixed value or referenced from a control (such as the combobox if that will be active when the report/query is run).

Another option would be to create a query listing distinct values in that field (SELECT DISTINCT query) and use the dCount() function against each value returned.

 

[Evon]

To reference a control on an open form use:



Forms!FormName!ControlName.Columns(2)

To use your example, I would need an addition to the expression that would target a specefic row in the column which I could then use the Count function on viz:Forms!FormName!ControlName.Columns(2).row2

 

[Isskint]

Forms!FormName!ControlName.Columns(2) will reference the selected row so row parameter not required.

However if you do need to reference a specific row/column in a combobox then use:

Forms!FormName!ControlName.Columns(c,r) where c = column and r=row

 

[Evon]

Isskint, that did not work for me. However, remember that the combo is just the vehicle by which I enter records into a table. The table is also included in the query that my report is based on.
The problem really is that all the records are stored in one field/column and I am not able to use the count overall on the field.

I was fooling around with filtering last night. Something is filtering, but to achieve what I want I would need to use about 11 different filters for each field on the report where I want separate totals for each group filtered. The database apparently only allows one filter. What I do is use the same filter but change the parameters to filter the values I want. However, the filter does not recognize the changes so all the filtered fields are giving the same output.

 

[Isskint]

Just trying to get my head around this. What i am thinking you want is to count up how many values of X you have in a given field. As such the Dcount() should do the trick.

Can you post some example data? Preferably a copy of your DB with only the releavant tables, queries, forms?

 

[Evon]

I really appreciate your interest. However, the copy I would send you is on another computer at home and unfortunately I am now at work.

Anyway, I will send you a link to one so you can see what I want to do.

 

[Evon]

Just trying to get my head around this. What i am thinking you want is to count up how many values of X you have in a given field. As such the Dcount() should do the trick.

Can you post some example data? Preferably a copy of your DB with only the releavant tables, queries, forms?

If you could give me a PM Link or email address I would send you a link to the DB. I get the feeling that it would be a breeze for you.

 

[Evon]

DCount("Field","TableName","Field=" & ValueToCount)

Another option would be to create a query listing distinct values in that field (SELECT DISTINCT query) and use the dCount() function against each value returned.

I just re-read your reply and this is exactly how I have it now. So there is a combo for each value with just one row per combo. However, my client wants just one combobox with all the values listed. Hence my needing help. IT is funny, because on the face of it it seems so simple.

I even tried to write expressions for each value in the query that the report is based on. However, the query keeps grouping them back as one list.

 

[Evon]

I only need one more post after this, then I can post a link.

 

[Evon]

And this is number ten so I can post a link to the db in my next post

 

[Evon]

Isskint, the db is here

 

[Isskint]

OK, i will have a look tomorrow (only have A2003 at work !!)

 

[Isskint]

Which forms, queries,reports am i looking for?

 

[Evon]

OK:
Firstly, just for an overview you could go:
"Switchboard Form">"Open CIM Module" here you will see the main form and the 11 combos which I would like to replace with just 1.

(You could probably play around with some of the buttons there, and you will see that this is a form to help monitor a dietetics facility at three hospitals viz. KPH, VJH & NCH.

Now the database objects involved are:
Table: "Diet"
Query: "Diet by Wards"
Report: "Diet by Ward"

 

[Evon]

Anything new

 

[Isskint]

been waylaid:banghead: looking at it tonight

 

[Evon]

My, My that does not sound so bad. Wish I could get waylaid myself