Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    83

    Unanswered: Retrieve the previous record value

    Is it possible to retrieve the previous month value in the ‘Previous CT” column based on the “department number”, “sub-dept”, “date”, “CT” columns, in the below example?

    For e.g. for department 3300 if the users tries to enter the data for 02/01/09, they would like to view the previous month data as ‘84’. Is it possible to retrieve the previous record value in Forms, or queries? Any ideas would be greatly appreciated. Thanks.


    Department Sub-Dept. Date CT PreviousCT
    3300 03/01/2009
    3300 02/01/2009 84
    3300 01/01/2009 84 96
    3300 12/01/2008 96 84
    3340 08/01/2008 127
    3340 EMG 07/01/2008 127 106
    3340 EMG 06/01/2008 106
    3340 EEG 05/01/2008 91
    3340 EEG 04/01/2008 91
    3340 03/01/2008

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes it is, but how depends on the meaning you give to the word "previous".

    If by "previous" you mean the value in the column "month" of the previous record in the form's recordset, then the solution is obvious:
    Code:
    Function GetPreviousMonth() As Long
    
        Dim rst As DAO.Recordset
    
        Set rst = Me.RecordsetClone
        If rst.AbsolutePosition > 0 Then
            rst.MovePrevious
            GetPreviousMonth = rst.Month
        End If
        rst.Close
        Set rst = Nothing
    
    End Function
    If "previous" means the previous month based on some criteria, then you'll have to create a recordset based on those criteria to retrieve the value you want. In that case it all depends on what criteria should be used and how to link the created recordset to the one of your form. Here is one possible template:
    Code:
    Function GetPreviousMonth() As Long
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
    
        strSQL = "SELECT <something> FROM <something> WHERE <link criteria> ORDER BY <sort order>
        Set rst = CurrentDB.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)
        If rst.AbsolutePosition > 0 Then
            rst.MovePrevious
            GetPreviousMonth = rst.Month
        End If
        rst.Close
        Set rst = Nothing
    
    End Function
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •