Yes/No Check box to update another field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a subform with a Yes/No check box (Tag)
When this is clicked the current date is entered in the Date field and the
key field PupilID from the main form is saved. This works fine using:

Private Sub Tag_Click()
Me.PupilID = Forms!SENReview.PupilID
Me.Date = Now()
End Sub

I also want the Date field to be made blank if the Tag box is unchecked.
I've tried variations on the following code:

Private Sub Tag_AfterUpdate()
If Me.Tag = "No" Then
Me.PupilID = Forms!SENReview.PupilID
Me.Date = Now()
Else
Me.Date = ""
End If
End Sub

This has no effect in AfterUpdate event.
Placing this code as a BeforeUpdate event causes an error.
Using it in the Click event prevents the date being saved at all!

Ideas please on how to solve what ought to be very simple to do!
 
=?Utf-8?B?cmlkZGVycw==?= <[email protected]>
wrote in
Hi

I have a subform with a Yes/No check box (Tag)
When this is clicked the current date is entered in the Date
field and the key field PupilID from the main form is saved.
This works fine using:

Private Sub Tag_Click()
Me.PupilID = Forms!SENReview.PupilID
Me.Date = Now()
End Sub

I also want the Date field to be made blank if the Tag box is
unchecked. I've tried variations on the following code:

Private Sub Tag_AfterUpdate()
If Me.Tag = "No" Then
Me.PupilID = Forms!SENReview.PupilID
Me.Date = Now()
Else
Me.Date = ""
End If
End Sub

This has no effect in AfterUpdate event.
Placing this code as a BeforeUpdate event causes an error.
Using it in the Click event prevents the date being saved at
all!

Ideas please on how to solve what ought to be very simple to
do!
Click to expand...
First change the fieldname date to something else.
IMPORTANT:

Date is a reserved word in Access. What you are trying to do is
set the computer's clock to "". .

Private Sub Tag_AfterUpdate()
if me.tag then
me.MyDate = null
else
Me.MyDate = Now()
end if
End Sub
 
In access "No" is equal to 0 and "Yes" is equal to -1
Private Sub Tag_AfterUpdate()
If Me.Tag = "0" Then
Me.PupilID = Forms!SENReview.PupilID
Me.Date = Now()
Else
Me.Date = ""
End If
End Sub
 
Hi Chris

Tried that also. The code works just the same using "True" as "-1"
Still no effect unless also I remove the Click event.
However this stops the date being stored in the first place!
Also I believe it should be Me.Tag = "-1" below.

Any other thoughts?
 
ridders said:
Hi

I have a subform with a Yes/No check box (Tag)
When this is clicked the current date is entered in the Date field and the
key field PupilID from the main form is saved. This works fine using:

Private Sub Tag_Click()
Me.PupilID = Forms!SENReview.PupilID
Me.Date = Now()
End Sub

I also want the Date field to be made blank if the Tag box is unchecked.
I've tried variations on the following code:

Private Sub Tag_AfterUpdate()
If Me.Tag = "No" Then
Me.PupilID = Forms!SENReview.PupilID
Me.Date = Now()
Else
Me.Date = ""
End If
End Sub

This has no effect in AfterUpdate event.
Placing this code as a BeforeUpdate event causes an error.
Using it in the Click event prevents the date being saved at all!

Ideas please on how to solve what ought to be very simple to do!
Click to expand...

Checkbox controls have a value of True (checked) or False (unchecked). Code
the Click event something like this:

If Me.Tag = True Then
Me.PupilID = Forms!SENReview.PupilID
Me.Date = Now()
Else
Me.Date = Null
End If


Carl Rapson
 
Bob

Thanks for your reply
I accept your point about the Date field and have changed that.
However your code still does not do the job!

Using the On Click event on its own with no If statement allows the date to
be set. Using this with an If statement stops the date being set in the first
place.

Using the AfterUpdate event on its own allows me to remove the date but not
set it in the first place.

The effect of using both statements is the same as just using On Click.

I'm still stuck.
 
=?Utf-8?B?cmlkZGVycw==?= <[email protected]>
wrote in
Bob

