More efficient code for If Else data validation rule?

  • Thread starter Thread starter kheisler6
  • Start date Start date
K

kheisler6

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

###
 
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
 
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
 
if the user says No to "Dump the Other
color?", txtOther gets disabled (grayed out) - it should stay enabled.
Click to expand...

hmm, suggest you step through the code execution to see what's happening -
you shouldn't get that result, and i didn't in my test.

hth
 
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.
 
It seems to me that these two lines may need to be reversed:
Cancel = True
Me!cboFavoriteColor.Undo
Click to expand...

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.
Try -1 instead of 0 in the Enabled line of code.
Click to expand...

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.
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.
Click to expand...

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

hth
 
tina said:
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.
Click to expand...

Maybe I misunderstand how Cancel works, but I had thought that the code
stops running once Cancel occurs.
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.
Click to expand...

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)
running the above "toggle" code in the form's Current event, as i posted,
appropriately toggles the txtOther.Enabled property for each record.
Click to expand...

Oops, sorry, I missed that.
 
It seems to me that these two lines may need to be reversed:
Cancel = True
Me!cboFavoriteColor.Undo
Click to expand...

Reversing these two line made no difference.
Try -1 instead of 0 in the Enabled line of code.
Click to expand...

Doing this made no difference. In fact, I replaced the 0 with a set of
random numbers 897875746 and that didn't make any difference, either.
The code still runs the same. (Replacing the 0 with the word, "Ralph,"
however, causes a 'Variable not defined' error. :) )

I also added this line ...

Me!Adult_Race_Other.Enabled = True

.... at the end of everything, after the line ...

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

.... just to make sure the last part of the code was getting evaluated,
and it was: adding this test line made txtOther Enabled.

So the problem is with how the code reads the value of
cboFavoriteColor when the use says "No." For some reason, it doesn't
see "Other" as the final value, so it evaluates the Nz statement to
False and disables txtOther. I suspect the Cancel or Undo statements
confuse it, but they're also essential. Yet this code is working for
Tina. Hmm ...

If you have additional time, I made a very simple .mdb mock-up which
replicates the problem. You can download it here (for 7 days):

http://download.yousendit.com/5F3BF59946ED4DE6

Thanks again for your help.

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

Cancel = True

will cancel the control (or form) update; but it does not stop execution of
the code in the procedure.

hth
 
well, so much for my test-before-post routine, and heaven knows what i was
doing last night - but when i tested my code again this PM, i got the same
error you did. the following change worked for me (really!), will you give
it a try?

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
Exit Sub
End If
End If
End If

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

End Sub

hth
 
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.
Click to expand...

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?
Click to expand...

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)
Click to expand...

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
 
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.
 
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.
Click to expand...

well, it depends on how they're using the data in that field. i usually use
a combo box control when i want to completely control what is stored in a
field, and often i want that control because i'm going to use the data in
statistical analysis. if i cared only about statistical results on 4
specific colors, then it would make sense to clump all other responses as
"Other"; that would be easier to work with than going thru contortions to
clump a bunch of other color values together, when doing the math. but it
might also be important to capture the actual color in each record, for use
in other ways, so the extra field to define "Other" becomes necessary.

hth
 
Tina - The Exit Sub line did the trick! (Now why didn't that occur to
me?)

And yes - your explanation for handling "Other" items is right on -
analyzing the data statistically is our plan, so automatically coding
infrequently used values as "Other" (in cboFavoriteColor), with the
option for analyzing them in detail later on (txtOther), is the most
convenient approach to data management.

Thanks for all your help.

Kurt
 
you're very welcome, glad we got it working. :)
Tina - The Exit Sub line did the trick! (Now why didn't that occur to
me?)
Click to expand...

well, i could ask the same question of myself. <g> great minds get these
things right the first time - as for the rest of us, we can usually figure
it out eventually! ;)



Tina - The Exit Sub line did the trick! (Now why didn't that occur to
me?)

And yes - your explanation for handling "Other" items is right on -
analyzing the data statistically is our plan, so automatically coding
infrequently used values as "Other" (in cboFavoriteColor), with the
option for analyzing them in detail later on (txtOther), is the most
convenient approach to data management.

Thanks for all your help.

Kurt
 
Back
Top