message box if a condition is true or false

[Guest]

I am rather new to access and I would like to have a message box appear if a
condition is either true or false. I have a yes/no checkbox that I would like
to have a message box appear for if the box is unchecked. I am not sure where
to begin with this at all and I would also like to use this across many
tables if possible. If I can figure out how do do it for one I think I can
get the rest to work.

Any help will be greatly appreciated.

 

[Suzette]

If you want the message to appear on a form as you scroll through records,
the code needs to be in the ON Current event of the form.

If ckbox = False Then MsgBox "False"

If you want the message to appear when the checkbox is clicked, you need
your code in the OnClick event of the checkbox. You will need one for each
table/form. You can't create code for the tables so you need to have forms.

 

[Guest]

I seem to have it working except for a little problem. When I open the form I
have the message box display if false for a particular field. The macro only
seems to work on the first record. If there are other fields that are false

 

[Steve Schapel]

Cory,

Your condition will only check the value of the Yes/No field in the
current record of the form. If you want the message box to show if
*any* of the records have a Yes value in the Yes/No field, you will need
to use a domain function. However, I have read your messages through,
and I can't quite grasp what you want to do... Are you able to give a
specific example?

Also, you will need to decide whether you are going to use a macro
(which is the subject of this newsgroup), or a VBA procedure, which is
sort of what Suzette is referring to, and also what event you want to
trigger the messagebox.

 

[Guest]

Thanks for all of the help so far. Here is an example of what I am doing and
what I am trying to do.

I am creating a Training Databse for several areas throughout our building.
For each area there is a table, forms and various reports. In each table
there are fields such as name, login ID, Training Dates (orientation, etc.)
and a Current with Standards check box. The Current with Standards check box
will allow our trainers to see if everyone who has been trained in a
particular area is up to date with the latest training revisions. I would
like to have an update query so if a new revision to our training comes out
then you can easily run the query and uncheck the "Current" box for every
record in a given table. Since there is a new revision everyone will need
retrained and brought up to speed with the current revisions.

The macro and message box will come into play after the records have been
edited by the update query or manually edited. I would like the message box
to appear on opening the form for editing. As people are retrained the
"current with standards" check box will be checked as proof they are up to
date. Until every record is checked I would like for the message box to
appear stating that 1 or more people or the following records show that not
everyone is up to date with the latest revisions. This message box will be a
constant reminder that retraining is needed. Once everyone is up to date and
all of the check boxes are "true" the message box will no longer appear until
a record is "false".

I hope this makes sense. Any help on this macro or the update query will be
greatly appreciated.

 

[Steve Schapel]

Cory,

Your update query will have to set the value of the Current With
Standards field to 0. I am not sure whether this means all the records
in the table, or if it will only relate to some of the training modules,
but either way it is pretty easy. If the former, the SQL view of the
query will look something like this...
UPDATE YourTable SET [Current with Standards] = 0

You can use an OpenQuery action in your macro to run this update.

As for the Messagebox, it sounds like what you want is to use a macro on
the After Update event of your form, using the MsgBox action. In the
Condition of the macro (select Condition from the View menu if you can't
see a Conditions column in the macro design window), you would put the
equivalent of this...
DCount("*","YourTable","[Current with Standards]=0")>0

By the way, to comment on something that is beyond the scope of your
original question, "for each area there is a table" would normally be
regarded as incorrect database design. You may want to revise this
approach.

 

[Guest]

Thank you very much. Everything is working perfectly. Know if I can work in
simliar things like checking for date expirations etc. I will be all set.
Anyone know of any other resources or links so I can read up on a few things?

Thanks again,
Cory

Cory,

Your update query will have to set the value of the Current With
Standards field to 0. I am not sure whether this means all the records
in the table, or if it will only relate to some of the training modules,
but either way it is pretty easy. If the former, the SQL view of the
query will look something like this...
UPDATE YourTable SET [Current with Standards] = 0

You can use an OpenQuery action in your macro to run this update.

As for the Messagebox, it sounds like what you want is to use a macro on
the After Update event of your form, using the MsgBox action. In the
Condition of the macro (select Condition from the View menu if you can't
see a Conditions column in the macro design window), you would put the
equivalent of this...
DCount("*","YourTable","[Current with Standards]=0")>0

By the way, to comment on something that is beyond the scope of your
original question, "for each area there is a table" would normally be
regarded as incorrect database design. You may want to revise this
approach.

--
Steve Schapel, Microsoft Access MVP

Thanks for all of the help so far. Here is an example of what I am doing and
what I am trying to do.

I am creating a Training Databse for several areas throughout our building.
For each area there is a table, forms and various reports. In each table
there are fields such as name, login ID, Training Dates (orientation, etc.)
and a Current with Standards check box. The Current with Standards check box
will allow our trainers to see if everyone who has been trained in a
particular area is up to date with the latest training revisions. I would
like to have an update query so if a new revision to our training comes out
then you can easily run the query and uncheck the "Current" box for every
record in a given table. Since there is a new revision everyone will need
retrained and brought up to speed with the current revisions.

The macro and message box will come into play after the records have been
edited by the update query or manually edited. I would like the message box
to appear on opening the form for editing. As people are retrained the
"current with standards" check box will be checked as proof they are up to
date. Until every record is checked I would like for the message box to
appear stating that 1 or more people or the following records show that not
everyone is up to date with the latest revisions. This message box will be a
constant reminder that retraining is needed. Once everyone is up to date and
all of the check boxes are "true" the message box will no longer appear until
a record is "false".

I hope this makes sense. Any help on this macro or the update query will be
greatly appreciated.