Unable to clear all checkboxes ?

  • Thread starter Thread starter Peter
  • Start date Start date
P

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




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

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

Peter said:
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




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

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

Peter said:
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




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