SQL in vba failing for 'lock violation'

[Cheese_whiz]

Hi all,

When I run the code below, the first delete string works, but the second one
tells me it won't delete the record because of a 'locking violation'. The
code runs in the beforeupdate event of a main form that is tabbed. The last
tab has a subform that is one-to-one to the main form. That subform is
tabbed. The first tab has just controls housing data from teh recordsource
of that subform, while the other tabs have their own subforms with
one-to-many related data (related to the first tab of the subform).

The records that are successfully deleted are in the 2nd and 3rd tabs of the
subform (in subforms on those tabs). The main record in that subform (on the
first tab) is the one that is failing.

Anyone help?

thanks,
CW

____________________________________
If Me.cmbStatus.OldValue = 3 And Me.cmbStatus.OldValue <> Me.cmbStatus Then
If MsgBox("You are changing the status of a currently marked
'Settled' file to some other " & _
"status classification. This will result in ALL settlement data
and related records being permanently " & _
"removed from the database. Do you wish to continue?", vbYesNo,
"WARNING") = vbYes Then
DoCmd.RunSQL "Delete * From SettlementPayees Where (FileID =
Forms!Files.txtID)"
Forms!Files.subSettlements.Form.Dirty = False
DoCmd.RunSQL "Delete * From Settlements Where (FileID =
Forms!Files.txtID)"
Else
End If
End If
______________________________________________________

 

[Steve Sanford]

Hi CW,

I don't see how the first (or second) delete command will run. To see what I
mean, run the following code:

Public Sub try3()
Debug.Print "Delete * From SettlementPayees Where (FileID =
Forms!Files.txtID)"
End Sub

Open the Immediate Window (Ctl-G). SQL doesn't know how to handle
"Forms!Files.txtID". The *value* of "Forms!Files.txtID" needs to be
concantated to the Delete string like this:

Debug.Print "Delete * From SettlementPayees Where FileID = " &
Forms!Files.txtID


The two Delete commands should be:

DoCmd.RunSQL "Delete * From SettlementPayees Where FileID = " &
Forms!Files.txtID
DoCmd.RunSQL "Delete * From Settlements Where FileID = " & Forms!Files.txtID


Also, I would replace "DoCmd.RunSQL" with "CurrentDb.Execute"


HTH

 

[John Spencer]

Actually
DoCmd.RunSQL
is able to interpret the reference to Forms!Files!TxtID.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Cheese_whiz]

Thanks for the replies.

I tried your suggestions, Steve. I'm not sure if I had previously tried the
concatenation before, but if not it was just an oversight on my part.

Nonetheless, it does operate a little differently in that the code I posted
threw the error message about not being able to delete one record due to lock
violation. The code using the concatenation doesn't throw that error
message, but still doesn't delete that same record (the one that is
one-to-one with the main form record). If I change the combo box on the main
form to the value that is suppose to run those deletes, save, then go back
and change it back to the value that should make taht last tab visible, I get
all the old data on the first tab of the subform in that last tab (on the
main form). It should basically be empty. The related records ARE gone
(with both code versions), but the main record in the subform on the last tab
is not.

So, no error message but same result.

Any other ideas?

Thanks again,
CW

 

[Cheese_whiz]

Hi again,

Just an update. I ran the delete query on the table that was failing. I
ran it in the immediate window and did it while the form was NOT on the
record in question (the main form). I just changed the reference to the form
value to the actual number of the record involved.

It works doing it that way. I think the lesson is that the record is being
treated as if it were being edited, which makes since given the error
message. I've never had this issue before, and I think it has something to
do with the one-to-one relationship between the main record and the record
I'm trying to delete in the subform on the last tab.

I hope maybe that helps better define the problem.

Thanks,
CW

 

[Cheese_whiz]

Ok, I got it figured out sort of.

Basically, I was running that code in the before update event of the main
form. I guess that's why Access didn't like the idea of deleting a related
record. I don't know exactly why, but either you can't edit two records at
once or you can't edit one record while you are editing a record it is
related to.

Regardless, I needed to inform the user what he was getting ready to do in
time for him to change his mind (in the before update event), but hold off
actually doing the deletion until the after update event (when the main
form's record is no longer being edited.

I made a form level variable and just assigned that to a value based on user
selection when she was warned of the impending deletion. I then check that
value in the after update code and if it says to go ahead and delete, those
delete queries run. It seems to work fine. There is probably a better way...

Anyway, thanks for the help,
CW

 

[Steve Sanford]

I thought i had read/heard the reference to the control had to be concantated
to the SQL string. Time to hit the books again

Thanks for pointing that out John.

 

[Steve Sanford]

Glad you got it working.

 

[John Spencer]

IF you use
dbAny.Execute strSQL
then the value must be concatenated into the SQL string.

If you use
DoCmd.RunSQL strSQL
then the value does not HAVE to be concatenated into the SQL string



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================