Avatar of Derek Brown
Derek Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Next Row = The one above plus () Query

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?
Microsoft Access

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
John Tsioumpris

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
Gustav Brock

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

Derek Brown

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
John Tsioumpris

I don't know for the other but all i see i a black screen in your capture
David Johnson, CD

black screen with mouse movement is all that I see
Derek Brown

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PatHartman

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.
Derek Brown

ASKER
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
Derek Brown

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Derek Brown

ASKER
Thanks All!
Gustav Brock

You are welcome!