By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,467 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

looping problem

P: n/a
Hi folks.

I have a form, frmHistory, whose Default View is set to Continuos
Forms.
The following is the coding that I inserted in frmHistory's On Open
event; it is intended to calculate the number of overdue days
(DaysOverdue [unbound]) for each record. Unfortunately, it only works
on the 1st record, and all subsequent DaysOverdue value of the
subsequent records are set to the 1st record's value. If I change or
delete the 1st record's value, all subsequent records reflect this new
value. Where did I go wrong? I've checked out other posts, but am
unable to find a solution. Thanks for your help.

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
Private Sub Form_Open(Cancel As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("history")

rst.MoveFirst
Do Until rst.EOF
If (IsNull(Me.Return)) And (Me.Due < Date) Then
Me.DaysOverdue = DateDiff("d", Me.Due, Date)
End If

If (IsNull(Me.Return) = False) And (Me.Due < Date) Then
Me.DaysOverdue = DateDiff("d", Me.Due, Me.Return)
End If

If Me.DaysOverdue < 0 Then
Me.DaysOverdue = 0
End If

rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Create a query to use as the source for your form.

In query design, type something like this into the field row:

DaysOverdue: CLng(IIf([Due] < Date(), DateDiff("d", [Due], Nz([Return],
Date())), 0))

For more information on creating calculated fields, see:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

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

I have a form, frmHistory, whose Default View is set to Continuos
Forms.
The following is the coding that I inserted in frmHistory's On Open
event; it is intended to calculate the number of overdue days
(DaysOverdue [unbound]) for each record. Unfortunately, it only works
on the 1st record, and all subsequent DaysOverdue value of the
subsequent records are set to the 1st record's value. If I change or
delete the 1st record's value, all subsequent records reflect this new
value. Where did I go wrong? I've checked out other posts, but am
unable to find a solution. Thanks for your help.

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
Private Sub Form_Open(Cancel As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("history")

rst.MoveFirst
Do Until rst.EOF
If (IsNull(Me.Return)) And (Me.Due < Date) Then
Me.DaysOverdue = DateDiff("d", Me.Due, Date)
End If

If (IsNull(Me.Return) = False) And (Me.Due < Date) Then
Me.DaysOverdue = DateDiff("d", Me.Due, Me.Return)
End If

If Me.DaysOverdue < 0 Then
Me.DaysOverdue = 0
End If

rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''

Nov 13 '05 #2

P: n/a
Hi Aravind

Your correct in that changing the value of an unbound field on a
continuous form will reflect the change of every record. Same works all
sorts of things, formating,colour, visiblity etc etc

The other shortcoming or your approach is that me.something refers to
something on the active record (i.e. the selected 'form' from the
long stream of forms)

Soo... perhaps as one of many possiblities, you could write a
function that calculates and returns the desired value, and make that
function the control source. i.e. write a function, say
fxMyFunction(MyParamater), and set the control source of the form to
"=fxMyFunction([SomeField])"

Or you could make the form based on a query, which is built on the history
table, and has an extra column that calculates the DaysOverdue, then bind
that textbox to the new column.

Lemme know if you need more detail on either of these
Glenn
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.