Update Query does not clear all records immediately

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
Click to expand...

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


Allen Browne said:
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
Click to expand...
 
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.

Emma Aumack said:
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!
Click to expand...
 
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.
 
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
 
Back
Top