Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good Friday.
    I have a simple continuous form "frmOnCall". Three fields: oncallstartdate, PrimaryResource, SecondaryResource
    Oncallstartdate is a date field with general formatting. I am trying to create a double click event on the oncallstartdate field that, if the current record's oncallstartdate value is null, will add 7 days to the previous recordís oncallstartdate value however I am not sure how to reference the value of the previous record in a continuous form. Any help with how to reference the previous record's value would be appreciated. I have been searching online without much luck. Thank you!

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    There is no simple way to do this.
    Even making use of VBA and a recordset, is going to depend on
    the sort order of the data.

    The best possible solution I can come up with is to make use of the
    Recordsetclone Property.

    The basic theory is.

    Get the Bookmark of the current Record.

    Move to the previous record and see if you are at the BOF

    If so error

    Otherwise Make changes to that record

    Example Code Below
    The trigger would be the Double Click Event

    (You will need to change field references)

    Code:
    Dim rst As DAO.Recordset
    Dim varBK As Variant
    
    Dim strMSG As String
    
    strMSG=""
    
    IF IsNull(oncallstartdate) Then 
      varBK = Me.Bookmark
    
      Set rst = Me.RecordsetClone
    
      rst.Bookmark = varBK
    
      rst.MovePrevious
    
      If rst.BOF Then
        strMSG = "On First Record" & vbLf & "Cannot Update Previous Record"
      Else
        rst.Edit
       	rst("oncallstartdate") = NZ(rst("oncallstartdate"),0) + 7  'Check NOT also Blank
        rst.Update
      End If
    
      rst.Close
      Set rst = Nothing
    End If
    
    If strMSG <> "" Then
        MsgBox strMSG
    EndIf

    Or Something Like that..
    Andrew

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You might look at the possibility of using SendKeys to copy the value of the previous record to the control - and then add the 7 days to that value. The key combination CTRL plus ' does that as I recall.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    You might look at the possibility of using SendKeys to copy the value of the previous record to the control - and then add the 7 days to that value. The key combination CTRL plus ' does that as I recall.
    Thank you both for looking at this. I appreciate your help.. Going to play with it some more.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Here is a Microsoft article that might help as well How to fill a record with data from a previous record automatically
    Regards
    John



  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Not criticising, but does that answer this one though?


    It is looking at using data in the previous record (in sequence) to populate the current one.
    I think this question is how do I populate the Previous Record
    based upon data in the Current Record, using a Double Click of a field in the current record.
    Andrew

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I am not sure. You may be right.

    if the current record's oncallstartdate value is null, will add 7 days to the previous recordís oncallstartdate value
    I took this to mean that, having added 7 days to the previous records value, it would put that into the current record. You are suggesting it would go back and change the previous one.
    Regards
    John



  8. #8
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Cincinnati, Ohio
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    I am not sure. You may be right.



    I took this to mean that, having added 7 days to the previous records value, it would put that into the current record. You are suggesting it would go back and change the previous one.
    I would like to base the current ("new") record on the previous record's date+7 days. Not change the previous record but base the new upon it. Thanks again for looking at this.

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Your interpretation was correct John, I stand corrected, just read like that to me.

    So if you don't want to use the Microsoft method,
    then I think this for the Double Click Event

    Code:
    Dim rst As DAO.Recordset
    Dim varBK As Variant, dblDate as Date, varDate
    
    Dim strMSG As String
    
    strMSG=""
    
    IF IsNull(oncallstartdate) Then 
      varBK = Me.Bookmark
    
      Set rst = Me.RecordsetClone
    
      rst.Bookmark = varBK
    
      rst.MovePrevious
    
      If rst.BOF Then
        strMSG = "On First Record" & vbLf & "Cannot Update from Previous Record"
      Else
        varDate=rst("oncallstartdate")
        If isnull(varDate) then
    	strMSG="Previous Record has NO Date, cannot Update"
        Else
         	dblDate=varDate
         	oncallstartdate=dblDate + 7
        End If
      End If
      rst.close
      Set rst=Nothing
    End If
    
    
    
    If strMSG <> "" Then
        MsgBox strMSG
    EndIf
    Andrew

Posting Permissions

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