Yes/No Check box to update another field

[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!

 

[Bob Quintal]

=?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!

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

 

[Guest]

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

 

[Guest]

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?

 

[Carl Rapson]

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!

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

 

[Guest]

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.

 

[Bob Quintal]

=?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.

it sounds like the two events are clashing. Try with the if
statement in the onclick, and nothing in the AfterUpdate event.

=?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

 

[Guest]

Bob,

it sounds like the two events are clashing. Try with the if
statement in the onclick, and nothing in the AfterUpdate event.

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

--------------------------------------------------------------------------

 

[Bob Quintal]

=?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

-------------------------------------------------------------- -
-----------

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.

 

[Guest]

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

 

[Bob Quintal]

=?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.

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!

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