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

    Red face Unanswered: how do you automate the opening hours from the closing hours in a subform

    Hey there I've been struggling with this for a long time anyone could help me it will make my day, I have a subform and on my subform is three fields: Opening Hours, Closing Hours and Hours Worked Like my Opening Hours start with 9000 and my Closing hours is 12000 = Hours Worked is 3000 now on the next record in the subform my Opening Hours must automatically become my Closing Hours from the previous record which Opening Hours will become 12000 automatically can anyone help me please as I am under heavy pressure, ill really appreciated thanks in advance. If you can help me with this one youll make my day

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the module of the subform, you can retrieve the values of the previous row (record) using:
    Code:
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.Bookmark = Me.Bookmark
    If rst.AbsolutePosition > 0 then
        rst.MovePrevious
        '
        ' You can now retrieve the values in the Fields of the current record of rst
        '  with the syntax: Value = rst!FieldName
        '
    Else
        '
        ' Handle the case when there is no previous record.
        '
    End If
    rst.Close
    Set rst = Nothing
    Have a nice day!

  3. #3
    Join Date
    Apr 2013
    Posts
    22
    Just one question does this go on the Subform After insert or the Closing or Opening field After Update

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by grampo View Post

    ...Opening Hours start with 9000 and my Closing hours is 12000...

    ...Hours must automatically become my Closing Hours from the previous record which Opening Hours will become 12000...
    Since these are apparently continuous running hours, would not the Opening Hours simply be the maximum Closing Hours in the Recordset? For Opening Hours a Default Value of

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

    should do the job, replacing all names with your actual names, of course.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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
  •