Check whether a check box is ticked

Hi all,

Just a simple question I think but I am having probs to get it running...

I have a form that requires a date to be inputted when a task has been carried out .  I also have a check box which I want checked when the date is entered.

My idea is that:-

the check box should either be ticked automatically after the date is entered.
or
Should be manually checked. i.e. a message should appear before the form is closed highlighting the fact that a date has been entered and the check box has not been ticked. If this is the case the form should be allowed to close until the check box is ticked or the date entered is removed.

Please let me know if the above is not clear enough.

Thanks all.
PipMicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Use the form's befpre update event:
Private Sub Form_BeforeUpdate(Cancel as integer)
    if nz(Me.chkBox) = False and ""Me.txtDate <> "" then
          msgbox "you have to either remove the date or check the box"
           Cancel = true
    end if
end sub

Open in new window

0
mbizupCommented:
Or in the AfterUpdate event of the date textbox:

If Me.txtDate & "" <> "" then Me.chkBox = true


(you might want to use this in conjunction with the form's before update event to ensure the user doesnt uncheck the box.)
0
PipMicAuthor Commented:
Hi,


tried the following with no luck... will try your code


Private Sub Actual_Resolution_Date_Enter()

If (Me.Actual_Resolution_Date <> Null,[Check159] = 1,"")
End If


End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PipMicAuthor Commented:
Hi mbizup

Tried

If Me.Actual_Resolution_Date  & "" <> "" then Me.Check159 = true

The code is accepted but when I enter the date nothing happens to the toggle...


I will now try the form approach...



0
MINDSUPERBCommented:
You may try:

Private Sub Actual_Resolution_Date_Enter()

If Me.Actual_Resolution_Date <> "" Then
Me.[Check159] = 1

End If

Sincerely,
Ed
0
mbizupCommented:
That code will fire after the user leaves the textbox (ie tabs to the next field or otherwise sets focus elsewhere).

Also, if you copy/pasted the entire sub including the Sub/EndSub lines, you need to make sure that the control is 'connected' to it's code.  To do this, in the forms Property sheet, click the "..." next to the textbox's After Update event and select Code Builder (and do the same for the After Update event).

If you simply copy paste a block of code without connecting it to the control, you may run into trouble with the event not running at all.
0
MINDSUPERBCommented:
The after update event is more appropriate.

Private Sub Actual_Resolution_Date_AfterUpdate()

If Me.Actual_Resolution_Date <> "" Then
Me.[Check159] = 1

End If

Ed

0
PipMicAuthor Commented:
Hi,

Tried these too.... no luck!!

_______________________________________________________

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Check159 = False And "Me.Actual_Resolution_Date <>"" Then
          MsgBox "you have to either remove the date or check the box"
           Cancel = True
    End If


End Sub

_________________________________________________________

Also tried this:-

Private Sub Form_BeforeUpdate(Cancel As Integer)

If nz(Me.Check159) = False And ""Me.Actual_Resolution_Date <>"" Then
          MsgBox "you have to either remove the date or check the box"
           Cancel = True
    End If


End Sub
0
mbizupCommented:
Double-check that the code is actually firing, and see my comment here: http:#a37236658
0
mbizupCommented:
<Tried these too.... no luck!!>

Also, what are you actually seeing?  Error messages or no response at all?

(it always helps to clarify what 'no luck' means :)  )
0
PipMicAuthor Commented:
Sorry guys,

Getting confused here. So far I have understood that two pieces of code have to be attached.

I should attach code A to the after update event of the date box.

and

I should attach code B to the Before update event of the form.

What is code A and code B:  :)
0
mbizupCommented:
Typo in the before Update event.

Try this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If nz(Me.Check159) = False And "" & Me.Actual_Resolution_Date <>"" Then
          MsgBox "you have to either remove the date or check the box"
           Cancel = True
    End If


End Sub 

Open in new window

0
PipMicAuthor Commented:
mbizup,,


when I attach the last bit of code that you've provided I get  a run time error,

"You entered an expression that has no value"

Please note that I have not attached any code to the date box!!
0
mbizupCommented:
This is code A -

In the AfterUpdate event of the date textbox:

If Me.Actual_Resolution_Date  & "" <> "" then Me.Check159 = true

Open in new window



This is Code B

In the Form's beforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If nz(Me.Check159) = False And "" & Me.Actual_Resolution_Date <>"" Then
          MsgBox "you have to either remove the date or check the box"
           Cancel = True
    End If


End Sub 

Open in new window


Use them both in conjunction with each other.
0
mbizupCommented:
<"You entered an expression that has no value">

Which line of code does it stop on when you click the Debug button, and what is highlighted?
0
PipMicAuthor Commented:
Mbizup..

