Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2013

    Question Unanswered: how to get the previous value on a subform for an existing record

    Good day can someone help me figure this one out!!!

    I got 4 fields
    Opening Hours
    Closing Hours

    for instance [Opening Hours] is 8 [Closing Hours] is 16 then my [Opening Hours] will become 16 on the new existing record by default.

    I place this code on my Default value property for Opening Hours its working, but not for existing records only for new records.

    =Nz(DMax("[Closing Hours]","PlantTransaction"))

    What must actually happen is that I already got 4 records in a subform now when I enter record one Opening Hours 8 and Closing Hours 16 record two Opening Hours must become as default 16 not record 5 because its skipping the 4 existing records and jump automatically to a new record like record 5
    Thanks in advance hope one of you guys can make my day!!!
    Last edited by grampo; 04-30-13 at 08:29.

  2. #2
    weejas is online now Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    The Default property only applies to new records. It is the default value to be applied to that column in a new record, so it cannot be used to backfill gaps in your data.

    Also, I don't think that your function will work as you require it to. You haven't provided any sort of restriction the DMax call, and so that will simply return the largest value that it can find in table PlantTransaction. If this is a subform, then the source table (toi my way of thinking) would have a composite key - the ID of the parent record, and the ID of the current record. You also haven't provided the Nz call a value in the case that the first argument yields a null - you might want to set this to 0.

    The following tweaks might help:
    =Nz(DMax("[Closing Hours]", "PlantTransaction", "ID = " & [ID]), 0)
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    Apr 2013
    It not working I'm getting a error isint there a other way of doing it?

  4. #4
    Join Date
    Apr 2013
    The below is the code for new and existing records

    Private Sub Form_Current()
    Dim rst As DAO.Recordset

    If IsNull(Opening_Hours) Then
    Set rst = Me.RecordsetClone
    If rst.RecordCount > 0 Then
    If Me.NewRecord Then
    rst.Bookmark = Me.Bookmark
    End If
    Opening_Hours= rst!Closing_Hours
    End If
    End If
    End Sub

Tags for this Thread

Posting Permissions

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