form on open calculation problem

[Aravind]

Hi folks.

I have a form, frmHistory, that has the following code for its On Open
event.

=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=
Private Sub Form_Open(Cancel As Integer)

Dim NumDays As Integer

'calculate days overdue, if any
NumDays = DateDiff("d", Me.DateDue, Me.DateReturn)

'reset negatives
If NumDays < 0 Then
NumDays = 0
End If

'calculate & set fine based on member type & number of overdue days
'for staffs, fine = $1.00 / day overdue
If (Me.MemberType = "Staff") And (NumDays > 0) Then
Me.TotalFine = NumDays
End If

'for students, fine = $0.50 / day overdue
If (Me.MemberType = "Student") And (NumDays > 0) Then
Me.TotalFine = NumDays * 0.5
End If

End Sub
=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=

What the coding does (or should do) is to calculate the fine a member
has to pay based on the member type and the number of overdue days.
Thus, if frankie is a staff and he is overdue by 3 days, he has to pay
$3.00. But if frankie is a student and he is overdue by 3 days, he has
to pay $1.50.

The problem is that the coding doesn't work. Anyone has any idea why?
Is it because I set the Default View of the form to "Continuous
Forms"? If that is the case then how do I rectify the situation? I'm
using Access 97, if that's important. Thank you.

[WindAndWaves]

I would put the calculations in a query (or several) and run the query
Form_open and put the answers in a special field.

Your calculation only does it for the current record.

Nicolaas
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

[PC Datasheet]

Put your code n the OnActivate event of the form rather than the OnOpen event.
When the OnOpen event fires, the field values are already set and the form does
not go back and recalculate them.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Aravind" <so**********@hotmail.com> wrote in message
news:61**************************@posting.google.c om...

Hi folks.

I have a form, frmHistory, that has the following code for its On Open
event.

=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=
Private Sub Form_Open(Cancel As Integer)

Dim NumDays As Integer

'calculate days overdue, if any
NumDays = DateDiff("d", Me.DateDue, Me.DateReturn)

'reset negatives
If NumDays < 0 Then
NumDays = 0
End If

'calculate & set fine based on member type & number of overdue days
'for staffs, fine = $1.00 / day overdue
If (Me.MemberType = "Staff") And (NumDays > 0) Then
Me.TotalFine = NumDays
End If

'for students, fine = $0.50 / day overdue
If (Me.MemberType = "Student") And (NumDays > 0) Then
Me.TotalFine = NumDays * 0.5
End If

End Sub
=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=

What the coding does (or should do) is to calculate the fine a member
has to pay based on the member type and the number of overdue days.
Thus, if frankie is a staff and he is overdue by 3 days, he has to pay
$3.00. But if frankie is a student and he is overdue by 3 days, he has
to pay $1.50.

The problem is that the coding doesn't work. Anyone has any idea why?
Is it because I set the Default View of the form to "Continuous
Forms"? If that is the case then how do I rectify the situation? I'm
using Access 97, if that's important. Thank you.

[Bob Quintal]

"PC Datasheet" <sp**@nospam.spam> wrote in
news:Xc*******************@newsread3.news.atl.eart hlink.net:

Put your code n the OnActivate event of the form rather than
the OnOpen event. When the OnOpen event fires, the field
values are already set and the form does not go back and
recalculate them.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Even in the OnActivate event you will only get the calculation done
for the first visible record. In hte on current event will show the
current fine in all rows.

Since the OP specified he wanted continuous forms, the only way to
get trustable answers is to put the calculation in a query.

Aravind, you need to move the code to a module, convert it to a
public function, and call it as an expression in the query that
becomes the recordsource for your form.

Bob Quintal


"Aravind" <so**********@hotmail.com> wrote in message
news:61**************************@posting.google.c om...

Hi folks.

I have a form, frmHistory, that has the following code for
its On Open event.

=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=
Private Sub Form_Open(Cancel As Integer)

Dim NumDays As Integer

'calculate days overdue, if any
NumDays = DateDiff("d", Me.DateDue, Me.DateReturn)

'reset negatives
If NumDays < 0 Then
NumDays = 0
End If

'calculate & set fine based on member type & number of
overdue days 'for staffs, fine = $1.00 / day overdue
If (Me.MemberType = "Staff") And (NumDays > 0) Then
Me.TotalFine = NumDays
End If

'for students, fine = $0.50 / day overdue
If (Me.MemberType = "Student") And (NumDays > 0) Then
Me.TotalFine = NumDays * 0.5
End If

End Sub
=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=

What the coding does (or should do) is to calculate the fine
a member has to pay based on the member type and the number
of overdue days. Thus, if frankie is a staff and he is
overdue by 3 days, he has to pay $3.00. But if frankie is a
student and he is overdue by 3 days, he has to pay $1.50.

The problem is that the coding doesn't work. Anyone has any
idea why? Is it because I set the Default View of the form to
"Continuous Forms"? If that is the case then how do I rectify
the situation? I'm using Access 97, if that's important.
Thank you.

[Rob]

I agree with Nicolaas. If you want to display it in continuous form,
then put the code in a query. If not, your code would work fine on a
"single form" view, just put the code in the CURRENT Event. THen as
they move from record to record, it would recalculate fine.

[Wayne Gillespie]

On Mon, 24 May 2004 12:51:03 GMT, "PC Datasheet" <sp**@nospam.spam> wrote:

Put your code n the OnActivate event of the form rather than the OnOpen event.
When the OnOpen event fires, the field values are already set and the form does
not go back and recalculate them.

Actually this is not entirely correct. When OnOpen fires the data has not yet been loaded therefore referencing a bound
control on the form will fail as there is no data present. You are correct in saying that moving the code to a later
event should solve the problem. I would use OnLoad unless the values need to be recalculated when focus returns to the
form from another object. In this case OnActivate would be the choice.
Wayne Gillespie
Gosford NSW Australia