I'm getting error 3218 on a inline SQL command

[Pack10]

I am working on a routine that the user can place a status of Archive....and the record will be inserted into a new table and
the current record deleted. When it runs the delete is giving me error 3218 cant delete current record. How can I get around this.

 

[TheAceMan1]

How are ya Pack10 . . .

We'll have no idea until you [blue]post your code![/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[missinglinq]

Actually, at least in v2003, Error 3218 is "Could not update; currently locked." In addition to showing us your current code, are you doing anything else to access/lock the underlying Table?

Also, if you're going to the trouble assigning a Status Value of 'Archived' to the Record, why place in in a second,identical/near identical Table? The pretty much standard way of handling this scenario is to have a single Table and simply query that Table and return Records based on whether or not the Status is 'Archived.'

Linq ;0)>

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Pack10]

Here's the code. First I am inserting the record into the archive table, then i am trying to delete the current record...that is where it's failing....


ws.BeginTrans
bInTrans = True
Set db = ws(0)

'Step 2: Execute the append.
strSql = "INSERT INTO [Archived_DDQs] (Status, Assigned_To, AssignedDate, Task_Received) VALUES('" & Forms!frmTasksUpdate!Combo570 & "', '" & Forms!frmTasksUpdate!Combo584 & "', '" & Forms!frmTasksUpdate!txtAssignedDt & "', '" & Forms!frmTasksUpdate!txtReceived & "');"
db.Execute strSql, dbFailOnError


'Step 3: Execute the delete.
strSql = "DELETE FROM Assigned_DDQs WHERE Parent = " & Forms!frmTasksUpdate!txtItem
db.Execute strSql, dbFailOnError

'Step 4: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If

 

[Pack10]

It is working now that I have done a compact and repair.
On another note, my insert is not working. I want to add a numeric value and am getting a syntax error.
How do you code the insert with a numeric.

 

[PHV]

How do you code the insert with a numeric
without single quote

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pack10]

Having an issue....

strSql = "INSERT INTO [Archived_DDQs] (Status, Assigned_To, AssignedDate, Task_Received, Time_On) VALUES('" & Forms!frmTasksUpdate!Combo570 & "', '" & Forms!frmTasksUpdate!Combo584 & "', '" & Forms!frmTasksUpdate!txtAssignedDt & "', '" & Forms!frmTasksUpdate!txtReceived & "');"

I added the field Time_On (which is numeric) how does it get coded in the values section, I can't get it to work.

Thanks

 

[PHV]

strSql = "INSERT INTO [Archived_DDQs] (Status, Assigned_To, AssignedDate, Task_Received, Time_On) VALUES('" & Forms!frmTasksUpdate!Combo570 & "', '" & Forms!frmTasksUpdate!Combo584 & "', '" & Forms!frmTasksUpdate!txtAssignedDt & "', " & Forms!frmTasksUpdate!txtReceived & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886