Solved Pass date as a timestamp to SQL Server

[NigelBishop]

Hello a quick back story

I have an access frontend hocked up to an SQL Server backend and all is working fine.
I have a form in Access is bound to a table in SQL and manually populating the form causes no issues.
I have been asked to add the ability to duplicate records to assist in data entry and to reduce data entry errors.

There is a Timestamp field [ValCol] in the table which is causing issues when I try to duplicate the record, I understand what the Timestamp field is within SQL and it automatically populates correctly when data is manually entered, however it doesn't appear to populate when I try to duplicate a record.

I am using the following code to perform the duplication (not all fields within the record are duplicated)
If I change the table to a local table and make the field [ValCol] not required the code runs fine.(the Highlighted code is not included)

To make the code run correctly with the SQL Backend I'm guessing that I need to provide the [ValCol] value however I have no Idea how to achieve this so any help would be gratefully received, maybe I need to take a completely different approach?

With Me.RecordsetClone

.ADDNEW
![ResultDate].Value = [T_RDate]
![RefType].Value = [RefType]
![QCRef].Value = [QCRef]
![Area].Value = [Area]
![TypeOfSample].Value = [TypeOfSample]
![Room].Value = [Room]
![Site].Value = [Site]
![Enteredby].Value = UCase(NetworkUserName())

' ![ValCol].Value = ?????????? ----------This is the part of the code I need help with

.Update

' 'Display the new duplicate.
Me.Bookmark = .LastModified

End With

Thank you in advance

 

[June7]

I tested this code. I viewed the table in SSMS and Timestamp field has value but it is not displayed as a date. Field is set as required.
AFAIK, this field cannot be edited.

 

[Minty]

The Timestamp is like an autonumber generated by the server, as far as I know you can't set it's value, it has to be set by the server.

 

[NigelBishop]

I have altered my approach and am performing the duplication in SQL Server

Thank you for your help.

 

[GPGeorge]

The TimeStamp, or more properly, RowVersion, datatype in SQL Server is NOT a date at all, despite the misleading name. In fact, MS has been trying for years to get people to use the more descriptive name, RowVersion, with only limited success. Unfortunately, they don't help the situation by leaving the TimeStamp name in the list of datatypes you see when creating a new field.

It is incremented by SQL Server each time any field in a record is modified. This allows Access to know accurately without having to check each and every field in a record, if there has been a change to that record. If the timestamp has changed "something" else in that record was also changed.

When concurrent editing is going on among multiple users, this is an important aspect of maintaining speed and accuracy of data updates.

Leave it alone! It's not there for your use anyway. Omit it from your attempt to duplicate records.