I'm using Access with a SharePoint list, treating it like it's a regular local table. I'm trying to program a button onto a form that will insert a new record into a different list than the one I'm working with on the form.

A user enters a date on a form, and I want the user to be able to press a "submit" button that will transfer that date to a new record on another SharePoint list (a sales person's schedule).

I'm inserting fields other than the date too, and those work great, so my connections and context seem OK.

I assume SharePoint's "behind the scenes" method of storing a date is different from that of Access, and I'm just wondering if anyone knows specifically how dates are stored in SharePoint (I may have to pass the data as a number, etc., but I don't know what formatting to use).

Also if there is a better way to go about this than the ADO method I used, I'm open to suggestions. This was just the way I knew.

-----This is my code so far:-----

Dim rstAppoint As ADODB.Recordset

Set rstAppoint = New ADODB.Recordset

rstAppoint.Open strPerson, CurrentProject.Connection, adOpenStatic, adLockOptimistic

With rstAppoint
.Fields("Appointment") = [ID Number] & " - " & [Last Name]
.Fields("Location") = [City]
.Fields("Notes") = [Notes]
.Fields("Time") = [Appointment Time]

End With

Set rstAppoint = Nothing

-----End of Code-----

strPerson stores the name of the table/SharePoint list that I'm adding the info to. Everything else is just fields that I want to pump from one SharePoint list into the other. If I take out the line ".Fields("Time") = [Appointment Time]", everything works as hoped. When I keep that line, I get "Multiple-step OLE DB operation generated errors". Both time fields are "Time and Date" fields on SharePoint, which is why this issue is confusing to me.

Many thanks for any help! Sorry if this was long-winded, I just wanted to sound clear

Re: Problem using Date/Time data type with VBA

Luiz Cláudio - MVP

If you debug your code, what value do you get for [Appointment Time]

Debug.Print [Appointment Time]

Re: Problem using Date/Time data type with VBA


It shows up in the usual Access date/time format "mm/dd/yyyy 00:00:00".

I couldn't figure that problem out so I actually ended up completely scrapping that code and using a DoCmd.RunSQL with an SQL statement of each insert I was trying to do above. More of a work-around, but this function doesn't have a problem with the date like the ADO did.