run a sum query on an id in a combobox

gbmarlysis

Registered User.
Local time
Today, 18:22
Joined
Jan 30, 2012
Messages
28
Hi
i have an athlete db
i have a form with a combo box
i want a cell to tell me how many races an athlete has had once i pick the athlete in the combo box
there is also a cell with the date in it
i want the "how many races cell" to only show the number of races the athlete competed in before the date in the "date cell"
thanks
 
You could use the DCount() function in the Control Source of your textbox. something along the lines of;
Code:
=DCount("YourWinsField", "TableWithWinsRecords", "AthleteID = " & Me.YourComboName
Alternatively you could use the DCount() to create an expression in your Combo Box Row Source and simply refer to that column of the combo as the Control Source for you text box.
 
hi
could i get a bit more guidance with how to set out the expression builder. i have an athlete table linked to a race table and bothed linked to a race results table through race id and athlete id. the combo box is at the moment combo 35. i dont really get the domain etc
thanks
ps how can i add the requirement that only races before the date shown in a text box on the form are counted
 
In terms of the DCount() or any of the other Domain Aggregation functions, Domain is the table from which you wish select the data you are interested in.
 
so what would the line of code you would use look like ..
thanks
 
ok not sure what the =" & Me. means but back to the internet.
thanks for the parts you helped me with.
 
You would put that code into the Text Box's Control Source.

The = indicates that you are populating the Text Box with a calculated value. Me. is the reference to the active form. so Me.YourComboName is a reference to a combo box on your currently active form.
 
hi
cant get it to work. the combo box is on a subform and unbound could this be the issue.
 
sorry John so how would i set that out.
=dcount("athleteidField","Race Results Table", is = to the value in the combo box)
thanks
 
If the text box you are trying to populate is on the main for you will need to use the following schema to refer to the combo;
Code:
Me!YourSubFormName.YourComboName
 
the text box is on the sub form aswell, when i do Dcount("*", "race results") i get the count of the number of entries but whenever i put in any criteria to show only the rows with the athleteid that the combo box shows it doesnt work and when i put stuff in the expression it doesnt work
 
Is Athlete Id a numeric or string value?

Is Athlete Id held in the bound (zero) column of your Combo35?
 

Users who are viewing this thread

Back
Top Bottom