How do you add the same value in mainform, to multiple selected rows in subform

[neelsfer]

I have a button on mainform that create current time.In the subform, i already have data in some of the fields.
I would like to highlight these specific rows in the subform (select them with mouse) and then add the value created by button on mainform to these selected rows in the subform at once.
Mainform = racef
Subform = timesf
field in subform = timestart
i currently use this code to add the value created by button on mainform, selectively to 1 row in subform at a time.

GetLocalTime MyTime
[Forms]![RaceF]![TimeSF]![TimeStart] = Format$(MyTime.wDay, "00") & "/" & Format$(MyTime.wMonth, "00") & "/" & MyTime.wYear & _
                                       " " & Format$(MyTime.wHour, "00") & ":" & Format$(MyTime.wMinute, "00") & ":" & _
                                       Format$(MyTime.wSecond, "00") & "." & Format$(MyTime.wMilliseconds, "000")

see attachment; value created in button must be added ie to racenumbers 1 and 3 and 5 at once if i select them using mouse. pls assist

[ADezii]

I found no easy way to do this, but I could be wrong in this matter. I did, however, arrive at a solution which you may/may not like, but here it goes along with a Demo Attached.

1. Create a Field named [Select] Yes/No in the TimeT Table. This Field will serve no real purpose other than to indicate which records will receive the Start Time Value generated from the Main Form.
2. Make sure this Field ([Select]) is included in the Sub-Form, TimeSF.
3. Select records in the Sub-Form for which you wish to transfer the Start Time.
4. Click on the Start Button.
5. Clear all the Check Boxes.
6. If you like this approach, repeat the process for the Finish Time.
7. I'll post the Code below, but see the Attached for a more detailed view.

P.S. - Only relevant Code has been posted.

Private Sub start_Click()
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strTimeValue As String
 
GetLocalTime MyTime
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("SELECT * FROM [TimeT] WHERE [Select] = True;", dbOpenSnapshot)
 
strTimeValue = Format$(MyTime.wDay, "00") & "/" & Format$(MyTime.wMonth, "00") & "/" & MyTime.wYear & _
                       " " & Format$(MyTime.wHour, "00") & ":" & Format$(MyTime.wMinute, "00") & ":" & _
                       Format$(MyTime.wSecond, "00") & "." & Format$(MyTime.wMilliseconds, "000")
 
If rst.BOF Or rst.EOF Then
  MsgBox "No Records Selected", vbCritical, "No Selections"
    Exit Sub
End If
 
With rst
  Do While Not .EOF
    CurrentDb.Execute "UPDATE [TimeT] Set [TimeStart] = '" & strTimeValue & _
                      "' WHERE [Select] = True", dbFailOnError
      .MoveNext
  Loop
End With
 
CurrentDb.Execute "UPDATE [TimeT] SET [Select] = False;", dbFailOnError
 
rst.Close
Set rst = Nothing
End Sub

[ADezii]

@neelsfer - Oops, think I made a Logic boo-boo. You may wish to revise the previously posted Code to:

Private Sub start_Click()
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strTimeValue As String
 
GetLocalTime MyTime
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("SELECT * FROM [TimeT] WHERE [Select] = True;", dbOpenDynaset)
 
strTimeValue = Format$(MyTime.wDay, "00") & "/" & Format$(MyTime.wMonth, "00") & "/" & MyTime.wYear & _
                       " " & Format$(MyTime.wHour, "00") & ":" & Format$(MyTime.wMinute, "00") & ":" & _
                       Format$(MyTime.wSecond, "00") & "." & Format$(MyTime.wMilliseconds, "000")
 
If rst.BOF Or rst.EOF Then
  MsgBox "No Records Selected", vbCritical, "No Selections"
    Exit Sub
End If
 
With rst
  Do While Not .EOF
     .Edit
       ![TimeStart] = strTimeValue
     .Update
       .MoveNext
  Loop
End With
 
CurrentDb.Execute "UPDATE [TimeT] SET [Select] = False;", dbFailOnError
 
rst.Close
Set rst = Nothing
End Sub

[neelsfer]

Thank you i will try to get it to work tonight

[neelsfer]

Great idea. Thx Mr Adezi. Sure it wil work

[ADezii]

Glad it all worked out for you, neelsfer.