Solved Annoying Write Conflict when using SQL Update (1 Viewer)

[Malcolm17]

Hi,

I have a continuous form that I use to filter out checklist items, I am looking to change my category number for each category\heading when I update one of the textboxes on my form, so all other textboxes with the same heading will be the same, I am using the following code however I then meet the horrible Write Conflict message, is there a way I can always accept this as Save or not appear at all? It could be around 10 - 20 records I am looking to change at a time.

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tbl_Net_Checklists SET CatOrder = Forms!frmBackground!subfrmChecklists!txtCatOrder WHERE Checklist = Forms!frmBackground!subfrmChecklists!cboChecklist AND Category = Forms!frmBackground!subfrmChecklists!cboCategory"
DoCmd.SetWarnings True
Forms!frmBackground!subfrmChecklists.Requery

I have also tried opening another 'temporary' form that has the code and closes, however this still brings the same problem.

I also tried this code, however it wasn't that simple and only changed the one record.

Is there other ways maybe or am I missing something? My code does work, it is just the Write Conflict box that is the problem, it just makes it look untidy.

Thank you,

Malcolm

 

[Minty]

I will make the assumption that the reason for the error is that you are updating the same records with the query that are also being displayed on the form.

You could either use VBA to change the values ON the form rather than in a separate query or alter the process so that the records you are viewing are a snapshot stored in a temporary table, that you base your update query on that?

 

[Malcolm17]

It would make sense because I am changing what I am displaying on the form.

How would I go about changing the values that are on the form please?

Form looks like this below, so with this data the 5 would change for all First Floor and 90 would change for all Second Floor

 

[Minty]

If that is a subform on the main form you could simply store the SQL statement, unload the subform, run the query, then reload the subform.
That removes the cause of the conflict.

 

[Malcolm17]

Is is a subform, when you say Unload and Store the SQL Statement, what do you mean by that? I have just been making subforms visible and not, is there a line of code I can use to unload then reload? Would I just copy the data that I need to use to make visible and requery to the main form textboxes?

 

[Minty]

To unload the subform you would remove its Control Source.
So something like this (Air code untested)

    Dim strSQL         as String
    Dim strSource     as String

    strSQL = " UPDATE tbl_Net_Checklists SET CatOrder = " & Forms!frmBackground!subfrmChecklists!txtCatOrder "
    strSQL = strSQL & "  WHERE Checklist = " & Forms!frmBackground!subfrmChecklists!cboChecklist & " AND "
    strSQL = strSQL & "  Category = " & Forms!frmBackground!subfrmChecklists!cboCategory

    ' Save the current source object
    strSource = Me.subfrmChecklists.SourceObject
    ' Remove it
    Me.subfrmChecklists.SourceObject= ""
    ' Run the query
    Currentdb.Execute strSQL, dbSeeChanges
    ' Releoad the subform
    Me.subfrmChecklists.SourceObject=strSource

This assumes all the criteria are numbers, if they are text you'll need to put single quotes around them.

 

[Pat Hartman]

There is something wrong with the logic of what you are doing. There should be a lookup table that has an ID and a desc.
5, First Floor
90, Second Floor

The only value stored would be 5 or 90, you would NEVER store the desc in any table except the lookup table. That way, you would use a combo to control entry. The user would pick First Floor and 5 would be saved in the bound control. If you later decided you wanted to have 5 show as 1st Floor, you would only change the one row in the lookup table that defines the lookup desc for 5.

It seems that you made a mistake and stored both fields in all tables and now have a conflict that you need to fix. I would NOT be using a form to do this. I would be using queries behind the scenes. Do totals query that selects just the two fields and a count.

Select ID, desc, Count(*) as CountOfValues
Group by ID, desc
From Yourtable.

Then do a second query that looks for ID's that occur more than once in the totals query and a third that looks for desc that occur more than once for the totals query. These two queries will give you the universe of errors.

Create a backup of the BE and make sure that everyone is out of the application. You should probably do this on the weekend to make it easy to shut everyone out.

You would then create an update query to fix each anomaly. As you run each of the error locating queries, the errors will decrease until they are gone.

Once the errors are all gone, then you need to fix the problem that caused them by removing the desc field from ALL tables except the lookup table. Now you need to test everything and fix the queries that use desc. For each one, delete the desc or for queries used by a report, replace it with a join to the lookup table. You do NOT want to end up back with the same problem so you must remove -totally- the desc field from all queries bound to forms. The desc can be included using a join for any query used for export to Excel or bound to a report. If you don't remove the desc field from the queries bound to forms, you will end up with an even worse mess on your hands.

