How to Update a Bound Table Without Getting a Write Conflct Error

[RunTime91]

Greetings Experts ~

First - please allow me to apologize for the ambiguity of my thread title - Just wan't sure how to phrase this one.

Ok, here is my setup:

What I have is a form bound to a linked SQL table - which, of course is updated with changes made to the form.

Where I encounter my issue is when the user completes their changes and clicks 'Submit'

The click event has an Update query which places a timestamp in an unbound field.

The table has an ID field; and as I understand from my research, updates the moment the record is entered into the form.

I further understand the event of updating the ID field also places a 'lock' on that record

Thus, when the user finishes with their changes and clicks 'Submit' - They receive the dreaded 'Write Conflict Error' Seemingly due to the Update Query.

I have tested this theory and the Update Query does appear to be the issue as with it commented out - the Write Conflict Dissappears.

So... What I am now trying to figure out is how to update the record with a timestamp without causing a Write Conflict.

I've tried setting the Dirty Property to True/False in a few locations - That hasn't worked yet.



I could place a hidden control (textbox) on the form and simply update the control and forego the Update Query - But is this the best way?

And lastly, I do have Bit fields (Yes/No - True/False) which do not have a default value and thus are set to Null until the record is updated

I also read where this could cause the issue I'm experiencing as well.

Anyway, sorry for the disertation, but I hope this puts you right in the driver seat of what I'm dealing with.

As for Code - I wasn't sure what to post for this question - but I will provide anything you need to help you help me

Thanks Everyone...

[ranman256]

your title says bound, your text says unbound.
why unbound and an update query?
can you not just edit the record directly (bound) along with the datestamp?

[Micron]

places a timestamp in an unbound field.

bind that field on the form; don't show if preferred. In click event, first write time stamp to control/field. Rest of code (if there is any) can run, including If Me.Dirty Then Me.Dirty = False
You should neither use or need update sql for a bound form in most cases.

[RunTime91]

RanMan - Micron, Glad you guys are out there...

So I kinda figured this would be hard to describe - but your insights will help me narrow down my issue a little better.

Yes, the form is bound - however there is one field in the table (the timestamp field) that has no form control associated with it,

and thus (to my understanding is an unbound field). It receives its value through the click event using an update qry.

All the other fields receive their values, of course, through the bound controls on the form.

Micron ~ If I understand your suggestion correctly, you would place a control on the form,

bind it to the (in this case) timestamp field, then simply update the control on the click event?

[Micron]

Yes. You can do so as Me.txtTimeStamp = Now(), then whatever action you need to take should update the field. To have a form based on 11 bound fields but have a field in the same table (thus related to the same record) as the 12th field but not bound makes no sense to me. Perhaps I am missing something though, like the timestamp field isn't where I think it is. I think ranman's 2nd question is implying the same approach, just expressed differently.

Keep in mind that there's more than 1 way to update/write a record: navigating off the record, clicking on main form (the update will happen to the subform), closing form, vba code - so you need to update the control first. Also, I just tested if Now() can be the default value for a table field because I couldn't recall. Seems that it can, so your solution may be as simple as setting table default to Now(), bind the control to it and forget it if you're not looking to set a particular value.

[RunTime91]

The initial design of this app was to simply update records via a userform - The idea to timestamp the update came later.
And, since the timestamp was not an updateable field there was no control alloted for it as it was set at runtime.
Well, I have now added a control to the form - Set its .visible prop to No - bound it to the timestamp field and set it to: Now() @ runtime
Problem solved
Thanks Guys - Very much!

[June7]

So this timestamp value is modified every time a record is edited?

[Micron]

not if it is the default value, no? That only applies to a new record?

[RunTime91]

Hey June ~

Users will go through each record and make updates. At the conclusion they click Submit which timestamps their changes, and moves to the next record thus saving their changes

Each time they go through this process and click submit that record is timestamped. If they update the same record more than once the timestamp will reflect the last time it was updated.

I hope that makes sense...

[Micron]

bound it to the timestamp field and set it to: Now() @ runtime

I guess by runtime you mean when some event like the button click occurs, and that you're using code to set the bound control value to Now(). You should be OK with that, but I guess I forgot the intent was for updating as well so just want to make sure you know that table default value won't work for updates.

[RunTime91]

Hey Micron ~ Yeah, because individual records might be updated multiple times I want to add the timestamp @ runtime (yes, when the user clicks the submit button) to always add the latest time when a record was updated - so, I'm not using a table default value for this one

Thank You, Micron

On a footnote - I still can't get rid of the Write Conflict Error
So I'm going to post the code and beg for help - I haven't been this frustrated in quite a long time...

Stay tuned Please!!! lol

[ArviLaanemets]

I think it will be better that instead of having button event updating the timestamp control you use BeforeUpdate event of form to do this. Then the timestamp will be updated regardless of method used to save the record. In case all the button currently does is to set timestamp and save the record, you even can consider removing the button then!

As about Write Conflict error, it is difficult to say anything for sure without an example DB uploaded here, but a couple of possible reasons may be:
1. The timestamp field in your table is calculated one;
2. You use an Update query for table with autonumeric field, and included this field into query.