How to refer to previous record value in a continous form?

  • Thread starter Thread starter Rajeev Samuel
  • Start date Start date
R

Rajeev Samuel

I need to calculate the time interval in months between
the dates we peform a service for the customer

The field "Diff" Contains the values that I want. How to
I calculate the value based on a previous record value?
I don't know how to get the value of the previous record
in a form.


Sample of live data.

CustNo Actual Diff
300799 5/6/02
300799 12/12/02 7 (may/02 - dec/02)
300799 6/4/03 7 (dec/02 - june/03)
300799
 
Paste the function below into a standard module.

Include a text box on your form to show the value from the previous row. Set
its Control Source property to:
=GetPreviousValue([Form], "MyField")
replacing MyField with the name of the field you want from the previous row.


Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function
 
You can use the DateDiff VB function to calculate the
difference between two dates by any interval including
months. Here's an example:

Diff = DateDiff("m",Actual,Date())

The result will be an integer representing the number of
months between the value of Actual and today's date. You
can find the syntax for the function in VB Help.

HTH

-Mike
 
You can also use OldValue property to get the value of a
previous record. (Assuming you are working on a form.)
 
Thanks Allen,

It doesn't seem to take. What I mean is I test the
function and it work but the value is not assigned to the
field I set. All i get is #NAME?

Rajeev
-----Original Message-----
Paste the function below into a standard module.

Include a text box on your form to show the value from the previous row. Set
its Control Source property to:
=GetPreviousValue([Form], "MyField")
replacing MyField with the name of the field you want from the previous row.


Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Rajeev Samuel said:
I need to calculate the time interval in months between
the dates we peform a service for the customer

The field "Diff" Contains the values that I want. How to
I calculate the value based on a previous record value?
I don't know how to get the value of the previous record
in a form.


Sample of live data.

CustNo Actual Diff
300799 5/6/02
300799 12/12/02 7 (may/02 - dec/02)
300799 6/4/03 7 (dec/02 - june/03)
300799
Click to expand...


.
Click to expand...
 
OldValue is the value of a bound control at the time you start editing the
record, so I don't see how you intend to connect it to any previous record
in the form?
 
Rajeev, are you wanting to *store* the value from the previous record in the
form into a field in the current record?

That's generally not a good idea. Have you considered what would happen if
the records were filtered? ... sorted differently? ... if another record was
added such that it fitted between two others? ... if a record gets deleted?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Rajeev Samuel said:
Thanks Allen,

It doesn't seem to take. What I mean is I test the
function and it work but the value is not assigned to the
field I set. All i get is #NAME?

Rajeev
-----Original Message-----
Paste the function below into a standard module.

Include a text box on your form to show the value from the previous row. Set
its Control Source property to:
=GetPreviousValue([Form], "MyField")
replacing MyField with the name of the field you want from the previous row.


Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Rajeev Samuel said:
I need to calculate the time interval in months between
the dates we peform a service for the customer

The field "Diff" Contains the values that I want. How to
I calculate the value based on a previous record value?
I don't know how to get the value of the previous record
in a form.


Sample of live data.

CustNo Actual Diff
300799 5/6/02
300799 12/12/02 7 (may/02 - dec/02)
300799 6/4/03 7 (dec/02 - june/03)
300799
Click to expand...


.
Click to expand...
Click to expand...
 
No the purpose of the field just to be a calculated field
to assist data input. It will not be stored.

Rajeev
-----Original Message-----
Rajeev, are you wanting to *store* the value from the previous record in the
form into a field in the current record?

That's generally not a good idea. Have you considered what would happen if
the records were filtered? ... sorted differently? ... if another record was
added such that it fitted between two others? ... if a record gets deleted?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Rajeev Samuel said:
Thanks Allen,

It doesn't seem to take. What I mean is I test the
function and it work but the value is not assigned to the
field I set. All i get is #NAME?

Rajeev
-----Original Message-----
Paste the function below into a standard module.

Include a text box on your form to show the value from the previous row. Set
its Control Source property to:
=GetPreviousValue([Form], "MyField")
replacing MyField with the name of the field you want from the previous row.


Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row
Click to expand...
of
the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
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.
I need to calculate the time interval in months between
the dates we peform a service for the customer

The field "Diff" Contains the values that I want.
Click to expand...
How
to
I calculate the value based on a previous record value?
I don't know how to get the value of the previous record
in a form.


Sample of live data.

CustNo Actual Diff
300799 5/6/02
300799 12/12/02 7 (may/02 - dec/02)
300799 6/4/03 7 (dec/02 - june/03)
300799


.
Click to expand...
Click to expand...


.
Click to expand...
 
Ah: so you are referring to the text box when you say "field".

The ControlSource of the text box needs to be:
=GetPreviousValue([Form], "NameOfYourFieldHere")

The Name of this text box must not be the same as any of the fields in the
form's RecordSource.

You must have a reference to the DAO library.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Rajeev Samue said:
No the purpose of the field just to be a calculated field
to assist data input. It will not be stored.

Rajeev
-----Original Message-----
Rajeev, are you wanting to *store* the value from the previous record in the
form into a field in the current record?

That's generally not a good idea. Have you considered what would happen if
the records were filtered? ... sorted differently? ... if another record was
added such that it fitted between two others? ... if a record gets deleted?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Rajeev Samuel said:
Thanks Allen,

It doesn't seem to take. What I mean is I test the
function and it work but the value is not assigned to the
field I set. All i get is #NAME?

Rajeev
-----Original Message-----
Paste the function below into a standard module.

Include a text box on your form to show the value from
the previous row. Set
its Control Source property to:
=GetPreviousValue([Form], "MyField")
replacing MyField with the name of the field you want
from the previous row.


Function GetPreviousValue(frm As Form, strField As
String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of
the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
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.
I need to calculate the time interval in months between
the dates we peform a service for the customer

The field "Diff" Contains the values that I want. How
to
I calculate the value based on a previous record value?
I don't know how to get the value of the previous record
in a form.


Sample of live data.

CustNo Actual Diff
300799 5/6/02
300799 12/12/02 7 (may/02 - dec/02)
300799 6/4/03 7 (dec/02 - june/03)
300799


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top