Accessing Public variables

[Confusion]

Not sure if this is the right place to post this or not - if it's in the wrong place please let me know and I'll shift it!

Basic problem...

In a forms class module some calculations are made, the result of one of which is then passed to a publically declared variable in the modules header.

When a button on the form is pressed, a report is opened. The report is made up of various fields, some taken from the form the button is on, some from a table, but most importantly, One is the variable publically declared in the form module.

Problem is - it cant access it - it just pops up an input box asking for the variable!

Below is the basic bits of code...

In the form...

Option Compare Database
Option Explicit
Public IPTcalc as double
----------------------------------

Private Sub CalcRefund_Click()
Dim response

If Me![NotRenew] = False Then
Me![PeriodCover] = Me![Cancelled] - Me![OnRiskFrom]
Me![PeriodPremium] = ((Me![Premium] / 365) * Me![PeriodCover]) * -1
IPTcalc = Me![PeriodPremium] * Me![IPT]
DoCmd.OpenReport "rptrefund", acNormal
Else
Response = MsgBox("This policy was cancelled at renewal" _
            & Chr(10) & "No Refund is Due", vbCritical, "STOP!")
End If
End Sub

I then have a report "rptrefund" with a field in it that is calculated using the public variable IPTcalc....

me![refundexcess] = me![baseprem] - IPTcalc

only that's where it goes pear shaped it pops up the box asking me to input IPTcalc instead!!

I've got similar problems in a couple of places - this is the simplest one so thought I'd start there. Am I totally misunderstanding how/what public variables are for, or just implementing it wrong?

I've actually found a way around it for the above example by adding a unbound field named "IPTamnt" into the form, assigning the figure that would have been IPTcalc to the field and then changing the code in the report to read

me![refundexcess] = me![baseprem] - [forms]![frmrefund]![IPTamnt]

but that seems somewhat messy to me and is not really viable in other areas where I need to access variables from all over the place!

 

[Mile-O]

if it's in the wrong place please let me know and I'll shift it!

How you going to do that?

Problem is - it cant access it - it just pops up an input box asking for the variable!

Of course you can't access it. You can't declare a variable as public within a Class module as it is "enclosed" within it - declare it as public in a standalone module.

Option Compare Database
Option Explicit
Public IPTcalc as double

So, move the Option Explicit line to the top - it should always come first. And create a new module and put Public IPTcalc As Double in the declarations section of this new module.

There are also a few things to be addressed within your code:

• You consistently use Me! which is not as good, in this instance, as using Me. - a good habit to get into.
• I don't know if you are referring to fields or to controls bound to fields. A better practice is to give your controls a proper prefix (such as txt for textbox, cbo for combobox, etc.) and then refer to them like so: Me.txtTextbox
• Response is dimensioned as a variable - save memory by dimensioning it as a byte - Dim Response As Byte.

 

[Confusion]

How you going to do that?

LOL, OK, OK, I meant I'll ask a mod to

Of course you can't access it. You can't declare a variable as public within a Class module as it is "enclosed" within it - declare it as public in a standalone module.

Can you see the word "dumb" written accross my forehead from there?

In my defense, I've not been doing this long, it's been a very steep learning curve and I was trying to work this after 17 hours solid in front of the PC

So, move the Option Explicit line to the top - it should always come first. And create a new module and put Public IPTcalc As Double in the declarations section of this new module

Now this bit....... the option compare database and option explicit automatically appeared when I hit "build" in the buttons onclick event, so every form that has a class modulte in it has that at the top (they were all automatically created first time I hit build somewhere) Is this bad then?!! could you elaborate as to why so I can understand better and add it to my notes please?

On the rest of the tips and hints, Many thanks! - I'm self taught so teaching myself bad habits very quickly!!! As you see more of my code you'll probably be laughing your head off, it aint the neatest of stuff

 

[Mile-O]

Sorry, Option Explicit should go before any procedures. I just have a habit of putting it atop every module.

A few other things I've noticed on second glance.

You divide the premium by 365. Would you have done this last year?

Maybe the DateDiff function to get the exact number of days in a year would be better.

i.e.

DateDiff("d", DateSerial(2004, 1, 1), DateSerial(2004, 12, 31)) + 1

Now, on to the report, you seem to be storing the calculation - a result of the premium multiplied by the Insurance Premium Tax - in a variable and then trying to use it in the report.

Why not just multiply the two fields together in a query and use the query as the recordsource for the report?

 

[Confusion]

You divide the premium by 365. Would you have done this last year?

LOL, yeah I did, because when I wrote the majority of this bit of software I had about 1 week to learn and programme it my boss seemed to prefer a few annomolies to paying someone who knew what they were doing

I did try to get the DateDiff function to work, but couldn't quite grasp that one - these reports need to be run on a daily basis for the foreseeable future - in the code you used you have an explicit date in using 2004, how would this work next year, year after etc? or am i yet again misunderstanding how the function works?!

Now, on to the report, you seem to be storing the calculation - a result of the premium multiplied by the Insurance Premium Tax - in a variable and then trying to use it in the report.

Sorry, that's me being lazy with my posting - I only used one example of it's use, it's in fact used many times througout several reports for further calculations.

Also, almost all places it's resulting calc's are used are in unbound fields which reset everytime the form is reopened.

Originally it was in a query as there were only a couple of uses, but once the boss had seen how usefull the original simple database it was expanded.... on a daily basis I ended up getting so confused with all the levels of queries that I had I thought it would be simpler to have code where needed - bad approach?

 

[Mile-O]

A little function to place in a standalone module...

Public Function DaysInYear(ByVal intYear As Integer) As Integer
    Dim dteStart As Date, dteEnd As Date
    dteStart = DateSerial(intYear, 1, 1)
    dteEnd  = DateSerial(intYear, 12, 31)
    DaysInYear = DateDiff("d",dteStart, dteEnd) + 1
End Function

You can amend this line:

Me![PeriodPremium] = ((Me![Premium] / 365) * Me![PeriodCover]) * -1

to:

Me.PeriodPremium = -((Me.Premium / DaysInYear([b]insert year here[/b])) * Me.PeriodCover)

 

[Confusion]

cheers mate, appreciate that

 

[Mile-O]

Of course, I wouldn't hard code a year into the insert year here part. If you have a date, pass that within the Year() function.

i.e.

Me.MyDateField is the date field

DaysInYear(Year(Me.MyDateField))

 

[Confusion]

LOL, good at coding, and a mind reader too

Nearly finished putting it in, got sidetracked as another "issue" has arisen (I made the classic mistake of underestimating how stupid other users can be )