Once you understand what the problem is and how it was caused, you can fix it in a couple of hours. The sooner you fix it, the sooner the app will stop saving bad data.

 

[Malcolm17]

Hi Pat, whilst I don't disagree with you, I was just trying to avoid a second table just for a sort order on what is going to be a really small table.

While working with your code Minty, I have run in to to a small problem. I get an error message:
Run-time Error 3075:
Syntax error (missing operator) in query expression 'Checklist = Bedrooms AND Category = First Floor'.
Error highlighted is:
CurrentDb.Execute strSQL, dbSeeChanges


I have adapted the code code slightly to:

    Dim strSQL         As String
    Dim strSource     As String

    strSQL = " UPDATE tbl_Net_Checklists SET CatOrder = " & Forms!frmBackground!subfrmChecklists!txtCatOrder
    strSQL = strSQL & "  WHERE Checklist = " & Forms!frmBackground!subfrmChecklists!cboChecklist & " AND "
    strSQL = strSQL & "  Category = " & Forms!frmBackground!subfrmChecklists!cboCategory

    ' Save the current source object
    strSource = Forms!frmBackground!subfrmChecklists.SourceObject
    ' Remove it
    Forms!frmBackground!subfrmChecklists.SourceObject = ""
    ' Run the query
    CurrentDb.Execute strSQL, dbSeeChanges
    ' Releoad the subform
    Forms!frmBackground!subfrmChecklists.cboChecklist.SourceObject = strSource

Can you see where I am going wrong please?

 

[Gasman]

You were advised to surround any text variables/ control values with single quotes in post #6?

 

[Malcolm17]

I tried single quotes and I get a couple of errors - am I putting them in the right places?

    strSQL = " UPDATE tbl_Net_Checklists SET CatOrder = " & Forms!frmBackground!subfrmChecklists!txtCatOrder
    strSQL = strSQL & "  WHERE Checklist = " & 'Forms!frmBackground!subfrmChecklists.cboChecklist' & " AND "
    strSQL = strSQL & "  Category = " & 'Forms!frmBackground!subfrmChecklists!cboCategory'

The error I get is:

Compile error: Expected: expression
or
Compile error: Syntax error

 

[CJ_London]

single quotes are in the wrong place (assuming checklist and category are text) - should be

strSQL = strSQL & " WHERE Checklist = '" & Forms!frmBackground!subfrmChecklists.cboChecklist & "' AND "
strSQL = strSQL & " Category = '" & Forms!frmBackground!subfrmChecklists!cboCategory & "'"

also pretty sure this is not correct

Forms!frmBackground!subfrmChecklists.cboChecklist

it should be

Forms!frmBackground!subfrmChecklists.form.cboChecklist

same for category

see this link about how to reference forms, subforms etc

Forms: Refer to Form and Subform properties and controls

 

[Gasman]

Debug.print strSql and see what you get.

 

[Malcolm17]

Sorry, I have been playing with this for 10 or so years and I'm learning all sorts of new things tonight

How do I Debug.print strSql?

