Re: Data from previous record
From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 04/30/04
- Next message: rupert: "Aggregate MEDIAN"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 30 Apr 2004 16:32:45 +0800
The main difficulty with trying to read a value from the previous row is
that "previous row" can be redefined at any instant, depending on how the
user filters or sorts the data. The only safe approach, therefore, is to use
the RecordsetClone of the form.
1. Paste the function below into a standard module (created from Modules tab
of Database window), and save.
2. Open your form in design view, and add a text box. Put this in its
Control Source:
=GetPreviousValue([Form], "PutYourFieldNameHere")
replacing the bit in quotes with the name of the field you wish to read
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. "Alan Nicoll" <anonymous@discussions.microsoft.com> wrote in message news:635801c42e3c$b0892570$a501280a@phx.gbl... > I have some data that I need to interpret based on the > value of the same field in the previous record. If I were > working in a Visual Basic array it would be easy enough, > but I don't know how to do this in Access. > > That is: I have a shift code in one field that indicates > (among other things) time of day when an employee worked. > The rate the employee earns depends on Day/Evening/Night > rates. Now, sometimes the shift is coded as "Overtime" > and so doesn't indicate the time of day. > > When the shift is coded "Overtime" I want to look back at > the previous record and take the time of day from there. > In most cases this will be correct, which will be more > convenient for the user than just plugging in the Day rate > (user checks all this by hand anyway). > > Thanks. > > Alan
- Next message: rupert: "Aggregate MEDIAN"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
