If this then that

D

Douglas J. Steele

You're going to kick yourself (I know I am!)

It's order of operation precedence. Try putting parentheses around the Or
clauses:

Private Sub Density_Exit(Cancel As Integer)
If IsNull(Me!UOM) = False Then
If (Me!UOM = "g" Or Me!UOM = "oz." Or Me!UOM = "lb.") _
And Len(Trim(Me!Density & vbNullString)) > 0 Then
Beep
MsgBox "Density must be NULL!"
Cancel = True
ElseIf Me!UOM = "fl. oz." And _
Len(Trim(Me!Density & vbNullString)) = 0 Then
Beep
MsgBox "Density is required!"
Cancel = True
End If
End If

End Sub

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JohnLute said:
Correction!!!
I changed all of the names accordingly and still had the Exit [Density]
problem. I changed the names back and tried again. This time I noticed
that
if I selected "lb." for [Density] that everything worked fine however if
I
selected either "g" or "oz." then the Exit [Density] problem occured.
Click to expand...

Should be:
I changed all of the names accordingly and still had the Exit [Density]
problem. I changed the names back and tried again. This time I noticed
that
if I selected "lb." for [UOM] that everything worked fine however if I
selected either "g" or "oz." then the Exit [Density] problem occured.
and
That is truly puzzling. I didn't notice it before. So things have
narrowed
down! I can't Tab or click OUT of [Density] after creating a new or
editing
an existing record with "g" or "oz." selected for [Density]. Moreover
there
are no problems when "fl. oz." is selected for [Density] regardless if
I'm
creating a new or editing an existing record.
Click to expand...

Should be:
That is truly puzzling. I didn't notice it before. So things have
narrowed
down! I can't Tab or click OUT of [Density] after creating a new or
editing
an existing record with "g" or "oz." selected for [UOM]. Moreover there
are no problems when "fl. oz." is selected for [UOM] regardless if I'm
creating a new or editing an existing record.
Click to expand...

So sorry for the confusion!
Click to expand...
 
J

JohnLute

DOH!!! That never occurred to me but now I know to be more aware of it in the
future!

I'm so glad this is now resolved! It works like a charm now. Upon further
consideration I changed the AfterUpdate MsgBox to this:

If MsgBox("Density must be NULL!" & vbCrLf & _
"Click OK to set to Null.", vbOK + _
vbQuestion) = vbOK Then
Me!Density = Null

This assures that [Density] is set to Null before proceeding although I
wonder if there's a way to remove "Cancel" from the MsgBox...?

Previously the question was:

If MsgBox("Density must be NULL!" & vbCrLf & _
"Do you want to set it to Null?", vbYesNo + _
vbQuestion) = vbYes Then
Me!Density = Null

Which doesn't force the issue as desired.

THANKS, THANKS, THANKS! This was a real adventure! Thanks for the guidance!
 
D

Douglas J. Steele

Replace vbOK with vbOKOnly

To be honest, though, why are you popping up a message box if they can't do
anything about it? In other words, they have no choice but to have the field
set to Null. What happens if they accidentally picked oz. when they meant to
pick fl. oz.? That means that whatever they (correctly) typed into the
Density field will be erased, and they'll have to retype it once they select
the correct UOM.
 
Top