Capture Date deleted

[Guest]

I need to capture the current date/time when a record was deleted.
This command buttons code writes the date to a table.. Everything works
fine except I cannot get the date/time to write to the table...
In the code below my problem seems to be: lCriteria = lCriteria & Date & "
AS tDate "

Private Sub cmdDelete_Click()
Dim lCriteria As String
Dim lICCNNO As String
Dim lRacfid As String

lICCNNO = Me!ICNNO

If MsgBox("Are you sure you want to delete ICN No. " & lICCNNO,
vbQuestion & vbYesNo, gAppname) = vbYes Then
DoCmd.SetWarnings False

Dim lID As Long
lID = GetNewID("tblTrackingDataDeleted")
lCriteria = "INSERT INTO tblTrackingDataDeleted ( ID, ICNNO,
MEMBERNO, TR_PRODUCT, TR_WHEN, "

lCriteria = lCriteria & "SELECT " & lID & " AS tID,
tblTrackingData.ICNNO, tblTrackingData.MEMBERNO, "
lCriteria = lCriteria & "tblTrackingData.TR_PRODUCT, "
lCriteria = lCriteria & Date & " AS tDate "
lCriteria = lCriteria & "FROM tblTrackingData "
lCriteria = lCriteria & "WHERE (((tblTrackingData.ICNNO)=" &
"""" & lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria


Any Suggestions

 

[Brendan Reynolds]

Try ...

lCriteria & "#" & Format$(Date(), "mm/dd/yyyy") & "# etc.

 

[Guest]

Dan,
You need to enclose dates in hashes '#'
The line should read something like:
lCriteria = lCriteria & " #" & Date & "# AS tDate "

Hope that helped.
Dave

 

[Guest]

I have to put this job into production today before noon (Florida time)..
I cannot get around the error "Number of query valuses don't match
destination field" #3346... The numbers are the same, but it's the DATE that
is not working. I have tried so many ways I'm going blind.
Please help..


Private Sub cmdDelete_Click()
Dim lCriteria As String
Dim lICCNNO As String
Dim lRacfid As String

lICCNNO = Me!ICNNO

If MsgBox("Are you sure you want to delete ICN No. " & lICCNNO,
vbQuestion & vbYesNo, gAppname) = vbYes Then
DoCmd.SetWarnings False

Dim lID As Long
lID = GetNewID("tblTrackingDataDeleted")
lCriteria = "INSERT INTO tblTrackingDataDeleted ( ID, ICNNO,
MEMBERNO, TR_PRODUCT, TR_CARENETWORK, "
lCriteria = lCriteria & "TR_GBU, TR_INQUIRYTYPE,
TR_DATE_TIMERCVD_HOI, TR_DATE_TIMERCVD, "
lCriteria = lCriteria & "TR_GRIEVANCECOORDINATOR, TR_CLOSEDATE,
TR_9000CAUSECODE, TR_CASETYPE, "
lCriteria = lCriteria & "TR_SPECIALTYCODE, TR_PROBLEMCODE,
TR_ACKNOWLTR, TR_MEDICALRELEASEFORM, "
lCriteria = lCriteria & "TR_MEDICALRELEASERETURNDT, TR_DECISION,
TR_EXPEDITED, TR_SOURCE, TR_COMMENTS, "
lCriteria = lCriteria & "TR_CASESUMMARY, TR_VO, TR_EXTENSION,
TR_STATUS, TR_NIA, TR_AORREM, "
lCriteria = lCriteria & "TR_AORRETURNDT, TR_Who, TR_When) "

lCriteria = lCriteria & "SELECT " & lID & " AS tID,
tblTrackingData.ICNNO, tblTrackingData.MEMBERNO, "
lCriteria = lCriteria & "tblTrackingData.TR_PRODUCT,
tblTrackingData.TR_CARENETWORK, "
lCriteria = lCriteria & "tblTrackingData.TR_GBU,
tblTrackingData.TR_INQUIRYTYPE, "
lCriteria = lCriteria & "tblTrackingData.TR_DATE_TIMERCVD_HOI,
tblTrackingData.TR_DATE_TIMERCVD, "
lCriteria = lCriteria &
"tblTrackingData.TR_GRIEVANCECOORDINATOR, tblTrackingData.TR_CLOSEDATE, "
lCriteria = lCriteria & "tblTrackingData.TR_9000CAUSECODE,
tblTrackingData.TR_CASETYPE, "
lCriteria = lCriteria & "tblTrackingData.TR_SPECIALTYCODE,
tblTrackingData.TR_PROBLEMCODE, "
lCriteria = lCriteria & "tblTrackingData.TR_ACKNOWLTR,
tblTrackingData.TR_MEDICALRELEASEFORM, "
lCriteria = lCriteria &
"tblTrackingData.TR_MEDICALRELEASERETURNDT, tblTrackingData.TR_DECISION, "
lCriteria = lCriteria & "tblTrackingData.TR_EXPEDITED,
tblTrackingData.TR_SOURCE, "
lCriteria = lCriteria & "tblTrackingData.TR_COMMENTS,
tblTrackingData.TR_CASESUMMARY, "
lCriteria = lCriteria & "tblTrackingData.TR_VO,
tblTrackingData.TR_EXTENSION, tblTrackingData.TR_STATUS, "
lCriteria = lCriteria & "tblTrackingData.TR_NIA,
tblTrackingData.TR_AORREM, tblTrackingData.TR_AORRETURNDT, "
lCriteria = lCriteria & "" & """" & gcurrentuser & """" & " AS
tUser "
lCriteria = lCriteria & " #" & Date & "# AS tDate "

lCriteria = lCriteria & "FROM tblTrackingData "
lCriteria = lCriteria & "WHERE (((tblTrackingData.ICNNO)=" &
"""" & lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria

 

[Brendan Reynolds]

You're missing the comma after tUser ...

lCriteria = lCriteria & "" & """" & gcurrentuser & """" & " AS tUser "

.... should be ...

lCriteria = lCriteria & "" & """" & gcurrentuser & """" & " AS tUser, "

 

[Guest]

YES, YES, YES - I was affected by the deadly "One Comma"

Thank you