vb Procedure/process coding help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

umpire_43 - 17 Mar 2008 22:10 GMT

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

Reply to this Message

Douglas J. Steele - 17 Mar 2008 22:52 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi there,
>
[quoted text clipped - 15 lines]
> Thanks
> Keith

Reply to this Message

umpire_43 - 17 Mar 2008 23:54 GMT

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

> Put the calculation into a query, and use the same query for the form and
> report.
[quoted text clipped - 18 lines]
> > Thanks
> > Keith

Reply to this Message

Douglas J. Steele - 18 Mar 2008 12:07 GMT

Pretty hard to give direction with such a generic problem!

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi Doug,
>
[quoted text clipped - 36 lines]
>> > Thanks
>> > Keith

Reply to this Message

John Spencer - 18 Mar 2008 12:22 GMT

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

Signature

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

> Hi Doug,
>
[quoted text clipped - 36 lines]
>> > Thanks
>> > Keith

Reply to this Message

umpire_43 - 18 Mar 2008 21:21 GMT

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

> 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
[quoted text clipped - 55 lines]
> >> > Thanks
> >> > Keith

Reply to this Message