Thanks for your reply
I accept your point about the Date field and have changed
that. However your code still does not do the job!

Using the On Click event on its own with no If statement
allows the date to be set. Using this with an If statement
stops the date being set in the first place.

Using the AfterUpdate event on its own allows me to remove the
date but not set it in the first place.

The effect of using both statements is the same as just using
On Click.

I'm still stuck.
Click to expand...

it sounds like the two events are clashing. Try with the if
statement in the onclick, and nothing in the AfterUpdate event.
Bob Quintal said:
=?Utf-8?B?cmlkZGVycw==?= <[email protected]>
wrote in

First change the fieldname date to something else.
IMPORTANT:

Date is a reserved word in Access. What you are trying to do
is set the computer's clock to "". .

Private Sub Tag_AfterUpdate()
if me.tag then
me.MyDate = null
else
Me.MyDate = Now()
end if
End Sub
Click to expand...
Click to expand...
 
Bob,
You said:
it sounds like the two events are clashing. Try with the if
statement in the onclick, and nothing in the AfterUpdate event.
Click to expand...

I have already tried using the If statement in On Click & no AfterUpdate
event.
This is no use at all.

Using the On Click event on its own with no If statement allows the date to
be set. Using this with an If statement stops the date being set in the first
place.
However existing dates are then removed on clicking Tag.

Simiarly using the AfterUpdate event with no On Click event allows me to
remove the date but not set it in the first place.

The effect of using both statements is the same as just using whatever code
is used in On Click.

I'm still stuck.

BTW Also changing the field name from Date to MyDate did nothing at all
except slow down the effect when Tag was clicked. With the changed name there
is a noticable delay (approx 2 seconds) before the date appears

--------------------------------------------------------------------------
 
=?Utf-8?B?cmlkZGVycw==?= <[email protected]>
wrote in
Bob,


I have already tried using the If statement in On Click & no
AfterUpdate event.
This is no use at all.

Using the On Click event on its own with no If statement
allows the date to be set. Using this with an If statement
stops the date being set in the first place.
However existing dates are then removed on clicking Tag.

Simiarly using the AfterUpdate event with no On Click event
allows me to remove the date but not set it in the first
place.

The effect of using both statements is the same as just using
whatever code is used in On Click.

I'm still stuck.

BTW Also changing the field name from Date to MyDate did
nothing at all except slow down the effect when Tag was
clicked. With the changed name there is a noticable delay
(approx 2 seconds) before the date appears

-------------------------------------------------------------- -
-----------
Click to expand...

I just created a form and put the two controls on it, and found
a problem. the word tag is a reserved word in Access.

Change the name of the checkbox to chktag, and change the names
of the events, and the code. Youll also want to inverse the
positions of the null and now() in the code.

Then it works, all from the afterupdate event.

As to the slowdown, Uncheck the Menu->Tools->Options->General->
nameAutoCorrect. This has been known to cause slowdowns and
worse.
 
Bob

Thanks. you are a star!
I knew Date was a reserved word but had no idea about Tag.

All now working correctly & quickly.
I always have Name..AutoCorrect turned off having suffered huge problems
with it in the past.

Now i have a different problem - error 2467 on a different form!
I've also had that error before & it was a pain to fix.
It looks like another post for tomorrow I think!

Thanks very much for your help
 
=?Utf-8?B?cmlkZGVycw==?= <[email protected]>
wrote in
Bob

Thanks. you are a star!
I knew Date was a reserved word but had no idea about Tag.
Click to expand...

I forgot all about it, it's a property I rarely use.
All now working correctly & quickly.
I always have Name..AutoCorrect turned off having suffered
huge problems with it in the past.

Now i have a different problem - error 2467 on a different
form! I've also had that error before & it was a pain to fix.
It looks like another post for tomorrow I think!
Click to expand...
That's the dreaded Application Defined error.
I've seen this when the odbc driver gets messed up, because the
linked table has changed, but the locally stored definitions
have not.
Thanks very much for your help
Click to expand...
 
Back
Top