Command Button to Uncheck Checkbox on all records

[Kate]

I create a command button to uncheck a checkbox on the form, which works, but
I want it to uncheck the the same checkbox on all records. I am doing this
for the church and am not using access on a regular basis, so I just need the
rest of the code. Thanks for any help.

 

[Steve Schapel]

Kate,

You will need an Update Query. You can do this in code, which runs on
the Click event of your command button, like this:

CurrentDb.Execute "UPDATE YourTable SET YourYesNoField = 0", dbFailOnError

 

[Kate]

I am geeting an error. My table is named Addresses and my checkbox is named
IndividualReport.
Here is my code:
Private Sub Command239_Click()
If Me.IndividualReport = True Then
Me.IndividualReport = False
End If
'CurrentDb.Execute "UPDATE YourTable SET YourYesNoField = 0", dbFailOnError
CurrentDb.Execute "UPDATE Addresses SET IndividualReport = 0", dbFailOnError
End Sub

I don't know any code.
Thanks

 

[Steve Schapel]

Kate,

Thanks for letting us know the names of your table and field, that helps
a lot. However, I don't know what the error message says.

 

[Kate]

When I click on command button I Get This Error.

Run-Time Error 3061
Too few parameters
Expected 1

Thanks for helping.

 

[Steve Schapel]

Thanks Kate. Well, you can remove the original code. So just leave the
one line of code:
Private Sub Command239_Click()
CurrentDb.Execute "UPDATE Addresses SET IndividualReport = 0",
dbFailOnError
End Sub

And then, can you please double check and make quite sure that the name
of the field in the Addresses table is IndividualReport and that you
have it 100% correctly spelt in the code, and the same for the name of
the table.

 

[Kate]

Thanks Steve,
It is working now. There was a space between Individual & Report ,so I put
it in brackets. It is a little slow updating 30 second or so, but a lot
faster than manually unchecking all of them. One more question if you don't
mind. I have a checkbox for single & one for married. I would like to make
sure if one is checked the other can't be. Would something like
If Single=true then Married=false
Should you use true or -1 or false for 0
Thanks again.

 

[Steve Schapel]

Kate,

I can't imagine why the update is so slow. Even if you had millions of
records and you running the database on a very old machine with
insufficient memory, 30 seconds is outside the realms of fantasy.

As for the Married/Single, basically this is a design flaw. Are you
able to consider a change to your table/form structure? You should just
have one field for Marital Status.

 

[Kate]

I am running it on a P4 2.4 with 1 gig Memory. There are less than 200
records in it. I have realized now I should have used a group with option
buttons, but I am querying the married checkbox for a mail merge. Here is my
query: Married is the checkbox. What I want is to put an & sign between
firstname & spoure if married.
This works:
Name: IIf([Married]=-1,[FirstName] & " & " & [SpouseName],[FirstName])

I tried this it doesn't work.
Name1: IIf([SpouseName]=Null,[FirstName],[FirstName] & " & " & [SpouseName])
This puts an & sign after the single and married person.

Like I said before I don't know alot about seting up a database correctly.
I am just trying to help out the church.

Thanks again for your replys.

 

[Steve Schapel]

Kate,

No worries, I'm not expecting you to be an expert. Just wondering
whether you could change it, that's all.

The amount of time a line of code such as we discussed
CurrentDb.Execute... would take to run on your computer would be
measured in microseconds - it would be so quick you couldn't detect it.
So there must be something else causing this 30 second whatever it is.

"[SpouseName]=Null" does not compute. The syntax should be:
IIf([SpouseName] Is Null, ...
or:
IIf(IsNull([SposeName]), ...

Using the existence of data in SpouseName as the test would be more
reliable, and therefore the value of the Married and Single fields
becomes irrelevant, right?

Another way to write in might be like this:
Name1: [FirstName] & IIf(IsNull([SpouseName]),""," & " & [SpouseName])

Here's how I would do it myself, neater but perhaps less inuitive:
Name1: [FirstName] & " & "+[SpouseName]

By the way, as an aside, 'name' is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control.

 

[Kate]

Steve,
Thanks again for the help. I just replace the = sign with IS and it works. I
was really close. I did a test and checked some boxes and pressed the command
button then moved to a another record and the box was unchecked. I moved to
other records as fast as I could and they were all unchecked, all but the
record I was on. Does there need to be a refresh on this somewhere. If you
stay on the same record it looks like it isn't doing anything. Also on the
married and Single should I use an option button instead of the checkboxes.
You have been a great help.


--
Kate
Just learning to use access

Kate,

No worries, I'm not expecting you to be an expert. Just wondering
whether you could change it, that's all.

The amount of time a line of code such as we discussed
CurrentDb.Execute... would take to run on your computer would be
measured in microseconds - it would be so quick you couldn't detect it.
So there must be something else causing this 30 second whatever it is.

"[SpouseName]=Null" does not compute. The syntax should be:
IIf([SpouseName] Is Null, ...
or:
IIf(IsNull([SposeName]), ...

Using the existence of data in SpouseName as the test would be more
reliable, and therefore the value of the Married and Single fields
becomes irrelevant, right?

Another way to write in might be like this:
Name1: [FirstName] & IIf(IsNull([SpouseName]),""," & " & [SpouseName])

Here's how I would do it myself, neater but perhaps less inuitive:
Name1: [FirstName] & " & "+[SpouseName]

By the way, as an aside, 'name' is a Reserved Word (i.e. has a special
meaning) in Access, and as such should not be used as the name of a
field or control.

--
Steve Schapel, Microsoft Access MVP

I am running it on a P4 2.4 with 1 gig Memory. There are less than 200
records in it. I have realized now I should have used a group with option
buttons, but I am querying the married checkbox for a mail merge. Here is my
query: Married is the checkbox. What I want is to put an & sign between
firstname & spoure if married.
This works:
Name: IIf([Married]=-1,[FirstName] & " & " & [SpouseName],[FirstName])

I tried this it doesn't work.
Name1: IIf([SpouseName]=Null,[FirstName],[FirstName] & " & " & [SpouseName])
This puts an & sign after the single and married person.

Like I said before I don't know alot about seting up a database correctly.
I am just trying to help out the church.

Thanks again for your replys.

 

[Steve Schapel]

Kate,

Ah, yes, if you are running the update of the Addresses table from a
form that is based on the Addresses table, then in order to see the
results of the update you will probably need to do like this:
Me.Requery
.... after the CurrentDb.Execute... line.

It doesn't matter whether you use an option button or a checkbox. The
functionality is identical. The issue I was referring to before, if you
are interested, is different. It is the problem of there being 2
separate fields for Married and Single. So you have a checkbox bound to
the Married field which is a Yes/No field, and you have another checkbox
bound to the Single field, which is a Yes/No data type. This is known
as the "fields as data trap". "Married" and "Single" are data, relating
to a more general data attribute which we could call Marital Status.
Depending on your database's purpose, you might even allow other
options, such as Divorced, Widowed, etc. But they are all mutually
exclusive. So the "correct" database design would be to just have one
field, for Marital Status. Now, once you make that decision, the way to
record it you have a choice. It could be a Text data type, and you
enter the data on your form via a combobox which lists all the possible
marital status options, including "married" or "single". Or you could
make it a Number data type field, and you can enter the data via an
Option Group on your form. In the case of an Option Group, the group
itself is bound to the Marital Status field, and the Option Group
contains a checkbox (or option button, doesn't matter) for each
allowable option, such as married, single, etc., whereupon you can only
ever select one or the other. Hope that helps.