446,224 Members | 1,201 Online Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

# form on open calculation problem

 P: n/a 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. Nov 13 '05 #1
5 Replies

 P: n/a 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 Nov 13 '05 #2

 P: n/a 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" 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. Nov 13 '05 #3

 P: n/a "PC Datasheet" 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" 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. Nov 13 '05 #4

 P: n/a 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. Nov 13 '05 #5

 P: n/a On Mon, 24 May 2004 12:51:03 GMT, "PC Datasheet" 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 doesnot 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 Nov 13 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion. 