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?
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Probably you need to work with the form's RecordSetClone and use goto Previous to get to the previous Record
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
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
1.gif
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
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.
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.
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
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
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
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.
If rs.RecordCount > 0 Then
While Not rs.EOF
rs.Edit
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.
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial