Option Group problem

[DavisGail]

I am working in Access 2003. I have a form that has an option group with 4
possible status codes ( 1 - 4). The status codes are basically, 1:
Qualified and 2 -4 are different ways of being not qualified.

I have another field on the same form that is for the reason code for the
NOT qualified.

If the student is qualified (1) is chosen I want the not qualified code to
be 0. Otherwise I want it blank.
I put some code in the AfterUpdate of the option group:

If Me.ScreeningStatus.Value = 1 Then
Me.NotQualCode = 0
Else
Me.NotQualCode = " "
End If

This works okay, but if I click qualified (1) in error, and then click the
correct code (say 2), the 0 stays in the NotQualCode. How do I clear it?

 

[Dirk Goldgar]

I am working in Access 2003. I have a form that has an option group with 4
possible status codes ( 1 - 4). The status codes are basically, 1:
Qualified and 2 -4 are different ways of being not qualified.

I have another field on the same form that is for the reason code for the
NOT qualified.

If the student is qualified (1) is chosen I want the not qualified code to
be 0. Otherwise I want it blank.
I put some code in the AfterUpdate of the option group:

If Me.ScreeningStatus.Value = 1 Then
Me.NotQualCode = 0
Else
Me.NotQualCode = " "
End If

This works okay, but if I click qualified (1) in error, and then click the
correct code (say 2), the 0 stays in the NotQualCode. How do I clear it?

Is NotQualCode a bound control; that is, does it save its value in a field
of the form's recordsource? If so, what is the data type of that field? If
it's a number field, you cannot assign the character " " to it. You might
try

Else
Me.NotQualCode = Null
End If

.... and see if that works.

 

[DavisGail]

Yes, it is s bound control and the field type is a number. I changed "" to
null (which makes a lot of sense), but that didn't work. It is like when it
gets that 0 in there, it cannot be removed.

 

[DavisGail]

It has something to do with the NotQualCode being a required field. When I
make it not required, it works.

 

[Dirk Goldgar]

Yes, it is s bound control and the field type is a number. I changed ""
to
null (which makes a lot of sense), but that didn't work. It is like when
it
gets that 0 in there, it cannot be removed.

Hmm, that's not what should be happening. Am I right in understanding that
this happens on the same record; that is, you are looking at a particular
record, you click the option selection for "Qualified", NotQualCode is set
to 0, then -- without changing records -- you click one of the other option
selections, and NotQualCode remains 0. Correct? And no error message is
displayed, right?

"ScreeningStatus" is the name of the option group?

If all the above is true, please set a breakpoint in the code at the line:

If Me.ScreeningStatus.Value = 1 Then

Then go click one of the other options in the group, and when the code stops
at the breakpoint, step through the code to see what happens. If it doesn't
do what you expect, check the value of ScreeningStatus at that point, to
make sure it has the value you expect. Please report the result of all this
back here.

 

[Dirk Goldgar]

It has something to do with the NotQualCode being a required field. When
I
make it not required, it works.

That makes sense, because you cannot set a required field to Null. But I
would expect an error to be raised and a message displayed, which you didn't
report. Do you have an On Error Resume Next statement in your code?

If NotQualCode is a required field and you don't want to change that, then
you can't set it to Null, but you can undo an accidental setting of it to
anything else. That is, your code in ScreeningStatus_AfterUpdate() could be
something like this:

With Me.NotQualCode
If Me.ScreeningStatus = 1 Then
.Value = 0
Else
If .Value = 0 Then
.Undo
End If
End If
End With

That should give you the effect you want if NotQualCode was originally Null
before you first updated ScreeningStatus for this record.