Solved Annoying Write Conflict when using SQL Update

[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.

 

[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.