Reference a previous record

[falconerlf]

In crystal reports when I write a formula I can state a condition of say if the current field is blank, then print the content of the same field but from the previous record. The function is Previous () and it works beautifully. I need this same function in Access.

There has to be a way that I can do this in Microsoft Access. I am not a VBA developer but if someone points me in the right direction as to how to state this in an Access expression I think I can get it to work for me. I must say I am somewhat perturbed that it is not a straight forward out-of-the-box function in MS Access.

[topher23]

A DLookup could be useful here. Use Help in the VB window and look it up. You'd want to do something like

DLookup("myfield","mytable","RecordID = " & Me.RecordID -1)

This assumes a field called RecordID as your Primary Key field and that you don't delete records. It should get you started.

[ADezii]

I took the liberty of re-creating a Previous() Function for you that will return the previous Value in a Field should it be NULL. Simply pass to the Function the Name of your Form and also the Field Name you wish to check. Execute the code in the Current() Event of the Form. Any questions, feel free to ask. For this example I used the frmEmployees Form containing the [Title] Field of the Northwind Sample Database. The big advantage of this approach is that the code is portable.

1. Function Definition:
   

   Public Function Previous(strFormName As String, strFieldName As String)
   On Error GoTo Err_Previous
   Dim rstClone As DAO.Recordset
   Dim strBookmark As String
   Dim frm As Form
    
   Set frm = Forms(strFormName)
    
   Set rstClone = frm.RecordsetClone           'Create a Cloned Recordset
    
   strBookmark = frm.Bookmark                  'Store Original Bookmark
    
   rstClone.Bookmark = frm.Bookmark            'Sync Bookmarks
    
   rstClone.MovePrevious                       'Move to the Previous Record in the Clone
    
   Previous = rstClone.Fields(strFieldName)    'Retrieve Value from Field in Clone
    
   frm.Bookmark = strBookmark                  'Resync Bookmarks
    
   Exit_Previous:
     Exit Function
    
   Err_Previous:
     If Err.Number <> 3021 Then    'Not at the 1st or New Record
       MsgBox Err.Description, vbExclamation, "Error in Previous()"
     End If
       Resume Exit_Previous
   End Function

2. Sample Function Usage:
   

   Private Sub Form_Current()
     If IsNull(Me![Title]) Then
       Me![Title] = Previous("frmEmployees", "Title")
     End If
   End Sub

[NeoPa]

@falconerlf
Quite the contrary in fact. I'm guessing from this that your expertise is not in database work. SQL and the SQL engine are the heart of anything related to databases nowadays, and SQL doesn't support ordinals. It recognises no order as being relevant to its processes. This makes absolute sense in an RDBMS, though is not so intuitive to those that come from a more conventional coding background.

Crystal Reports (as well as Access of course) has the possibility of overlaying the basic database concepts with other code (processing through recordsets etc). In this way it is possible to provide the functionality that you're after - ADezii has provided just that for you - but this is not something natural to any RDBMS.

[nico5038]

I concur with NeoPa that this is no "logical" functionality in a RDBMS.
Functionally an empty field indicates "not known" and that's different from "value from previous record". Problem with "value from previous record" is the order the rows are processed and thus a different order can give an empty field different values.... :-(

Looks to me that the normalization standards don't apply to the data in your table(s) and that should be taken care of instead of using a "Previous" function in a report.
In the form you can take care that the previous value is filled as a default and ADezii gave you code to do that. (An alternative sample .mdb can be found at http://examples.oreilly.com/accesscook/CDROM/ the fifth sample of the chap09 download contains a "Carry Data Forward from Record to Record" form)

So when producing a report no tricks are needed.

Finally the Access reporting does have the "reverse" operation available, being the "Suppress duplicate values" option.

Nic;o)