INSERT INTO with date
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
INSERT INTO with date
Hello. (Access 2003)
I'm executing an INSERT INTO statement to create an audit record in a table. It works except for the field dtmStamp, the date and time value.
Perhaps this is because it contains spaces? should I put quotes(') around this value (this doesn't seem to work?) or do I need to use the Format() function?
I'm in the UK if that helps? Thanks, Andy.
I'm executing an INSERT INTO statement to create an audit record in a table. It works except for the field dtmStamp, the date and time value.
Perhaps this is because it contains spaces? should I put quotes(') around this value (this doesn't seem to work?) or do I need to use the Format() function?
I'm in the UK if that helps? Thanks, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INSERT INTO with date
If you want to use the current date and time, you can use the Now() function:
strSQL = "INSERT INTO tblSomething (dtmStamp, ..., ...) VALUES (Now(), ..., ...)"
CurrentDb.Execute strSQL
If you want to get the date and time from a text box, say txtTimeStamp, you must do it slightly differently:
strSQL = "INSERT INTO tblSomething (dtmStamp, ..., ...) VALUES (" & Format(Me.txtTimeStamp, "mm/dd/yyyy") & ", ..., ...)"
CurrentDb.Execute strSQL
strSQL = "INSERT INTO tblSomething (dtmStamp, ..., ...) VALUES (Now(), ..., ...)"
CurrentDb.Execute strSQL
If you want to get the date and time from a text box, say txtTimeStamp, you must do it slightly differently:
strSQL = "INSERT INTO tblSomething (dtmStamp, ..., ...) VALUES (" & Format(Me.txtTimeStamp, "mm/dd/yyyy") & ", ..., ...)"
CurrentDb.Execute strSQL
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: INSERT INTO with date
Thank you but neither version works. It works for all text fields and a Yes/No value, so it must be the date format that's causing the problem.
Does it effect things that I'm in the UK (dd/mm/yyyy)? If I can get the date part working I still want to add the time 12/02/2010 11:58:20.
Any further ideas? Andy.
My sql statement looks like this in the immediate window (when just using Now()):
INSERT INTO tblAudit (SourceID, UserName, FieldName, OldValue, NewValue, NewRecord, DateTime)
VALUES ('33', 'Admin', 'FirstName', ' Mark', ' Markus', False, 12/02/2010 13:05:36);
Does it effect things that I'm in the UK (dd/mm/yyyy)? If I can get the date part working I still want to add the time 12/02/2010 11:58:20.
Any further ideas? Andy.
My sql statement looks like this in the immediate window (when just using Now()):
INSERT INTO tblAudit (SourceID, UserName, FieldName, OldValue, NewValue, NewRecord, DateTime)
VALUES ('33', 'Admin', 'FirstName', ' Mark', ' Markus', False, 12/02/2010 13:05:36);
Last edited by agibsonsw on 12 Feb 2010, 13:07, edited 1 time in total.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INSERT INTO with date
Are you using code? If so, could you post the relevant part of the code?
Or are you creating a query in design view? If so, could you switch to SQL view and post the SQL?
Or are you creating a query in design view? If so, could you switch to SQL view and post the SQL?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: INSERT INTO with date
Hello. I've attached a text file containing the function that runs on the before update event of a form: =AuditTrail("StaffID")
It works if I remove the date value. I'm currently trying with Now() but would rather use dtmStamp. Thanks for any assistance, Andy.
(If I print Now() in the immediate window, it displays: 12/02/2010 13:18:26)
It works if I remove the date value. I'm currently trying with Now() but would rather use dtmStamp. Thanks for any assistance, Andy.
(If I print Now() in the immediate window, it displays: 12/02/2010 13:18:26)
You do not have the required permissions to view the files attached to this post.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INSERT INTO with date
I'm probably dumb but I don't see any SQL statement in the text file that you attached.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: INSERT INTO with date
Oops! That didn't help. I inserted the previous code I started with. New attachment ..
You do not have the required permissions to view the files attached to this post.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INSERT INTO with date
Try changing the line
& blnNewRec & ", " & Now() & ");"
to
& blnNewRec & ", Now());"
& blnNewRec & ", " & Now() & ");"
to
& blnNewRec & ", Now());"
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: INSERT INTO with date
Thank you. I got excited, but no.. still the same syntax error.
The resultant sql was:
INSERT INTO tblAudit (SourceID, UserName, FieldName, OldValue, NewValue, NewRecord, DateTime) VALUES ('33', 'Admin', 'FirstName', ' Mark', 'Mark', False, Now());
Eureka! Access can't cope with the field name DateTime. I amended it to DateStamp and it works. Thank you again. Andy.
ADDITIONAL:
I've now managed to use the variable dtmStamp (without Format), but needed date qualifiers # to be quoted. Andy.
The resultant sql was:
INSERT INTO tblAudit (SourceID, UserName, FieldName, OldValue, NewValue, NewRecord, DateTime) VALUES ('33', 'Admin', 'FirstName', ' Mark', 'Mark', False, Now());
Eureka! Access can't cope with the field name DateTime. I amended it to DateStamp and it works. Thank you again. Andy.
ADDITIONAL:
I've now managed to use the variable dtmStamp (without Format), but needed date qualifiers # to be quoted. Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: INSERT INTO with date
A further point arises. I've tried using the same function for OnDelete, Before and After DeleteConfirm but is doesn't create any record - presumably because all fields are null
(or nothing?). Should I create a separate procedure for the delete process? Thanks, Andy.
(or nothing?). Should I create a separate procedure for the delete process? Thanks, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INSERT INTO with date
You should provide a custom command button and/or toolbar button to delete a record. In the code you can insert a record into tblAudit first, then delete the current record in the form.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: INSERT INTO with date
That's sensible. Of course, to make the database fully auditable it would need to be fully secured and all menu options (and the delete key?) removed.
In the meantime, can I not make use of the OnDelete event to record who deleted which record?
BTW: Does Access have a BeforeRightClick event hiding somewhere? Ta, Andy.
In the meantime, can I not make use of the OnDelete event to record who deleted which record?
BTW: Does Access have a BeforeRightClick event hiding somewhere? Ta, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: INSERT INTO with date
You can't use the Before Delete Confirm, On Delete or After Delete Confirm events to log which record is being deleted; when these events occur, Access has already placed the record to be deleted in a buffer which you can't access. The "current" record is now the record after the one to be deleted.
Access doesn't have a right-click event, but you can create a custom toolbar and set its type to Popup. You can then set the Shortcut Menu Bar property of a control to the custom toolbar. The custom toolbar will pop up when the control is right-clicked, instead of the built-in context menu.
Access doesn't have a right-click event, but you can create a custom toolbar and set its type to Popup. You can then set the Shortcut Menu Bar property of a control to the custom toolbar. The custom toolbar will pop up when the control is right-clicked, instead of the built-in context menu.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: INSERT INTO with date
Thank you.
I have already created a AuditTrailDELETE function which is called by OnDelete, and this does appear to give me the correct ID and Surname of the deleted staff member.
Perhaps because I'm calling a function rather than the event procedure, it gives me this information? Andy.
I have already created a AuditTrailDELETE function which is called by OnDelete, and this does appear to give me the correct ID and Surname of the deleted staff member.
Perhaps because I'm calling a function rather than the event procedure, it gives me this information? Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: INSERT INTO with date
For what it is worth, SQL Server will do much of the audit things your are trying to create within the database engine. It has triggers as well as the ability to insert default values, for things such as auditing. The challenge you have in trying to do this in Access is that only forms have events, and you cannot do audits on things users do at the table or query level. And that means you really have to lock up the database so users can't get to the database window or run queries.
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: INSERT INTO with date
Thank you.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.