Unanswered: Write Conflict while saving/closing form
I have a bound form which is giving me a very annoying 'Write Conflict' dialog box when clicking the Close and sdave form button. The 'Write Conflict' dialog box indicates 'a record has been changed by another user since you opned....'
This is a local .mdf file which I am the only one opening. Please help!!! I have done very little Access work. I am an ASP coder who usually uses Access as a backend only.
What I am trying to achieve:
> I have a combo box which has various close status's amoung others
> Since the form is bound and the values update on blur (or it seems that way) I figure I can write a macro which calls an SQL query to check if one of the close status has been chosen. If this is true themn all I need to do is update a Close Date field with the current date.
Here is my Query which gets called by a macro when the user clicks the save and close button:
UPDATE TBL_Support_Call SET TBL_Support_Call.[Close Date] = Now()
WHERE ((TBL_Support_Call.Ref=Forms!FRM_Support_Call1!Ref ) And (TBL_Support_Call.Status="Closed - On first contact" Or TBL_Support_Call.Status="Closed - Logged in WRS" Or TBL_Support_Call.Status="Closed"));
Please note - the query runs fine if I run it direct (after entering the ID of course)
Further to this if I open an existing record using the form talked about above and simply submit without altering any details I do not get the error. However if I alter a value and then click the save and close button I get the conflict. Very annoying, im sure this something simple??
and carried out the steps however it just gave me a different type of dialog box.
;;;Make sure you save the record before running your update
Doesnt a bound form save as you cursor through the form? If so the data saves its self. Sorry Im not following you. COuld I trouble you for a more details (dummed down) explanation?
I did try this. I placed your code in a function which is called from a macro which is called from the close and save button however I get the same write conflict.
There was some urgency to get this done so I have worked around it. I just couldnt figure out how to write to that record. I have now made a statusChangeHistory table and simply write the date to the new table without issue. It would have been nice to simply update the date in the original table however.........
As described I have created the history table which has a relationship to the main table. Of course this is one to many. One issue which may have its status changed multiple times. Therefore record 1010 will have five entries in the statusHistory table if the status has been altered five times. I then tried to write a macro which runs the following SQL (outside of the form):
UPDATE TBL_Support_Call SET [Close Date] = (SELECT TOP 1 CloseDate FROM TBL_CLose_Dates WHERE refID=Forms!FRM_Support_Call1!Ref ORDER BY CloseDate DESC)
My aim is to get the most reccent closedDate from the history table and insert it into the main table. The SQL is valid syntax but I get a 'updatable query' issue. When I write ASP when working with Access this is because of permissions on the folder which holds the .mdf file - I am confused why I cant run the query, any ideas on this?
Well, without a lot more detail about how your form works and what state things are in when the code is run, I really don't know how else to help you. Somehow or other, you are modifying data and trying to modify that data again before the first edit is saved.