G
Guest
I want to save a deleted record and copy it to a table.
Can you please help me see why this SQL will not run.
It bombs at: DoCmd.RunSQL lCriteria
Private Sub cmdDelete_Click()
Dim lCriteria As String
Dim lICCNNO As String
lICCNNO = Me!ICNSR
If MsgBox("Are you sure you want to delete ICNSR No. " & lICNSR,
vbQuestion & vbYesNo, gappname) = vbYes Then
DoCmd.SetWarnings False
Dim lID As Long
lID = GetNewID("t_DataTrackingDeleted")
lCriteria = "INSERT INTO t_DataTrackingDeleted ( ID, ICNSR,
PVNO, "
lCriteria = lCriteria & "SELECT " & lID & " AS tID, t_DataTracking.ICNSR,
t_DataTracking.PVNO, "
lCriteria = lCriteria & "FROM t_DataTracking "
lCriteria = lCriteria & "WHERE (((t_DataTracking.ICNSR)=" & """"
& lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria
///////////////////////
Public Function GetNewID(tblName As String) As Long
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(tblName)
If rs.RecordCount > 0 Then
rs.MoveLast
GetNewID = rs.Fields(0) + 1
Else
GetNewID = 0
End If
End Function
Can you please help me see why this SQL will not run.
It bombs at: DoCmd.RunSQL lCriteria
Private Sub cmdDelete_Click()
Dim lCriteria As String
Dim lICCNNO As String
lICCNNO = Me!ICNSR
If MsgBox("Are you sure you want to delete ICNSR No. " & lICNSR,
vbQuestion & vbYesNo, gappname) = vbYes Then
DoCmd.SetWarnings False
Dim lID As Long
lID = GetNewID("t_DataTrackingDeleted")
lCriteria = "INSERT INTO t_DataTrackingDeleted ( ID, ICNSR,
PVNO, "
lCriteria = lCriteria & "SELECT " & lID & " AS tID, t_DataTracking.ICNSR,
t_DataTracking.PVNO, "
lCriteria = lCriteria & "FROM t_DataTracking "
lCriteria = lCriteria & "WHERE (((t_DataTracking.ICNSR)=" & """"
& lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria
///////////////////////
Public Function GetNewID(tblName As String) As Long
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(tblName)
If rs.RecordCount > 0 Then
rs.MoveLast
GetNewID = rs.Fields(0) + 1
Else
GetNewID = 0
End If
End Function