Update value based on entry in another record

[sparlaman]

I have the following code on a form so that when I enter an EndTime on one record the next new record has a default StartTime to match.

 
'Code by Dev Ashish http://www.mvps.org/access/forms/frm0012.htm
Private Sub txtEndTime_BeforeUpdate(Cancel As Integer)
Const cQuote = """" 'Thats two quotes
Me!txtStartTime.DefaultValue = cQuote & Me!txtEndTime.Value & cQuote
End Sub

But, I have an error in my logic. This code only updates the StartTime in a new record. Because, there are times when I need to change an EndTime long after the record has been created, I need to modify this code so that if I update the EndTime of a record that update will go to the StartTime of the next record not just of the new record.

Unfortunately I'm very clueless when it comes to VBA and don't know what I need to do so that I actually am updating pre-existing records. I saw a post on here that started to address this I think but there was no solution.
https://www.accessforums.net/access/d...ata-11171.html

Any help is greatly appreciated.

[RuralGuy]

The Default Value *only* comes into play when a field has no previous value. What field and its value constitutes the "next record"?

[sparlaman]

Attached is a screen shot of my tables & relationships. I'm working with two tables primarily on this: tbTimesheets & tbTSEntry. I have a form/subform corresponding to the two tables. When I create a new tbTimeSheet record I will then add records to TSEntry that are children of the tbTimeSheet record. Within a single Timesheet I would like the EndTime of record 1 to be the StartTime of record 2 and to carry through like that for all records within that Timesheet. If I edit an EndTime on any given record. I'd like Access to automatically update the StartTime of the following record, if any, within that TimeSheet.

Does this make sense?

TimeSheetID| TSEntryID| StartTime| End Time|
1 | 1 | 0600| 0700|
1 | 2 | 0700| 0800|
1 | 3 | 0800| 0900|

[pbaldy]

As I mentioned on your other thread, you should be able to open a recordset on an SQL statement that got the next record. Based on your example, the timesheet ID would have to match and you want the next TSEntryID (SELECT TOP 1 ...). If the recordset returns EOF you're already at the last record, otherwise it would return the next one, at which point you'd use the Edit method to change the start time.

[sparlaman]

I almost understand what you're telling me but can you elaborate? I stumble my way through understanding VBA & SQL. All my queries are built from the GUI.

[pbaldy]

Here's some pseudo code:

  Dim strSQL                  As String
  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset

  Set db = CurrentDb()

  strSQL = "SELECT TOP 1 * FROM TableName " _
         & "WHERE TimeSheetID = " & Me.TimeSheetID & " AND TSEntryID > " & Me.TSEntryID _
         & " ORDER BY TSEntryID"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  If Not rs.EOF Then
    rs.Edit
    rs!StartTime = Me.EndTime
    rs.Update
  End If
  
  Set rs = Nothing
  Set db = Nothing