Update Query does not clear all records immediately

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Emma Aumack - 28 Jun 2007 00:52 GMT

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

Signature

www.bardpv.com
Tempe, Arizona

Reply to this Message

Allen Browne - 28 Jun 2007 03:06 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi All,
>
[quoted text clipped - 35 lines]
>
> Emma

Reply to this Message

Naeem Azizian - 28 Jun 2007 07:46 GMT

> Is there any chance the current record in the form might be unsaved?
>
[quoted text clipped - 57 lines]
> >www.bardpv.com
> > Tempe, Arizona

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.

Reply to this Message

Emma Aumack - 28 Jun 2007 17:38 GMT

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!

Signature

www.bardpv.com
Tempe, Arizona

> Is there any chance the current record in the form might be unsaved?
>
[quoted text clipped - 49 lines]
> >
> > Emma

Reply to this Message

Allen Browne - 28 Jun 2007 18:26 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thank you Allen!
>
[quoted text clipped - 93 lines]
>> >
>> > Emma

Reply to this Message

Emma Aumack - 28 Jun 2007 18:50 GMT

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.

Signature

www.bardpv.com
Tempe, Arizona

> To get a sample SQL statement, you can mock up a query and switch to SQL
> View (View menu.)
[quoted text clipped - 101 lines]
> >> >
> >> > Emma

Reply to this Message

Emma Aumack - 28 Jun 2007 19:02 GMT

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

Signature

www.bardpv.com
Tempe, Arizona

> Hi Allen,
>
[quoted text clipped - 110 lines]
> > >> >
> > >> > Emma

Reply to this Message

Emma Aumack - 28 Jun 2007 22:42 GMT

Got it working! Thanks for your help

Signature

www.bardpv.com
Tempe, Arizona

> Hi Allen,
>
[quoted text clipped - 110 lines]
> > >> >
> > >> > Emma

Reply to this Message