vb Procedure/process coding help

  • Thread starter Thread starter umpire_43
  • Start date Start date
U

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
 
Put the calculation into a query, and use the same query for the form and
report.
 
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
 
Pretty hard to give direction with such a generic problem!

Put the calculation into a function and use the function in the query.
 
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
..
 
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
 
Back
Top