vb Procedure/process coding help

[umpire_43]

Hi there,

I'm hoping someone can help me on this?

I think i'm brain dead here!!!

I have complex calculation within text boxes on a form that same information
needs to go to a report.

If at any time the calculation changes i don't want to change the formula
twicetimes. (one on the form and one on the report). My theory is once and
done concept.

Could you tell me what i need to do to have the report and form interact
with the same process/procedure?

Thanks
Keith

 

[Douglas J. Steele]

Put the calculation into a query, and use the same query for the form and
report.

 

[umpire_43]

Hi Doug,

Thanks for the quick reply.

I would agree with you by setting up a query but my calculations is very
complex. It has several nested if then statements which gives me an error
more than 1024 characters if i put it on a query on access.

Thats why i was thinking setting up a vb program that will lookup the
procedure no matter if the report or the form is run.

can you give some kind of direction?

thanks keith

 

[Douglas J. Steele]

Pretty hard to give direction with such a generic problem!

Put the calculation into a function and use the function in the query.

 

[John Spencer]

Write a function that does the calculation and save the function in a VBA
module instead of a class module. Then pass all the arguments (values) you
need into the function. You should then be able to call the function from
a query, a form, or a report.

Simple sample
Public Function fGetSpecialValue (arg1, arg2, arg3)

If IsNull(Arg1 + Arg2 + Arg3) Then
fGetSpecialValue = Null
Else IsNumeric(Arg1) and IsNumeric(Arg2) and IsNumeric(Arg3) = False
then
fGetSpecialValue = Null
Else
fGetSpecialValue = Arg1 * (Arg2 + Arg3)
End if

End Function

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[umpire_43]

Hi John,

Thanks for the quick reply.

I was wondering if you have a link to a site that might give me more
information and good example of the functions in vba?

Thanks