Unable to clear all checkboxes ?

[Peter]

We have created a field to identify whether the permit has been printed or
not.

Then we create a button in a form that linked to a delete query -
qryResetPrintPermit (It reset that field to false).

When we run the query, it does reset all selected checkboxes.

However, when we click the button, it delete all except the last selected
checkbox. We just wonder what is the cause of the problem.

The major code for the procedure attached to the button is as follow:

stDocName = "qryResetPrintPermit"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70


Thanks

 

[Allen Browne]

Are you really trying to delete records?
Or are you just wanting to set a yes/no field to No?

To reset all the field to No in all records, use code like this:
Dim strSql As String
If Me.Dirty Then Me.Dirty = False
strSql = "UPDATE [Table1] SET [MyYesNo] = False WHERE [MyYesNo] = True;"
CurrentDb.Execute strSql, dbFailOnError

For the broader question of tracking when the record has been printed, this
article might help:
Has the record been printed?
at:
http://allenbrowne.com/ser-72.html

 

[Peter]

Dear Allen,

Yes, you are right. We just want to reset the Yes/No field to No.

The query I use before is an update query (Not a delete query).

Your query works fine. I just would like to know why we have to use
Me.Dirty to check the changes ? Can you elaborate ?

Thanks for your help.

Regards,
Peter

Are you really trying to delete records?
Or are you just wanting to set a yes/no field to No?

To reset all the field to No in all records, use code like this:
Dim strSql As String
If Me.Dirty Then Me.Dirty = False
strSql = "UPDATE [Table1] SET [MyYesNo] = False WHERE [MyYesNo] =
True;"
CurrentDb.Execute strSql, dbFailOnError

For the broader question of tracking when the record has been printed,
this article might help:
Has the record been printed?
at:
http://allenbrowne.com/ser-72.html

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

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

We have created a field to identify whether the permit has been printed
or not.

Then we create a button in a form that linked to a delete query -
qryResetPrintPermit (It reset that field to false).

When we run the query, it does reset all selected checkboxes.

However, when we click the button, it delete all except the last selected
checkbox. We just wonder what is the cause of the problem.

The major code for the procedure attached to the button is as follow:

stDocName = "qryResetPrintPermit"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

 

[Allen Browne]

If you are editing a record in a bound form, and you click the button to
execute the update query, there will be a locking issue with the record that
is already being edited in the form.

So, it makes sense to force any changes to be saved before you try to
execute the update.

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

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

Dear Allen,

Yes, you are right. We just want to reset the Yes/No field to No.

The query I use before is an update query (Not a delete query).

Your query works fine. I just would like to know why we have to use
Me.Dirty to check the changes ? Can you elaborate ?

Thanks for your help.

Regards,
Peter

Are you really trying to delete records?
Or are you just wanting to set a yes/no field to No?

To reset all the field to No in all records, use code like this:
Dim strSql As String
If Me.Dirty Then Me.Dirty = False
strSql = "UPDATE [Table1] SET [MyYesNo] = False WHERE [MyYesNo] =
True;"
CurrentDb.Execute strSql, dbFailOnError

For the broader question of tracking when the record has been printed,
this article might help:
Has the record been printed?
at:
http://allenbrowne.com/ser-72.html

 

[Peter]

Dear Allen,

Thanks for your prompt reply.

In other words, Me.Dirty not only indicates that something has been edited
but also save the change when we set it to False. Is my understanding
correct ?

Thanks again
Peter

If you are editing a record in a bound form, and you click the button to
execute the update query, there will be a locking issue with the record
that is already being edited in the form.

So, it makes sense to force any changes to be saved before you try to
execute the update.

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

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

Dear Allen,

Yes, you are right. We just want to reset the Yes/No field to No.

The query I use before is an update query (Not a delete query).

Your query works fine. I just would like to know why we have to use
Me.Dirty to check the changes ? Can you elaborate ?

Thanks for your help.

Regards,
Peter

Are you really trying to delete records?
Or are you just wanting to set a yes/no field to No?

To reset all the field to No in all records, use code like this:
Dim strSql As String
If Me.Dirty Then Me.Dirty = False
strSql = "UPDATE [Table1] SET [MyYesNo] = False WHERE [MyYesNo] =
True;"
CurrentDb.Execute strSql, dbFailOnError

For the broader question of tracking when the record has been printed,
this article might help:
Has the record been printed?
at:
http://allenbrowne.com/ser-72.html

 

[Allen Browne]

Correct.

If Me.Dirty is true, then an unsaved edit is in progress.
Setting Me.Dirty to False instructs Access to save the record.

You get a trappable error if the save does not succeed (e.g. if a required
field is missing.)