looping problem

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" <solaris_nite@hotmail.com> wrote in message
news:6174ca57.0405271958.43184d46@posting.google.c om...[color=blue]
> 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
> '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''[/color]

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