Write conflict - Access 2003

[mafrank101]

I have an Access 2003 front end that is linked to a separate Access 2003 back end database. I have written some VBA in the After Update event on a form to allow a field on the form to be autofilled when another field is changed. This appears to work fine, but when I change to another record after running it, I get a "Write Conflict" error. I'm given the option to "Save Changes" or "Drop Changes". If I "save changes" then my SQL code changes aren't kept. If I "Drop changes", then the result of the SQL code are kept. I'm currently working in a test database on my PC, so no one else has access to it.



I found solutions to this problem all over the web, however they are all based on having a SQL Server backend, and adding a Timestamp field to the table. The field I am updating is a date field, and I'm not using SQL Server.

The SQL code in the VBA appears to work fine, but I'll post it below because it's better to have too much info. If anyone has an idea, I appreciate any help.

Thanks

Dim SQLStr As String
Dim CurrentFile As String
DoCmd.SetWarnings (False)
CurrentFile = [Forms]![frmCP_Main]![CP_FileNo]

SQLStr = "SELECT cv_Fileno, MAX(CV_Voucher_DT) AS MaxPayment_Dt INTO TempFinalPmt FROM [CP-CustPrimary] INNER JOIN [CV-CustVoucher] ON [CP-CustPrimary].CP_FileNo = [CV-CustVoucher].CV_FileNo WHERE CP_FinalPayment_Dt IS NULL AND cp_Fileno = """ & CurrentFile & """ GROUP BY CV_FileNo;"

DoCmd.RunSQL SQLStr

DoCmd.RunSQL "UPDATE TempFinalPmt INNER JOIN [CP-CustPrimary] ON TempFinalPmt.cv_Fileno = [CP-CustPrimary].CP_FileNo SET [CP-CustPrimary].CP_FinalPayment_Dt = [maxpayment_dt];"

DoCmd.SetWarnings (True)

[NTC]

There isn't any issue requiring or involving sqlserver per se.

The generic issue is that you are attempting to both change a record via an update query and also be in that record at the same time.

You may be making this overly difficult. If you/user are in the record in the form - and want to auto change a value of one field based on another field - - then don't use an update query; instead just use a little vba in the form alone.

on the other hand if I misunderstand your scenario an you believe the update query to be mandatory - then first close the form, run the update while form is closed, then reopen the form.

Hope this helps.

[pkstormy]

You need a Refresh command or me.Requery or Forms!MyFormName.requery command before leaving that record on the form (ie. after your update code or before going to another record or closing the form).

See here: https://www.accessforums.net/code-re...tion-7761.html

Whenever you get a write conflict error (ie. Drop or Save Changes - it doesn't really matter which you click - it typically won't save.)

This is because MSAccess thinks that the record on the form is still being updated (ie. updates pending so to speak.) MSAccess tends to get confused when a form is open and a record is being updated which bypasses/disturbs (so to speak) it's connection of that record on the form to the table (another way of saying the same thing as NTC stated). You'll get the same type of error (see link above) whenever you also open a 2nd updateable form based on the same table/record and the 1st form is open on the same table/record. After you update data on the 2nd form and then close it, MSAccess doesn't know that it should automatically apply those changes to the 1st form. The above link shows a similar example to your situation as well.

A simple Refresh or me.requery or Forms!MyFormName.requery or Forms!MyFormName!MyFieldName.requery command to the form after your update coding will fix the issue.

If the error happens before the updating code, put the refresh (not a me.requery though) in the code before your updating code as well.

(Note also: If your updating code is in a module and not within the form's coding, you'll need to issue a: Forms!MyFormName.requery in the module's code. Unlike the Refresh command (which refreshes the form's data fields and stays on the same record but doesn't work in any functions outside of the form's events - ie. you can't do a: Forms!MyFormName.Refresh command), the requery command refreshes the form's data fields but then takes you to the first record on the form (which is why you wouldn't want to do a requery before your updating code). If you're using criteria to only have 1 record retrieved in the form's recordsource, this is ok. But if you have multiple records being retrieved within the form's recordsource, this may or may not be what you want. If your coding is in a module and you have multiple records within the form's recordsource, then consider putting your updating code in one of the events or as a function within the form itself, or retrieving only 1 record to the form's recordsource so you can utilize the Forms!MyFormName.requery command or possibly this: Forms!MyFormName!MyFieldName.requery or as NTC suggested, closing the form, and then running the update coding (using a variable).)

For example, the following vba code within the form (not in a module):

This typically produces the Drop/Save changes message...
me.SomeField.value = "something"
docmd.gotorecord,,acnewrec

This doesn't...
me.SomeField.value = "something"
Refresh
docmd.gotorecord,,acnewrec
or
me.SomeField.value = "something"
me.requery
docmd.gotorecord,,acnewrec

[PLSystems]

PKStormy, I have inserted your suggested code (all and parts of) at every juncture I could think of...OnDirty, OnClose, OnChange, OnLostFocus, OnDeactivate and others. No resolution.

The write conflict is certainly due to a form that is open on top of another form that writes back to the same table (Back end of my Front end and I'm still creating the database/gui so it is on one PC while I create).

The conflict is ONLY after the initial opening of the first form and with no other controls being activated other than to open the form that sits on top of this first form. If there is activity on the first form, no write conflict occurs when the second form is opened, altered and closed. Why??

I've had this issue before and resolved it but not this time and I can't remember what solutions I've employed in the past (that really annoys me). I understand the concept but I'm not getting anywhere.

Where should your suggested code be inserted...
BTW, the ONLY code currently on the second form (datasheet view with only one field that is updateable) is code for a CLOSE button (DoCmd.Close)...

The "Drop Changes" choice does save the changes.

Yeah, I'm frustrated

----------------------------------------
Changed how the form was opened. Instead of a pop-up (modal) form it is now opened as a subform but hidden and closed (re-hidden) with the same close button. Different coding, almost the same effect (no ability to make it modal but with code the main form is disabled while the subform is opened).

Thanks to anyone that was giving this issue some thought.

[jasonm]

PKStormy,

I am getting a write conflict error, although the 2 fields I update via an update query seem to be updating correctly. I am concerned about the write confict error that I am getting - just the fact that the error is coming up spooks me (although the desired result is happening). Should I just ignore the error? I am not sure how to go about trying to figure out what the cause is.

THX

J

[jasonm]

PKStormy,

I reviewed some of your suggestions above and I did a refresh before my update query and it seemed to work. But now I get a blank msgbox with just an ok button on it.

Thoughts??

THX

J

[pbaldy]

What is your code?

[jasonm]

What is your code?

Actually I was able to solve the problem via tinkering around - learn alot that way!