We help IT Professionals succeed at work.

Next Row = The one above plus () Query

Derek Brown
Derek Brown used Ask the Experts™
on
Is there any way to reference the record above in any form of Query?

I want to Update a Date field where the new Date is 1 hour greater than the record above. I created a great form, with help from you guys, all works really well. Unless a group of records has been duplicated where the Date field is empty.  (I have fixed this issue going forward but existing clients have old duplicates, so I need to fill the empty Dates in before they can use my new form)

Something like:  NewRow, Date field = Date in row above plus 1 hour

I don't ask for much do I?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
How about a screenshot of your form to get a better grasp of what you are trying to do.
Probably you need to work with the form's RecordSetClone and use goto Previous to get to the previous Record
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Run this code in your form:

Public Function UpdateDates()

    Dim rs          As DAO.Recordset
    Dim LastDate    As Date
    
    Set rs = Me.RecordsetClone
    
    If rs.RecordCount > 0 Then
        While Not rs.EOF
            If IsNull(rs!YourDateField.Value) Then
                rs.Edit
                    rs!YourDateField.Value = DateAdd("h", 1, LastDate)
                rs.Update
            End If
            LastDate = rs!YourDateField.Value
        Wend
        rs.MoveNext
    End If
    rs.Close
    
End Function

Open in new window

Author

Commented:
My whole system relies on Dates. But if a duplicate is made you cannot get between the two dates. As it happens I still have another issue because the date to change to is wrong. But I hope you get the picture and you can open the attachment.
1.gif
John TsioumprisSoftware & Systems Engineer

Commented:
I don't know for the other but all i see i a black screen in your capture
Top Expert 2016

Commented:
black screen with mouse movement is all that I see

Author

Commented:
Yes sorry about that. I could not get a screen capture showing combo contents so I did a Gif video. Hopeless. I'll see if I can show it some other way
Distinguished Expert 2017

Commented:
Queries are set operations so row order is "flexible" at best.  You might have some success using a correlated subquery.  Just make sure to sort both queries.  The outer query should select the record that is the "anchor".  The subquery can use the TOP predicate so it only returns a single row.  

You can also do this if you open a recordset but the recordset MUST BE SORTED ascending by date.  When you get to the record you want, move once more and if that record is in the time frame you are looking for, update it.

I didn't test it but Gus is reliable and so his code will probably work but MAKE SURE that the recordset is sorted correctly to bring the two records together.

Author

Commented:
Hi Gustav

To get away with it I have put this code  on the on enter procedure for the button that opens the pop up form. Has solved the problem. But the dates generated from it start at 31/12/1899?.  Anyway to get the first record to be Now()?

Public Function UpdateDates()
    Dim rs          As DAO.Recordset
    Dim LastDate    As Date
   
    Set rs = Me.RecordsetClone
   
    If rs.RecordCount > 0 Then
        While Not rs.EOF
                rs.Edit
                    rs!ItemDateCreated.Value = DateAdd("h", 1, LastDate)
                rs.UpDate
            LastDate = rs!ItemDateCreated.Value
       
        rs.MoveNext
        Wend
    End If
    rs.Close
   
End Function

Author

Commented:
In Here I get RecordCount = 24 records but While Not rs.EOF I get False???????????????????????

    If rs.RecordCount > 0 Then
        While Not rs.EOF
                rs.Edit

Open in new window


I have to close all forms and reopen to get it to work. I have tried every combination of save dirty requery in numerous places to no avail.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Set Now  as the initial value, and do a MoveFirst:

Public Function UpdateDates()
    Dim rs          As DAO.Recordset
    Dim LastDate    As Date
    
    Set rs = Me.RecordsetClone
    LastDate = Now
    
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        While Not rs.EOF
            rs.Edit
                rs!ItemDateCreated.Value = DateAdd("h", 1, LastDate)
            rs.UpDate
            LastDate = rs!ItemDateCreated.Value        
        rs.MoveNext
        Wend
    End If
    rs.Close
    
End Function

Open in new window

Author

Commented:
Thanks All!
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!