MS Access VBA coding to update field

How are you creating the new record?

with an insert statement, you might do something like:

INSERT INTO yourTable (field1, Field2, Field3, StartTime)
Values (Field1, Field2, Field3, DMAX("StartTime", "yourTable", "DateValue([EndTime]) = #" & date() & "#"))

Select allOpen in new window

However, if this is a multi-user database, then you might have users step on each other by simultaneously running this query, which would return the same startTime form multiple records.  If that is the case, would you want the values for the previous records to apply to the specific individual who is entering the data?

Currently I have a split database which I run a local table on each of the users FE database located on their desktop.  They each have a form that they enter data into (each record has a StartTime field and an EndTime field).  The Main table is linked but the local table stores all their records for the day until at the end of the day they load their data from their local table to the linked main table located in the BE database so there is no worry about other user data getting confused with current users data.

I have code under Form Current as :

Dim dtEndTime As Date

If me.newrecord then Me.StartTime = dtEndTime

This method works most all of the time but mostly screws up on the second record.  I need it to work every time.
How can I make this happen?

The StartTime field and the EndTime field format is for the time.

Yes, each user clicks on the load button on the form which the code loads all the data from the local table to the linked main table every day.
I will try this code

Thanks much!

Excellent fix, Dale!

glad to help.