Autoupdate textbox based on previous record

[Bhoc]

this is my first post to this forum - what a great forum - has helped me greatly so far but can't seem to find an answer to below:

I am creating a database for an annual golf tournament. Part of this is to allocate players to play off together at certain times.
I have a form bound to a table called tblteeofftimes. This form has five fields (continuous form) - four of which are comboboxes whereby user selects each player for the record (who will play together). The other textbox has the tee off time. the first tee off time will be allocated by user eg 7:00 am. My problem is I need to update each ensuing record with a new time slot at 7 minute intervals. Rather than have the user populate the form themselves I would prefer this be done authomatically as the user moves onto the next record.

Any help would be greatly appreciated

 

[JHB]

Maybe something like that in the forms OnCurrent event:

Private Sub Form_Current()
  If Me.NewRecord Then
      Dim dbs As Database, rst As Recordset
  
      Set rst = Me.RecordsetClone
      If Not rst.EOF Then
         rst.MoveLast
         Me.teeofftimeControlName.DefaultValue = rst![teeofftimeFieldname] + 7
      Else
        Me.teeofftimeControlName.DefaultValue = "07:00"
     End If
  End If
End Sub

 

[Bhoc]

Thanks JHB for your prompt reply - will give it a go in a couple of days and let you know how it goes

 

[JHB]

Thanks JHB for your prompt reply - will give it a go in a couple of days and let you know how it goes

Yes do that - luck with you project.

 

[Bhoc]

JHB

thanks again
I am getting a compile error - method or data error not found - I changed your code as below (shown in red) to reflect the table name of teeofftimes and the field to teeofftime.

For info the tables full name is tblteeofftimes and the field that textbox named teeofftime is bound to in this table is formatted for short time. Help!!!


If Me.NewRecord Then
Dim dbs As Database, rst As Recordset

Set rst = Me.RecordsetClone
If Not rst.EOF Then
rst.MoveLast
Me.teeofftimesControlName.DefaultValue = rst![teeofftimesteeofftime] + 7
Else
Me.teeofftimesControlName.DefaultValue = "07:00"
End If
End If

 

[JHB]

Yeah I see I haven't been clear enough about the control-, table- and field-names.
I have attached a small example database for you, open the only form in it, and put a date in the date field.
Remember if you code the code to yours database to change all the names to what you use.
If you can't get your database to run then post it with some sample data, (zip it because you haven't post 10 post yet.).

 

[Bhoc]

JHB

thanks for your efforts - it worked exactly as I wanted it to.
I realised exactly where I went wrong on your first reply when you posted the last reply and small db

 

[JHB]

You're welcome. By doing mistake we learn, afterwards is it (mostly) easy to see what went wrong.

 

[Bhoc]

JHB

Am having problems with this form now - am going through test times with database and it is not adding the extra 7 minutes each time only placing 7:00 am start in each record. Have not made any changes to the form at all or to the tables fields etc. Any ideas???

 

[JHB]

Post the database, (zip it).
And also a print screen of where it goes wrong.

 

[Bhoc]

JHB

Have uploaded database - it is only part of it as was too big to fully upload but the tables and forms etc that relate to tee off are included. Also a screen print of the form where it goes wrong - you will see in screen print that when first records put in it creates the second tee off time but it is still 7:00 am as was the first

 

[JHB]

Bhoc, you have to replace the code in the form "tblteeofftimes" with the below code, then it should work else let me know:

Private Sub Form_Current()
  If Me.NewRecord Then
    Dim dbs As Database, rst As Recordset
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(Me.RecordSource)
    If Not rst.EOF Then
      rst.MoveLast
      Me.Teeofftime.DefaultValue = "#" & DateAdd("n", 7, rst![Teeofftime]) & "#"
    Else
      Me.Teeofftime.DefaultValue = "#" & CDate("07:00") & "#"
    End If
  End If
End Sub

 

[Bhoc]

JHB

Works well thanks - have tried it several times and it is working.