By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,101 Members | 959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,101 IT Pros & Developers. It's quick & easy.

make all values identical

P: n/a
Mia
Hi,

I'm wondering how to press a button on a form in MS ACCESS and have all
values in a yes/no field in a table be UNCHECKED?
Thanks
Mia

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use VBA to run an UPDATE statement.

== untested ==

Private Sub cmdUncheck_Click(Cancel As Integer)

Const SQL_UPDATE = "UPDATE table_name " & _
"SET yes_no = 0 WHERE column_name = "
If Not IsNull(Me!txtCriteria) Then
CurrentDb.Execute SQL_UPDATE & Me!txtCriteria, dbFailOnError
Else
MsgBox "The criteria is missing", vbCritical
End If

End Sub

You'll have to change this to suit your control names, table and column
names & criteria needs. Put in an error trap. This uses DAO not ADO,
so be sure you have a Reference to the current DAO library (Modules
menu: Tools > References).

If the criteria is a string, be sure to wrap it in quotes:

CurrentDb.Execute SQL_UPDATE & _
"'" & Me!txtCriteria & "'", dbFailOnError

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQffrjYechKqOuFEgEQK1lwCfbkdIEgFcuj2umbeeOR6Xre +BsVcAoITA
VKg/WytomXiyD7/u8868OiZP
=fnD1
-----END PGP SIGNATURE-----
Mia wrote:
Hi,

I'm wondering how to press a button on a form in MS ACCESS and have all
values in a yes/no field in a table be UNCHECKED?

Nov 13 '05 #2

P: n/a
This seems quite simple so let me know if there's something I've
missed.

1 - Create an update query to which updates the relevant field (no
criteria as it seems you want it done to all the records) to the
unchecked and/or checked value (checked is -1 and unchecked is 0) or to
reflect the check box value on the form (see 2b below).

2 - It sounds like you want one button to basically change the value.
The problem here is working out what the current value is. The two
simplest ways, which aren't exactly what you want but what the hell:
a - Have two buttons one that checks them all and one that unchecks
them all.
b - Have a check box on the form which reflects the desired position
and either add an event "onchange" which runs the query to update the
values to this new value to this check box or add a button which causes
runs the query to match the current check box value.

The code needed to run a query is:

DoCmd.OpenQuery "qTheQuery"

"a" is probably preferred as "b" can potentially show the current
status incorrectly when the form is opened (unless you add some other
bit of code) where as "a", with no place to show the current status is
not as misleading.

The ultimate solution would be to have some code/query to work out the
current status and then run the appropriate query to change it to the
opposite status (and then cause something on the form to indicate this
new status). This is a little more complex but I think if you work
through the suggestion above then you should find it easier to work on
the more complex solution from there.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.