Save Deleted Record

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
 
B

Brendan Reynolds

You have an extra comma and no closing parenthesis at the end of the field
list. That is, this ...

"INSERT INTO t_DataTrackingDeleted ( ID, ICNSR, PVNO, "

.... should look like this ...

"INSERT INTO t_DataTrackingDeleted ( ID, ICNSR, PVNO ) "

Similarly in the SELECT clause, you have an extra comma after the last value
....

lCriteria = lCriteria & "SELECT " & lID & " AS tID, t_DataTracking.ICNSR,
t_DataTracking.PVNO, " <-- extra comma here.

It might help if I demonstrate how I discovered these errors ...

Public Sub PrintSql()

Dim lID As Long
Dim lCriteria As String
Dim lICCNNO As String

lID = 1
lICCNNO = "one"

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

Debug.Print lCriteria

End Sub

The result in the Immediate window looks like this ...

INSERT INTO t_DataTrackingDeleted ( ID, ICNSR, PVNO , SELECT 1 AS tID,
t_DataTracking.ICNSR, t_DataTracking.PVNO , FROM t_DataTracking WHERE
(((t_DataTracking.ICNSR)="one"));

Notice how much easier it is to see the error when you debug print the
concatenated result rather than trying to find it among the fragments of SQL
embedded in the code.
 
G

Guest

oops, those camma's were there because I am actually pulling in many more
variables and accidently left them when I copied this..

Even so, when they are removed, the same error appears (immidiate window or
running the code)..

I did notice one error in the MsgBox: & lICNSR should be & lICCNNO..

The big part of my problem is that I don't know if the error is in this code
or the Public Function to get the new ID..

Beacuse the error is at my DoCmd and teh SQL is not running, I don't know
where it's getting stuck???
 
B

Brendan Reynolds

I can only debug what I can see, Dan.

Try my Debug.Print suggestion. Just comment out your DoCmd.RunSQL line and
add a Debug.Print lCriteria line like so ...

'DoCmd.RunSQL lCriteria
Debug.Print lCriteria

If you can't see what the problem is, copy the result from the Immediate
window and post it here.
 
G

Guest

Ok, I commented out the "DoCmd.RunSQL lCriteria" and added the "Debug.Print
lCriteria"..
Now when I click the command to run the code, it does deleted the record
from the form and leave me at a blank form.
But the record is not deleted from the table.
And it's not copied to the table I'm trying to copy it to..

Here are the results in the Imidiate Window:

INSERT INTO t_DataTrackingDeleted ( ID, ICNSR, PVNO SELECT 0 AS tID,
t_DataTracking.ICNSR, t_DataTracking.PVNO FROM t_DataTracking WHERE
(((t_DataTracking.ICNSR)="1"));

Thanks
 
B

Brendan Reynolds

You're still missing the closing parenthesis after the field list ...

( ID, ICNSR, PVNO SELECT 0 AS tID

.... should be ...

( ID, ICNSR, PVNO) SELECT 0 AS tID

To delete the record from the source table you will need to execute a DELETE
statement after you have executed the INSERT INTO statement.
 
G

Guest

My main concern at this point is the INSERT INTO Statement.
The output in the immediate window seems right.
But no data gets written to the table.???
INSERT INTO t_DataTrackingDeleted (ID, ICNSR, PVNO) SELECT 0 AS tID,
t_DataTracking.ICNSR, t_DataTracking.PVNO FROM t_DataTracking WHERE
(((t_DataTracking.ICNSR)="01-123456"));

Suggestions??
 
B

Brendan Reynolds

Are you certain that the table 't_Data Tracking' includes a record with a
value of "01-123456" in the 'ICNSR' field?

If the answer to the above question is 'yes' then you may need to post the
code again, as it has changed since the last time you posted it.
 
G

Guest

Yes, there was a record with a value of "01-123456" in the 'ICNSR' field, the
deletion portion of this code works it deleted the record. But as I
mentioned, the problem I'm having is writting the deleted data to a table.

I'm only trying to delete and copy 3 variables. I have many more to add
once I can copy something to the table after deleting it.

Thanks for the help on this I've tried everything I can imagine:
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. " & lICCNNO,
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 ID,
t_DataTracking.ICNSR, t_DataTracking.PVNO "

lCriteria = lCriteria & "FROM t_DataTracking "
lCriteria = lCriteria & "WHERE (((t_DataTracking.ICNSR)=" & """"
& lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria
'Debug.Print lCriteria

lCriteria = "DELETE DISTINCTROW t_DataTracking.ICNSR FROM
t_DataTracking "
lCriteria = lCriteria & "WHERE (((t_DataTracking.ICNSR)=" & """"
& lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria

DoCmd.SetWarnings True

DoCmd.GoToRecord , , acNewRec
End If
Forms!f_TrackingData.Refresh
Forms!f_TrackingData.Visible = False

Forms!f_TrackingData.Visible = True


Exit_cmdDelete_Click:
Exit Sub
End Sub
//////////////////////////////////

Option Compare Database
Option Explicit

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


Thanks again
 
B

Brendan Reynolds

This works for me ... the record is inserted into 't_DataTrackingDeleted',
and removed from 't_DataTracking'. Be careful of the code I added to create
the necessary tables and data for me to test the code on my PC. Do *NOT*
execute the DROP TABLE statements against your production database! In fact,
just as an extra precaution, I'm going to comment out those two lines in the
code below, though they were not commented out when I ran the test ...

Private Sub cmdDelete_Click()

'added for testing - presumably this is a global variable in the
original code ...
Dim gappname As String

Dim lCriteria As String
Dim lICCNNO As String

'added for testing ...
Me.ICNSR = "01-123456"
On Error Resume Next

'careful with these ...
'CurrentDb.Execute "DROP TABLE t_DataTracking"
'CurrentDb.Execute "DROP TABLE t_DataTrackingDeleted"

On Error GoTo 0
CurrentDb.Execute "CREATE TABLE t_DataTracking (ID integer NOT NULL
PRIMARY KEY, ICNSR varchar(50), PVNO varchar(50))"
CurrentDb.Execute "CREATE TABLE t_DataTrackingDeleted (ID integer NOT
NULL PRIMARY KEY, ICNSR varchar(50), PVNO varchar(50))"
CurrentDb.Execute "INSERT INTO t_DataTracking (ID, ICNSR, PVNO) VALUES
(1, '01-123456', 'whatever')"
'end of code added for testing

lICCNNO = Me!ICNSR

If MsgBox("Are you sure you want to delete ICNSR No. " & lICCNNO,
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 ID,
t_DataTracking.ICNSR, t_DataTracking.PVNO "

lCriteria = lCriteria & "FROM t_DataTracking "
lCriteria = lCriteria & "WHERE (((t_DataTracking.ICNSR)=" & """"
& lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria
'Debug.Print lCriteria

'DISTINCTROW and field name not required
'lCriteria = "DELETE DISTINCTROW t_DataTracking.ICNSR FROM
t_DataTracking "

lCriteria = "DELETE FROM t_DataTracking "
lCriteria = lCriteria & "WHERE (((t_DataTracking.ICNSR)=" & """"
& lICCNNO & """" & "));"
'Debug.Print lCriteria
DoCmd.RunSQL lCriteria

DoCmd.SetWarnings True
'commented out for testing
'DoCmd.GoToRecord , , acNewRec
End If

'I commented out these as they are not directly relevant
'to the problem at hand

'Forms!f_TrackingData.Refresh
'Forms!f_TrackingData.Visible = False

'Forms!f_TrackingData.Visible = True


Exit_cmdDelete_Click:
Exit Sub
End Sub
 
G

Guest

So close!! and what you have shown me I truly appreciate.
You say this works for you, does it copy the record into another table called
t_DataTrackingDeleted, after getting a new ID/row in the table(GetNewID)???

I have brought in the code you pasted below and it works fine until the
DoCmd.RunSQL lCriteria.
The only error I get is Run-Time Error 3134 Syntax error in INSERT INTO
statement.

Do you see any reason why, when the code runs this statement, it's not
executing the below public function:
Dim lID As Long
lID = GetNewID("t_DataTrackingDeleted")


////////

Option Compare Database
Option Explicit

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
 
B

Brendan Reynolds

To answer your first question, the answer is yes.

In my previous tests, I was dropping the t_DataTrackingDeleted table and
re-creating it, so the GetNewID function always returned 0 (zero) and this
was the value that was inserted into the table. But I just tested again,
this time commenting out the lines that drop and re-create the
t_DataTrackingDeleted table, and can confirm that the first time I execute
the code the value 0 (zero) is inserted, the second time the value 1 (one)
is inserted.

The above test, taken together with the error message ("syntax error in
INSERT INTO statement") strongly suggests that the error, whatever it is, is
not in the 'GetNewID' function but in the code that builds the SQL string.

We may be falling victim to 'newsreader line-wrap' - code that was
originally on one line in the VBA editor gets broken into multiple lines in
the newsgroup message. When you try to fix up those lines again in the VBA
editor, extra quotes can creep in as the editor 'helpfully' inserts quotes
for you where it thinks they belong. I've pasted the code into a text file,
which you can find at the following URL ...

http://brenreyn.brinkster.net/cmdDelete_Click.txt

Perhaps comparing the line breaks and quotes in that text file with your
code might help to reveal where the problem lies.
 
G

Guest

It's now deleting the record and copying the record to another table.
Could Never have done it without you...

Thanks
 
B

Brendan Reynolds

Glad to have helped, Dan. Just out of curiosity, was I right about the
source of the problem being an extra quote inserted by the VBA IDE while
fixing up the lines?
 
G

Guest

I fixed all the quotes, then used your copy with the quotes corrected, still
I could not see any quotes out of place, so I added the other variables I
needed.
I think it was when I added the user ID to record the person deleting the
record.
Once I had that under control the record was copied over correctly before
deleting.
Here is the final results:

Private Sub cmdDelete_Click()

Dim gappname As String
Dim lCriteria As String
Dim lICCNNO As String
Dim lRacfid As String

lICCNNO = Me!ICNSR

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

Dim lID As Long
lID = GetNewID("t_DataTrackingDeleted")
lCriteria = "INSERT INTO t_DataTrackingDeleted (ID, ICNSR, PVNO,
RN_RACF,"
lCriteria = lCriteria & "LetterDate, BCBSreceived,
Appealsreceived,"
lCriteria = lCriteria & "Who, When)"

lCriteria = lCriteria & "SELECT " & lID & " AS ID,
t_DataTracking.ICNSR, t_DataTracking.PVNO, "
lCriteria = lCriteria & "t_DataTracking.RN_RACF,
t_DataTracking.LetterDate,"
lCriteria = lCriteria & "t_DataTracking.BCBSreceived,
t_DataTracking.Appealsreceived,"
lCriteria = lCriteria & "" & """" & gcurrentuser & """" & " AS
Who, "
lCriteria = lCriteria & "#" & Format$(Now, "mm\/dd\/yyyy") & "#
AS When "

lCriteria = lCriteria & "FROM t_DataTracking "
lCriteria = lCriteria & "WHERE (((t_DataTracking.ICNSR)=" & """"
& lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria
'Debug.Print lCriteria

lCriteria = "DELETE FROM t_DataTracking "
lCriteria = lCriteria & "WHERE (((t_DataTracking.ICNSR)=" & """"
& lICCNNO & """" & "));"
DoCmd.RunSQL lCriteria
'Debug.Print lCriteria

DoCmd.SetWarnings True
DoCmd.GoToRecord , , acNewRec

End If

Forms!f_TrackingData.Refresh
Forms!f_TrackingData.Visible = False
Forms!f_Cases.Refresh
Forms!f_Cases.Visible = False

Forms!frmMain.Visible = True



Exit_cmdDelete_Click:
Exit Sub
End Sub
////

Again I really appreciate the help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OpenRecordSet Error 3
Transfer data 14
Send data to table 11
Capture Date deleted 5
New ID 1
record changed values 1
User Defining Problem 2
Dialog in MsgBox 4

Top