form control based on a query result

[ray436]

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!

 

[Guest_imported]

I dont suppose the form is based on this query is it?

 

[ray436]

Hi JohnneyRingo,

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

 

[Guest_imported]

Give this a try,
create a new module "basResults"
paste this function into the module


Public Function qrySum() As String

qrySum = DLookup("fieldName", "queryName&quot

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.

 

[ray436]

thanks John!

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!

 

[Guest_imported]

I think you could just make the following change.

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
[sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]