INSERT INTO with date

[agibsonsw]

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.

[HansV]

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

[agibsonsw]

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);

[HansV]

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?

[agibsonsw]

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)

[HansV]

I'm probably dumb but I don't see any SQL statement in the text file that you attached.

[agibsonsw]

Oops! That didn't help. I inserted the previous code I started with. New attachment ..

[HansV]

Try changing the line

& blnNewRec & ", " & Now() & ");"

to

& blnNewRec & ", Now());"

[agibsonsw]

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.

[agibsonsw]

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.

[HansV]

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.

[agibsonsw]

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.

[HansV]

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.

[agibsonsw]

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.

[Wendell]

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.

[agibsonsw]

Thank you.