I have also noticed that this even happens (the same error) if I dont enter a date. In other words the form should be saved even if there is no date in that field.

:(
0
PipMicAuthor Commented:

Hi,

it stops on the following line and highlighted in yellow!!


If Nz(Me.Check159) = False And "" & Me.Actual_Resolution_Date <> "" Then

(I am using A97)  :(
0
mbizupCommented:
Okay -

1.  Double-check the names of your controls (these should be names of the controls, not names of fields in the underlying table)

2.  I'm pretty sure that the syntax I posted is valid in A97, but try these syntactical variations:

>> If Nz(Me.Check159) = False And "" & Me.Actual_Resolution_Date <> "" Then

try rewriting that as:

If Nz(Me.Check159, 0) = False And "" & Me.Actual_Resolution_Date <> "" Then

Or this

If iif(isNull(Me.Check159),False, me.Check159) = False And "" & Me.Actual_Resolution_Date <> "" Then

0
PipMicAuthor Commented:
Tried this....

I still get runtime error!!!....


Private Sub Form_BeforeUpdate(Cancel As Integer)

If Nz(Me.Check159, 0) = False And "" & Me.Actual_Resolution_Date <> "" Then
          MsgBox "you have to either remove the date or check the box"
           Cancel = True
    End If


End Sub
0
mbizupCommented:
Did you try the second suggestion in my last comment?

If iif(isNull(Me.Check159),False, me.Check159) = False And "" & Me.Actual_Resolution_Date <> "" Then

Open in new window

(please give the exact runtime errors you are getting if any).

If this doesn't help, please post a sample database.
0
PipMicAuthor Commented:
mbizup yes  I did but nothing ....

I'll send a sample ok..... working on it now..   :)
0
PipMicAuthor Commented:
here is sample converted for A03
7sample2003.mdb
0
mbizupCommented:
Is there any reason that you are using a 'Frame' (an option group) as a border for your controls and not a simple rectangle?

If you are simply using this for a decorative border, you need to use a rectangle - not a frame.

A Frame is a special control (an option group) whose value is determined by checkbox or radio button selections that are mutually exclusive.  Checkboxes and radiobuttons will not behave normally in a frame (they will behave as expected in a rectangle).  This is causing your problems with the code posted...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PipMicAuthor Commented:
No not really.... hmmmmm..


The frame is not really necessary... is that the reason then?
0
mbizupCommented:
Yes.

- Remove the frame and the checkbox which is associated with it.

-  AFTER the frame is removed, add a new checkbox (if you add a new checkbox before removing the frame, you will have the same issues)

- Try the code again, using the new checkbox name...
0
PipMicAuthor Commented:
Removed the frame and still it wont work...


Have you trued amending the copy I sent??
0
PipMicAuthor Commented:
ooops Tried...  :)
0
mbizupCommented:
Yes - and it appears to work for me.

<and still it wont work...>

Again, please be specific about what you mean by "won't work".

It make it very difficult for us to help you debug issues if you don't let us know the errors (or incorrect results) that you are seeing.
0
PipMicAuthor Commented:
Yeepppeeeeee!!!

thanks a lot...phase one completed   :)
0
PipMicAuthor Commented:
good
0
mbizupCommented:
PipMic,

Go ahead and upload your latest revision of the database, showing the issue you are having with closing the form.
0
PipMicAuthor Commented:
Hi mbizup,

I'll upload sample.. in just a while
0
PipMicAuthor Commented:
Mbizup,

please find attached the sample.

Thanks once again for your time.
8sample2003.mdb
0
mbizupCommented:
To get the functionality you are describing, you need to:

1.  Disable the built-in close button (the  form's 'x' button) through th form's property sheet.
2. Move the check to the top of your code for your custom Close  button, with this modification:

If Nz(Me.Check159, 0) = False And "" & Me.Actual_Resolution_Date <> "" Then
          MsgBox "you have to either remove the date or check the box"
          Exit Sub
End If

Open in new window


That will ensure this check is made when the user clicks your Close button,

However all bets are off if the user clicks the x button to exit Access altogether (there is not much you can do about that).

See the sample:
 8sample2003.mdb

Also worth noting is that your checkbox is not bound to any field in your table.  So anytime you open your form, that checbox will be unchecked.

That doesn't seem quite right, and you may want to post a seperate thread about that if it is a problem.
0
PipMicAuthor Commented:
Thanks mbizup.

I will check it out immediately....also the bit about he tick box.

PS

How can award you more points??
0
mbizupCommented:
Did that work out okay?

Regarding points - Its not necessary here, but anytime you need that sort of help with a question, you can use the Request Attention button at the top of the thread to ask a Moderator for help.
0
PipMicAuthor Commented:
Yes thanks a lot...


have made some amendments just as you suggested...

Good stuff.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.