Update Query does not clear all records immediately

[Emma Aumack]

Hi All,

I have a form on which there are several "Flag" text fields which users use
to mark records. Next to each flag field there is a clear button that runs
the following code:

Private Sub btn_ClSA1Flag1_Click()
On Error GoTo Err_btn_ClSA1Flag1_Click

Dim stDocName As String

stDocName = "QryUpd_ClearSA1Flag1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Me.Requery
'DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_btn_ClSA1Flag1_Click:
Exit Sub

Err_btn_ClSA1Flag1_Click:
msgbox Err.Description
Resume Exit_btn_ClSA1Flag1_Click

End Sub


As it is, if I am in the last record that was "Flagged" and I click on the
clear button, that records Flag field does not get cleared. But when I
uncomment the second docmd.runquery (which runs the update query that clears
the flag field) it clears everything because I am no longer on the last
flagged records flag field. How do I get the last flagged field cleared,
without having to move off of the record and running the docmd.runquery
twice??

Thanks for your help.

Emma

 

[Allen Browne]

Is there any chance the current record in the form might be unsaved?

Assuming your form is bound, try inserting this code on a new line after the
DIM line:
If Me.Dirty Then Me.Dirty = False

Instead of OpenQuery, you could also try this:
dbEngine(0)(0).Execute stDocName, dbFailOnError
which should notify you if the query is not able to update all the records.
For an explanation of this, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Naeem Azizian]

Is there any chance the current record in the form might be unsaved?

Assuming your form is bound, try inserting this code on a new line after the
DIM line:
If Me.Dirty Then Me.Dirty = False

Instead of OpenQuery, you could also try this:
dbEngine(0)(0).Execute stDocName, dbFailOnError
which should notify you if the query is not able to update all the records.
For an explanation of this, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

First save the record and then do what you want to do,
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
when you don't save the record, it will load the old data from the
table and will not notice the changes you made in the form.

 

[Emma Aumack]

Thank you Allen!

I like your code much better. However, I am getting an error in the SQL
Update statement. Think you could help me out? Quotations just kill me.

Please see the updated code below:

Private Sub btn_ClFlag_Click()
On Error GoTo Err_btn_ClFlag_Click

Dim strSQL As String
Dim flgField As String

flgField = InputBox("Enter Flag #:", "Flag Field")
txtFlgfield = InputBox("Enter Flag Criteria to be Cleared. i.e.",
"Clear Flag" & flgField)

If Me.Dirty Then
Me.Dirty = False
End If

strSQL = "UPDATE Tbl_Contacts SET Tbl_Contacts.Flag" & _
flgField & "WHERE (((Tbl_Contacts.Flag" & flgField & ") = " &
txtFlgField & "));"

DBEngine(0)(0).Execute strSQL, dbFailOnError

Me.Requery


Exit_btn_ClFlag_Click:
Exit Sub

Err_btn_ClFlag_Click:
msgbox Err.Description
Resume Exit_btn_ClFlag_Click

End Sub

Thank you again!

--
www.bardpv.com
Tempe, Arizona

Is there any chance the current record in the form might be unsaved?

Assuming your form is bound, try inserting this code on a new line after the
DIM line:
If Me.Dirty Then Me.Dirty = False

Instead of OpenQuery, you could also try this:
dbEngine(0)(0).Execute stDocName, dbFailOnError
which should notify you if the query is not able to update all the records.
For an explanation of this, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Allen Browne]

To get a sample SQL statement, you can mock up a query and switch to SQL
View (View menu.)

For an explanation of how to handle the quotes, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen!

I like your code much better. However, I am getting an error in the SQL
Update statement. Think you could help me out? Quotations just kill me.

Please see the updated code below:

Private Sub btn_ClFlag_Click()
On Error GoTo Err_btn_ClFlag_Click

Dim strSQL As String
Dim flgField As String

flgField = InputBox("Enter Flag #:", "Flag Field")
txtFlgfield = InputBox("Enter Flag Criteria to be Cleared. i.e.",
"Clear Flag" & flgField)

If Me.Dirty Then
Me.Dirty = False
End If

strSQL = "UPDATE Tbl_Contacts SET Tbl_Contacts.Flag" & _
flgField & "WHERE (((Tbl_Contacts.Flag" & flgField & ") = " &
txtFlgField & "));"

DBEngine(0)(0).Execute strSQL, dbFailOnError

Me.Requery


Exit_btn_ClFlag_Click:
Exit Sub

Err_btn_ClFlag_Click:
msgbox Err.Description
Resume Exit_btn_ClFlag_Click

End Sub

Thank you again!

 

[Emma Aumack]

Hi Allen,

That's where I got this SQL statement. but I've modified it to allow the
user to enter in parameters, so that I don't have to have a Clear button for
each of the 9 Flag fields. This way the user selects the Flag field they
want to clear then enters the flag text they want cleared in that flag field.
However, I am getting the Update syntax error message and I can't figure out
why.

 

[Emma Aumack]

Okay, I think I've figured out the SQL statement error. I had not told the
Update query what to SET the text in the txtFlgField to.

But now I'm getting the error:

"Too Few parameters. Expected 1" when I put a "1" in the flgFieldNo
inputbox and some text to be cleared in the txtFlgField input box.

Is my inputbox code right?

Again, here is the updated code:

Private Sub btn_ClFlag_Click()
On Error GoTo Err_btn_ClFlag_Click

Dim strSQL As String
Dim flgFieldNo As String
Dim txtFlgfield As String

flgFieldNo = InputBox("Enter Flag #:", "Flag Field")
txtFlgfield = InputBox("Enter Flag Criteria to be Cleared.", "Clear
Flag" & flgFieldNo)

If Me.Dirty Then
Me.Dirty = False
End If

strSQL = "UPDATE Tbl_Contacts SET Tbl_Contacts.Flag" & _
flgFieldNo & " ="""" WHERE ((Tbl_Contacts.Flag" & flgFieldNo & ") =
" & txtFlgfield & ");"

DBEngine(0)(0).Execute strSQL, dbFailOnError

Me.Requery

Exit_btn_ClFlag_Click:
Exit Sub

Err_btn_ClFlag_Click:
msgbox Err.Description
Resume Exit_btn_ClFlag_Click

End Sub

 

[Emma Aumack]

Got it working! Thanks for your help