Record Locking (1 Viewer)

[Snuggle Walrus]

Hi all, i'm trying to understand record locking and how to use it correctly within my new form. The designs a bit....strange managing customer accounts with a sub form that allows the user to put an order through as well. It seems to work really really well however on my submit button Access warns me that the record is currently being edited and needs to be 'saved' 'discarded' or copy to clipboard. Hitting save has the desired effect, i assume whats happening is how i'm generating the fresh record when the form opens and then potentially moving customer info from the main form into the subform see below...

        Forms![WORKING ON].frm_Subform_ManageNewOrders_RightSide!CustomerDetails_CB = VCurrentCustomerID
        Forms![WORKING ON].frm_Subform_ManageNewOrders_RightSide!ShippingAddress_Order = VCurrentCustomerAddress
        Forms![WORKING ON].frm_Subform_ManageNewOrders_RightSide!ShippingPostCode_Order = VCurrentCustomerPostCode

is what might be making it feel like the values have been changed....Anyway the overwrite with just clicking 'save changes' seems to do the trick just fine but i'd like it if possible to not show up at all and I 'think' record locking is the way to go but i'm not sure I understand it fully.... Anyone have any ideas as to how to avoid this popping up? or a way to perhaps tell VBA if it does pop up to just save the record as shown on the form if record locking isent the way? Thanks everyone have a fantastic day!


EDIT: Ok so i narrowed down the write conflict. I use a query in the early part of my button click to tick a box to say that the order has been 'requested' this way when i go through potential 'unwanted' created orders anything that wasnt manually submitted could be removed, if i comment out this line I dont get the write error.....

 

[arnelgp]

this occurs when you have a Table in form and you are updating its Field through SQL query.
the Form holds a snapshot of your data.

 

[Snuggle Walrus]

this occurs when you have a Table in form and you are updating its Field through SQL query.
the Form holds a snapshot of your data.

Ah I see so if i've finished with the form i could get around this by telling it to go to a new record and then updating the yes/no box perhaps as the form would no longer be using the record?

 

[Snuggle Walrus]

Hmm i cant seem to control my records from within VBA at all at the end. Says saving is not available and it wont let me create a new record either.

 

[Pat Hartman]

1. if the BE is SQL Server, you have NO control over record locking from the Access FE. All of that is controlled by using Server-side settings.
2. There is little point to attempting to control record locking in the FE. You should just go with optimistic locking which is the default.
3. As you have discovered, you were conflicting with yourself. Why are you running an update query on a record you are viewing? The logic of doing this is questionable. However, if it turns out that you need to do this for some bizarre reason, save the current record before running the update query.