I have a new problem, I have it updating and working with the following code, we are just sticking on the last part now (Reloading the Subform, I'm assuming loading as it was? I filter the subform with Checklist = Forms.frmBackground!subfrmChecklists!cboChecklist

    Dim strSQL         As String
    Dim strSource     As String

    strSQL = " UPDATE tbl_Net_Checklists SET CatOrder = " & Forms!frmBackground!subfrmChecklists!txtCatOrder
    strSQL = strSQL & " WHERE Checklist = '" & Forms.frmBackground!subfrmChecklists!cboChecklist & "' AND "
    strSQL = strSQL & " Category = '" & Forms.frmBackground!subfrmChecklists!cboCategory & "'"
    
    ' Save the current source object
    strSource = Forms!frmBackground!subfrmChecklists.SourceObject
    ' Remove it
    Forms!frmBackground!subfrmChecklists.SourceObject = ""
    ' Run the query
    CurrentDb.Execute strSQL, dbSeeChanges
    ' Releoad the subform
    Forms!frmBackground!subfrmChecklists!cboChecklist.SourceObject = strSource

Run-time error 2467:
The expression you entered refers to an object that is closed or doesn't exist.

If I take !cboChecklist away it reloads the form blank - so its how do I get it to reload with the original data please?

How can I fix this one please?

 

[bastanu]

Of course you do because you "unload" the subform; you need to store those values in variables:

Dim strSQL         As String
Dim strSource     As String
Dim lCatOrder as Long
Dim sChecklist as string
Dim sCategory as string

lCatOrder=Forms!frmBackground!subfrmChecklists!txtCatOrder
sChecklist=Forms.frmBackground!subfrmChecklists!cboChecklist
sCategory=Forms.frmBackground!subfrmChecklists!cboCategory

    strSQL = " UPDATE tbl_Net_Checklists SET CatOrder = " & lCatOrder
    strSQL = strSQL & " WHERE Checklist = '" & sChecklist & "' AND "
    strSQL = strSQL & " Category = '" & sCategory & "'"
    
    ' Save the current source object
    strSource = Forms!frmBackground!subfrmChecklists.SourceObject
    ' Remove it
    Forms!frmBackground!subfrmChecklists.SourceObject = ""
    ' Run the query
    CurrentDb.Execute strSQL, dbSeeChanges
    ' Releoad the subform
    Forms!frmBackground!subfrmChecklists!cboChecklist.SourceObject = strSource

Cheers,

 

[Malcolm17]

Fabulous, we have it fixed - thank you all!!

Code that works is:

Dim strSQL         As String
Dim strSource     As String
Dim lCatOrder As Long
Dim sChecklist As String
Dim sCategory As String

lCatOrder = Forms!frmBackground!subfrmChecklists!txtCatOrder
sChecklist = Forms.frmBackground!subfrmChecklists!cboChecklist
sCategory = Forms.frmBackground!subfrmChecklists!cboCategory

    strSQL = " UPDATE tbl_Net_Checklists SET CatOrder = " & Forms!frmBackground!subfrmChecklists!txtCatOrder
    strSQL = strSQL & " WHERE Checklist = '" & Forms.frmBackground!subfrmChecklists!cboChecklist & "' AND "
    strSQL = strSQL & " Category = '" & Forms.frmBackground!subfrmChecklists!cboCategory & "'"
    
    ' Save the current source object
    strSource = Forms!frmBackground!subfrmChecklists.SourceObject
    ' Remove it
    Forms!frmBackground!subfrmChecklists.SourceObject = ""
    ' Run the query
    CurrentDb.Execute strSQL, dbSeeChanges
    ' Releoad the subform
    Forms!frmBackground!subfrmChecklists.SourceObject = strSource
    Forms!frmBackground!subfrmChecklists!cboChecklist = sChecklist
    Forms!frmBackground!subfrmChecklists.Requery

 

[CJ_London]

glad you have solved your problem.

But surprised this actually works

Forms.frmBackground!subfrmChecklists!cboCategory

 

[GPGeorge]

Spoiler: etc

Hi Pat, whilst I don't disagree with you,

I was just trying to avoid a second table just for a sort order on what is going to be a really small table.

Spoiler: etc

While working with your code Minty, I have run in to to a small problem. I get an error message:
Run-time Error 3075:
Syntax error (missing operator) in query expression 'Checklist = Bedrooms AND Category = First Floor'.
Error highlighted is:
CurrentDb.Execute strSQL, dbSeeChanges


I have adapted the code code slightly to:

    Dim strSQL         As String
    Dim strSource     As String

    strSQL = " UPDATE tbl_Net_Checklists SET CatOrder = " & Forms!frmBackground!subfrmChecklists!txtCatOrder
    strSQL = strSQL & "  WHERE Checklist = " & Forms!frmBackground!subfrmChecklists!cboChecklist & " AND "
    strSQL = strSQL & "  Category = " & Forms!frmBackground!subfrmChecklists!cboCategory

    ' Save the current source object
    strSource = Forms!frmBackground!subfrmChecklists.SourceObject
    ' Remove it
    Forms!frmBackground!subfrmChecklists.SourceObject = ""
    ' Run the query
    CurrentDb.Execute strSQL, dbSeeChanges
    ' Releoad the subform
    Forms!frmBackground!subfrmChecklists.cboChecklist.SourceObject = strSource

Can you see where I am going wrong please?

There are a lot of aphorism appropriate to this point, but they all boil down to one idea: trying to circumvent best design practices leads to more problems, not less problems when implemented in the real world.

 

[Pat Hartman]

Hi Pat, whilst I don't disagree with you, I was just trying to avoid a second table just for a sort order on what is going to be a really small table.

This is EXACTLY the reason I created a table maintenance app that I add to all my applications. Once you understand how it works, you can simply add it to every new application. It doesn't matter how many simple lookups you have. The app handles them all with just Two tables, two forms, and two reports. It also includes rudimentary security so that you can allow users to manage some tables but not all of them. Even if you don't allow the users to manage any tables, you can still use the app yourself to manage the tables behind the scenes.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk