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?

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

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

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.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Derek Brown

ASKER

Thanks All!

Gustav Brock

You are welcome!