[kmxs]

I've run into this numerous times and through tinkering I get around the issue but I don't understand why it keeps coming up. Basically when I run a piece of code like the one below, I get the error "The record has been changed by another user since you started editing it. I am the only user on the database since this is my backend test. I imagine that I am trying to launch multiple queries at once while another is still open but I don't know how to just make one happen instantly and stop so that others can be run without it hitting this conflict.

This most often happens when I use the query in an afterupdate or lost.focus and before I leave the box I change the record and on form close a bunch of other queries run. Anyone know what might be going on here? Even if you don't have the answer and you can throw me a bone that would help, this problem keeps coming up and it's very frustrating.

Private Sub chkVisa_Click()
If chkVisa.Value = True Then
    CurrentDb.Execute ("UPDATE Orders SET InvoiceSent=Yes WHERE OrderID=" & txtOrderID.Value)
    txtPaidDate.Value = Int(Now())
    Me.Refresh
Else
    CurrentDb.Execute ("UPDATE Orders SET InvoiceSent=No WHERE OrderID=" & txtOrderID.Value)
    txtPaidDate.Value = Format("", "mm/dd/yy")
End If
 On Error GoTo ERR_POSearch
ERR_POSearch:
End Sub

[kmxs]

I'm not positive of it but I think the issue is stemming from the checkbox. Since the checkbox is updating it's own yes/no field while I'm running a query on click of the checkbox it's trying to do 2 queries/updates at once? Not positive though.

[immeraufdemhund]

I usually have that if i forget to close a table, or a connection to a table/database. Might want to check that all your tables are closed before you call this sub routine. I also found that if it is a form that has the table source as the one that I'm checking it is the one that is "editing" it and not a person...

if this is your problem you might want to look into using unbinding your data from your form and creating a connection to the table and appending the data to your table.

kmxs, on 12 July 2011 - 12:14 PM, said:

I'm not positive of it but I think the issue is stemming from the checkbox. Since the checkbox is updating it's own yes/no field while I'm running a query on click of the checkbox it's trying to do 2 queries/updates at once? Not positive though.

could you post your entire forms vba? also could you post how it is setup?

[kmxs]

immeraufdemhund, on 12 July 2011 - 10:18 AM, said:

I usually have that if i forget to close a table, or a connection to a table/database. Might want to check that all your tables are closed before you call this sub routine. I also found that if it is a form that has the table source as the one that I'm checking it is the one that is "editing" it and not a person...

if this is your problem you might want to look into using unbinding your data from your form and creating a connection to the table and appending the data to your table.

kmxs, on 12 July 2011 - 12:14 PM, said:

I'm not positive of it but I think the issue is stemming from the checkbox. Since the checkbox is updating it's own yes/no field while I'm running a query on click of the checkbox it's trying to do 2 queries/updates at once? Not positive though.

could you post your entire forms vba? also could you post how it is setup?

The form code is huge I'm not sure that would help. I was a bit wrong in my original assertion as the problem will occur by checking the box without leaving the form or what not. I'm pretty sure the the issue has something to do with not closing tables as you were saying, how do I do that though when I do a currentdb.execute? Or when the check is bound to a yes/no field on the table and causes a query every time I check or unhcheck it.

Not sure what you mean by how the form is setup? It's a single form with a subform. The problem seems isolated though, when I step through nothing else is happening in the code or on the form and the problem still occurs. I am thinking it's a problem between the bound check and the currentdb.execute both being applied to the stack at the same time and not just happening in sequence.

[immeraufdemhund]

would you mind sending me the access file so I can take a gander at the problem and the steps it takes to replicate the problem?

[kmxs]

immeraufdemhund, on 12 July 2011 - 10:33 AM, said:

would you mind sending me the access file so I can take a gander at the problem and the steps it takes to replicate the problem?

Sorry that's not possible. It's my office DB and the data is quite sensitive. Do you think if I use dao recordsets and edit then close them it will help?

[kmxs]

This works when I unbind the checkbox. So it has directly to do with the ordering of the stack. Basically when it's bound and checked, and I run a click event it sends 2 queries to the stack for the same table. I would like to know a way to force the click code to run the query, update, then close it's connection so when it finishes and the check updates its bound field there is no conflict.

This is the final code that worked, unfortunately I had to suppliment with an on current even that checks if the field in the DB is True/False to update the form. Seems much less efficient than it could be, would love any other suggestions to do this with a bound field.

Private Sub chkVisa_Click()
    If chkVisa.Value = True Then
        CurrentDb.Execute ("UPDATE Orders SET Visa=Yes, InvoiceSent=Yes, PaidDate=#" & Format(Int(Now()), "mm/dd/yy") & "# WHERE OrderID=" & txtOrderID.Value & ";")
    Else
        CurrentDb.Execute ("UPDATE Orders SET Visa=No, InvoiceSent=No, PaidDate=Null WHERE OrderID=" & txtOrderID.Value & ";")
    End If
    Me.Refresh
End Sub

I'm gonna throw you a rep for suggesting the bound fields fix but asking for a DB that is obviously dealing with credit card and financial data is a little fishy when I am supplying the relevant code to my problem.

[kmxs]

Ok I figured it out. It was what I had thought and there were too many queries on the stack that had not completed. When the after update finishes it tries to run whatever I queried in the code and it's own command which somehow conflict. I would love to know more about this and how it's functioning more technically.

DoCmd.RunCommand acCmdRefresh

This DoCmd will save the current change from the bound field and then the code within it's event can run after without conflict.

Private Sub chkVisa_Click()
DoCmd.RunCommand acCmdRefresh
    If chkVisa.Value = True Then
        CurrentDb.Execute ("UPDATE Orders SET Visa=Yes, InvoiceSent=Yes, PaidDate=#" & Format(Int(Now()), "mm/dd/yy") & "# WHERE OrderID=" & txtOrderID.Value & ";")
    Else
        CurrentDb.Execute ("UPDATE Orders SET Visa=No, InvoiceSent=No, PaidDate=Null WHERE OrderID=" & txtOrderID.Value & ";")
    End If
    Me.Refresh
End Sub