Using value from previous record

  • Thread starter Thread starter DisloyalHarp
  • Start date Start date
D

DisloyalHarp

I have field that when I select it if the value Is Null
it should use the value from the previous record. How do
I write the code for this?

Thanks in advance
 
The definition of "previous record" is a problem, e.g. the records in a form
can be filtered or ordered so that the previous row changes.

This code retrieves the value from the previous record in a form, and works
correctly even when the form is filtered or sorted differently. For example,
to show the value of a field called "MyField" in the previous row, add a
text box to your form with this controlsource:
=GetPreviousValue([Form], "MyField")

If that is not what you need, this article may help:
ACC2000: How to Compare a Field to a Field in a Prior Record
at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;208953&Product=acc2000


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.
'Keywords: PriorRow PreviousValue
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
 
I havn't tested this but if you place the following in the BeforeUpdate
event of the form then it should do what u want.

Me.MyTextBox.DefaultValue = Me.MyTextBox

HTH,

Neil.
 
Simply press the [Ctrl]['] (Control & Apostrophy) combination to retreive the value of the same filed in previous record
If you want it to be standing their without even having to run through [Ctrl]['], you need
- a global variable called "Last", for exampl
- Assign the value of the current record to this variable with "MyField=Last" on "After Insert" even
- on "On current" event, test if "Last" is not null before assigning it to the default value of that field
If not isnull(last) the
myfield.defaultvalue = las
end if
 
Back
Top