Display the date in a textbox in a form (Access VBA)

[JM11]

Hello,
I would like to display the current date (in a field called "Latest date" in a form) when a button is pressed in that form. The field in the form has a source in a table; thus, I am trying to run on the click() event of the button the following query to update the cell in the table and the field in the form.

DoCmd.RunSQL "INSERT INTO [myTable] ([Latest date]) VALUES (" & Format(currDateTime, "\#yyyy-mm-dd hh:nn:ss\#") & ")"

Any suggestions?
Thank you for your time,
Regards,

[zmbd]

How you've explained this is a tad difficult to follow.

It would be a good thing to tell us what Access is doing and not doing for you.

With that said.

First, I suggest that you do not build your string within the function. I know that's the way they show to do this; however, it makes troubleshooting a pain.

Dim sSQL as String
'...
sSQL = "INSERT INTO " & _
   "[myTable] ([Latest date]) VALUES (" & _ 
    Format(currDateTime _
       , "\#yyyy-mm-dd hh:nn:ss\#") & ")"
'
'[update]
Debug.Print sSQL 
'
DoCmd.RunSQL sSQL
 

Make this little change, and then we can insert a debug.print sSQL and run, presto, <ctrl><g> and we can see how the string is resolving.

Second your date field in your table would accept #MM/DD/YYYY# by default from code. The only reason to format is for a text field - and such is highly unadvisable for date/time data. Allen has an insight for International Dates in Access
If your system date is set to "yyyy-mm-dd hh:nn:ss" Access will often handle that without too many issues... sometimes. In any case... using the "#" delimitation, access is expecting #MM/DD/YYYY hh:nn:ss

Third, you have to force the form to either refresh or requery the bound recordset. Using an "Insert" you will have to me.requery

[NeoPa]

ZMBD:
Second your date field in your table would accept #MM/DD/YYYY# by default from code. The only reason to format is for a text field

I'm not sure I would agree with that Z.

Even in the USA I would recommend that date values are Format()ed into the SQL string as it is only a coincidence that USA date formats are acceptable in a SQL string. Almost anywhere else in the world pushing the date value into the SQL string without formatting it first will cause failures and unrequired results. yyyy-m-d is a particularly good way to format the date as it implicitly makes clear that the date is formatted for SQL rather than accidentally or randomly dropped in, and is fully internationally independent.

One addition I would advise for full portability (Some countries recognise the '-' as a date separator and convert it automatically to a '\' and thus break the SQL string.) would be to escape the '-' characters with the backslash. Thus the format used would be :

\#yyyy\-m\-d hh:nn:ss

See Literal DateTimes and Their Delimiters (#) for more on this.

NB. For the original question, it seems that Now() would be a good way to express the current Date/Time. It works in both SQL and VBA.
As for the rest of the question, I'm equally confused as to what you're asking.

[JM11]

Actually my problem is that I am not able to save the value of the current date in my table. I am running an SQL query from a form to insert the date into the table, but it is not working.

(The column in the table corresponding to the date is always empty; nothing is being saved)

[zmbd]

well, let's take a look at the sql-string you are using.
please make the changes as I suggested by pulling the string out of the function and then inserting the debug.print (I've updated the code block to show this)

Run your code, click on the button, etc...

once done, press <ctrl><g> and the VBA editor and immedates window should open, cut and paste the string in a post here
PLEASE format that string using the [CODE/] formatting tool in the toolbar.