Re: More efficient code for If Else data validation rule?

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks for the explanations. In some situations I still have some trouble
wrapping my mind around the "True" part of an expression when "True" is not
explicitly stated. I had not considered that cboFavoriteColor being null
would change the expression, since I would have thought that anything other
than "Other", including null, would have the same effect on disabling
cboFavoriteColor. So now I have something else to look for when an
expression is not producing the expected results.
I suppose the OP's example is a simplified version of the actual situation,
since in the described situation it makes little sense I can see to have a
separate field to store the Other value when it could be typed into
FavoriteColor.

"tina" <nospam@xxxxxxxxxxx> wrote in message
news:z1ZUh.314601$5j1.71691@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Me!txtOther.Enabled = Nz((Me!cboFavoriteColor = "Other"), 0)

Actually, I don't get how that line of code is supposed to work, now that
I
look at it again.

when cboFavoriteColor = "Other" is a true statement, then the value of the
Enabled property = True; when cboFavoriteColor = "Other" is a false
statement, then the value of the Enabled property = False. when
cboFavoriteColor = "Other" evaluates to Null, then the value of the
Enabled
property = False as specified by the Nz() function.

Under what circumstances will it evaluate to -1?

it will evaluate to True when cboFaoriteColor = "Other" is a true
statement.

As I
understand, the sense of the code is:
Me.txtOther.Enabled = IIf(Me.cboFavoriteColor = "Other",-1,0)

essentially, except that the False value also encompasses Null, where
cboFavoriteColor doesn't equal anything, as i noted above. "toggle" code
works on Properties that have a True/False range of settings; based on the
fact that comparison statements evaluate to True or False. so you set the
Property equal to an equation, and the return value of the equation
becomes
the True/False value of the property. when an equation evaluates to Null,
an
error occurs because Null is not an acceptable value for a boolean
property
setting. that's where the Nz() function comes in, providing a specified
boolean value to replace Null.

hth


"BruceM" <bamoob@xxxxxxxxxxxxxxxxx> wrote in message
news:%23Ov6VjDgHHA.1240@xxxxxxxxxxxxxxxxxxxxxxx

"tina" <nospam@xxxxxxxxxxx> wrote in message
news:NpLUh.310933$5j1.149827@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
It seems to me that these two lines may need to be reversed:
Cancel = True
Me!cboFavoriteColor.Undo

if i'm going to cancel an event, i normally do it before taking
additional
actions in the procedure, and in this case it works fine in my test.
but
Access can be weird about these things, and reversing the two lines of
code
is certainly worth a try if the op's problem persists.

Maybe I misunderstand how Cancel works, but I had thought that the code
stops running once Cancel occurs.


Try -1 instead of 0 in the Enabled line of code.

that would work for the specific problem the op is reporting, but the
downside is that the user would be able to enter an "other" color in
the
textbox without choosing anything in the combo box at all.

Actually, I don't get how that line of code is supposed to work, now that
I
look at it again. Under what circumstances will it evaluate to -1? As I
understand, the sense of the code is:
Me.txtOther.Enabled = IIf(Me.cboFavoriteColor = "Other",-1,0)


I think you will need something in the form's Current event to enable
or
disable txtOther, or else the next record will just inherit the
current
property.

running the above "toggle" code in the form's Current event, as i
posted,
appropriately toggles the txtOther.Enabled property for each record.

Oops, sorry, I missed that.

hth


"BruceM" <bamoob@xxxxxxxxxxxxxxxxx> wrote in message
news:uuF0pFCgHHA.1008@xxxxxxxxxxxxxxxxxxxxxxx
Would it work in your case just to set the Limit To List property for
the
combo box to No? Perhaps you offered a simplified example, and this
suggestion will not work in the actual scenario, but I'll toss it out
there
anyhow.
It seems to me that these two lines may need to be reversed:
Cancel = True
Me!cboFavoriteColor.Undo

Try -1 instead of 0 in the Enabled line of code.

I think you will need something in the form's Current event to enable
or
disable txtOther, or else the next record will just inherit the
current
property.

<kheisler6@xxxxxxx> wrote in message
news:1176665824.380423.175360@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Your code works well, except if the user says No to "Dump the Other
color?", txtOther gets disabled (grayed out) - it should stay
enabled.
The user needs to select Other again in cboFavoriteColor to make
txtOther enabled.

I would think that the last line of the Before Update event ...

Me!txtOther.Enabled = Nz((Me!cboFavoriteColor = "Other"), 0)

... would do it, but it seems to be getting skipped?

And your OnCurrent code is great, too - I've been using tedious If
Else statements for that as well.

Kurt

On Apr 14, 9:59 pm, "tina" <nos...@xxxxxxxxxxx> wrote:
here's some alternate code for you to test drive. suggest you make
a
copy
of
your form, and add the code to the copy for testing - saves the
headache
of
replacing code.

Private Sub cboFavoriteColor_BeforeUpdate(Cancel As Integer)

If Not IsNull(Me!txtOther) Then
If IsNull(Me!cboFavoriteColor) Then
Me!txtOther = Null
ElseIf Not Me!cboFavoriteColor = "Other" Then
If MsgBox("Dump the Other color?", _
vbYesNo + vbDefaultButton2) = vbYes Then
Me!txtOther = Null
Else
Cancel = True
Me!cboFavoriteColor.Undo
End If
End If
End If

Me!txtOther.Enabled = Nz((Me!cboFavoriteColor = "Other"), 0)

End Sub

Private Sub Form_Current()

Me!txtOther.Enabled = Nz((Me!cboFavoriteColor = "Other"), 0)

End Sub

hth

<kheisl...@xxxxxxx> wrote in message

news:1176580869.131894.75400@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



I'm looking for a more efficient way to write some code designed
to
enforce some data integrity rules.

For example, the user selects his favorite color from a combo
box.
The
options are: Red, Green, Blue, and Other. If he selects Other, a
nearby text box is enabled so he can write in his favorite color.
If
he writes in his favorite color (e.g., "Orange") but then changes
his
mind and tries to select one of the other colors from the combo
list,
a message tells him, "Changing your answer from 'Other' will
delete
the information in the related text field. Continue?" If he says
Yes,
the information is deleted and text field is no longer enabled.

The code I use for this scenario works, but it seems lengthy for
such
a simple routine. Is there a more efficient way to code this
routine?
I have lots of other controls that need this same kind of
enforcement.

Thanks.

Code I'm using:

###

Private Sub cboFavoriteColor_AfterUpdate()
If Me.cboFavoriteColor.Value = "Other" Then
Me.txtOther.Enabled = True
Else
If IsNull(Me.txtOther.Value) Then
Me.txtOther.Enabled = False
Else
iresponse = MsgBox("Changing your answer from 'Other'
will
delete the information in the related text field." & _
Chr(13) & Chr(13) & "Continue?", 4 + 48 + 256,
"Delete
confirmation")
If iresponse = 7 Then
Me.cboFavoriteColor.Value = "Other"
Exit Sub
Else
Me. txtOther.Value = Null
Me. txtOther.Enabled = False
End If
End If
End If
End Sub

###- Hide quoted text -

- Show quoted text -












.

Quantcast