Suppress Write Conflict Warning when running Update Queries (1 Viewer)

DeanFran · Jul 24, 2017

I have a set of 3 cascading combo boxes in a form that are intended to let users update values in a table as they need to. There is no need to maintain the old values when the data is updated, so I am doing two things. When a combo box is updated, I run queries on the other two to refresh the values, and OnChange, I run and Update query to update the table fields to the new value. I get the Write Conflict warning as shown in attached. Is it possible to suppress this warning for all users? I assume that Access considers one of the queries to be another user in this case as I'm the only one using the database?

Minty · Jul 24, 2017

The fact that you have already changed data on the record (using your combo's) before running your update query is what is causing the warning.
If you save the record before running your update then you shouldn't get the error.

I would use

Code:

 If Me.Dirty Then Me.Dirty = False

to save any changes to the current record.

DeanFran · Jul 24, 2017

Beautiful. I added the suggested code in each Change() sub before the DoCmd.OpenQuery line. Thank you.

Edit* Whoops, I thought it worked, but it only works once. If I go back and try to repeat, it wipes away the cascading options. I guess I don't understand what you are suggesting. *Edit

Minty · Jul 24, 2017

I wouldn't use on change - use the after update event for combo's. On change will fire with each key stroke - which I doubt is your intention.

isladogs · Jul 24, 2017

It may not be relevant to your situation but I used to get this error with update queries which included a Boolean field and no default value had been set.

So if any value was null, Access threw a wobbly and showed the write conflict error.

SOLUTION -ALWAYS set a default value for Boolean fields

DeanFran · Jul 26, 2017

Still struggling.
This works albeit with the Write Conflict warning if I refresh or save.

Code:

Private Sub cboOne_AfterUpdate()
Me.cboTwo.Requery
Me.cboThree.Requery
Me.cboFour.Requery
DoCmd.OpenQuery "Level1CauseUpdateQ"
End Sub

This breaks the cascading combo boxes

Code:

Private Sub cboOne_AfterUpdate()
Me.cboTwo.Requery
Me.cboThree.Requery
Me.cboFour.Requery
DoCmd.OpenQuery "Level1CauseUpdateQ"
[COLOR=Red]If Me.Dirty Then Me.Dirty = False[/COLOR]
End Sub

isladogs · Jul 26, 2017

Have you tried this?

Code:

Private Sub cboOne_AfterUpdate()
[COLOR="Red"]If Me.Dirty Then Me.Dirty = False[/COLOR]
Me.cboTwo.Requery
Me.cboThree.Requery
Me.cboFour.Requery
DoCmd.OpenQuery "Level1CauseUpdateQ"

End Sub

DeanFran · Jul 26, 2017

Yes I have. It breaks the cascading combo boxes as well.

isladogs · Jul 26, 2017

DeanFran said:
Yes I have. It breaks the cascading combo boxes as well.

You need to give details of the combo boxes in order for someone to assist further. Better still upload a stripped down copy of your db

DeanFran · Jul 26, 2017

Its a bit of a mess as I'm early on in this, but here's the whole thing.

EDIT/ Focus on the form ExistingInvReportF /EDIT
Sorry, should have pointed that out.

Minty · Jul 26, 2017

I think I'm maybe missing the point here, but as your combo boxes are bound to the fields in the underlying table you don't need to run your update query at all?
All it seems to do is update the controls value to the value you have just changed it to anyway?

DeanFran · Jul 26, 2017

I added the update query because without it, the table field values wouldn't update if changes were made after the first time they were populated.
EDIT/
I took out the code to run the update queries and now the values are all updating, and the warning is gone as well. /EDIT
Lord have mercy, I swear I'm my own worst enemy. Thank you everyone who took the time to look at this mess. I'll do better next time.

isladogs · Jul 26, 2017

That's great. Saved me the need to look at your db!

Please mark the thread as SOLVED.

Minty · Jul 26, 2017

Edit this line is about your comment about it not updating the underlying values -- Are you sure? I've just checked and that's not the case.

What you would need to do is remove the existing values from level 2,3,and 4 if level 1 changes, and similarly 3 and 4 if 2 changes etc. as they wouldn't be valid any longer.

Your level2Q etc are correct but you don't need the bring in the table above at all. In fact this is where you have a problem, you have created two forms for existing and new records. You don't need to and then wouldn't need two sets of queries. Open the form with no data showing and have command buttons to search existing records or add new records and only maintain the one form.

Personally I don't save queries I use as sources for combo's unless they are really complex (which is really rare) I build and save them in the controls on the form. That way if I forget and alter the saved query without realising, I don't break my form.

I would be tempted to reverse you naming convention as well put the q t f r etc (in lower case) at the beginning of your object names that way you know what you are referring to at the start of the name, you don't need to scroll to the end to fin out. That's just personal taste though.

DeanFran · Jul 26, 2017

You have given me some food for thought. If you have time can you expand and explain this passage a bit?

Your level2Q etc are correct but you don't need the bring in the table above at all. In fact this is where you have a problem, you have created two forms for existing and new records. You don't need to and then wouldn't need two sets of queries. Open the form with no data showing and have command buttons to search existing records or add new records and only maintain the one form.

Minty · Jul 27, 2017

There are, as usual with Access, many ways to skin a cat.

You could open the same form in two ways - Add records or Edit - just change the forms opening mode in the menu used to open it, requires two menu command button - but you may already have those.
My original suggestion - open the form with a recordset where the ID field = 0, this will populate the form with no records. Have a search text box that will let users find existing records, and use that to update the recordsource accordingly. Also a button to just Add New, and possibly another one to show all records. This has the advantage of as the database grow you aren't dragging every record in on form load. There are numorous search examples on here or have a read of Allen Browne's excellent example here http://allenbrowne.com/ser-62.html

Suppress Write Conflict Warning when running Update Queries (1 Viewer)

DeanFran

Registered User.

Attachments

Minty

AWF VIP

DeanFran

Registered User.

Minty

AWF VIP

isladogs

MVP / VIP

DeanFran

Registered User.

isladogs

MVP / VIP

DeanFran

Registered User.

isladogs

MVP / VIP

DeanFran

Registered User.

Attachments

Minty

AWF VIP

DeanFran

Registered User.

isladogs

MVP / VIP

Minty

AWF VIP

DeanFran

Registered User.

Minty

AWF VIP

Similar threads

Users who are viewing this thread