form control based on a query result
form control based on a query result
(OP)
hi all
i would like to have a control on my form display a total based on the result of select query that does a Sum of a field in the underlying table.
i have the query working, it does the Sum, but i can't seem to get the result to be the control source for the control on the form.
i know there is an easy way to do this, but all i get is the #Name? error.
where did i go wrong....
if i do this as a script (event)inside the form would it be better?
thanks!
i would like to have a control on my form display a total based on the result of select query that does a Sum of a field in the underlying table.
i have the query working, it does the Sum, but i can't seem to get the result to be the control source for the control on the form.
i know there is an easy way to do this, but all i get is the #Name? error.
where did i go wrong....
if i do this as a script (event)inside the form would it be better?
thanks!
Talk To Other Members
RE: form control based on a query result
John A. Gilman
gms@uslink.net
RE: form control based on a query result
No unfortunately not ;
the other snag is that it would be nice for the results to be current without rerunning the query.
i cant seem to get the source to be the query, i cant get it to dump the query result into a field for retrieval either.
i would really like to do it in code so that the DB is a neater package too. i have lots of other events such as loging current user, time, appending new notes to memo fields, etc, but this one is giving me grief.....
thanks
RE: form control based on a query result
create a new module "basResults"
paste this function into the module
Public Function qrySum() As String
qrySum = DLookup("fieldName", "queryName")
End Function
Replace "queryName" with the name of your query.
Replace "fieldName" with the name of the field containing your summary results you want displayed on your form. (open the query in data sheet view to verify the name)
Then with your form in design view, type this
into the text box you want to display the results.
=qrySum()
save the form and view it. The summary your looking for should display.
John A. Gilman
gms@uslink.net
RE: form control based on a query result
works great, now i just need to have it display the results in the correct format ( ie 2 decimal cuurency - formating the control on the form didn't work )
this will save me loads of processing and printing queries for staff
thanks again!
RE: form control based on a query result
The first example used this...
Public Function qrySum() As String
Try this...
Public Function qrySum() As Integer
Then format your field to currency or fixed and set the decimal.
John
John A. Gilman
gms@uslink.net