Thanks, Doug I truly appreciate your help. I think I've stumbled onto a real
brain buster here - read on if you feel brave!
I'm guessing that the form controls are named the same as the fields in the
underlying recordset. It usually helps to have them named differently (and
refer to the controls in the VBA code, not the fields). (I always rename all
my text boxes to start with txt, my combo boxes to start with cbo and so on)
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.
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.
This just doesn't make any immediate sense.
Here are the events as slightly edited:
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
Private Sub UOM_AfterUpdate()
If Me!UOM = "fl. oz." Then
If Len(Me!Density & vbNullString) = 0 Then
Beep
MsgBox "Density is required!"
Me!Density.SetFocus
End If
ElseIf Me!UOM = "g" Or Me!UOM = "oz." Or Me!UOM = "lb." Then
If Len(Me!Density & vbNullString) > 0 Then
Beep
If MsgBox("Density must be NULL!" & vbCrLf & _
"Do you want to set it to Null?", vbYesNo + _
vbQuestion) = vbYes Then
Me!Density = Null
DoCmd.GoToControl "PKTareWtg"
End If
End If
End If
End Sub
You don't happen to have a default value assigned to Density, do you? That
would might explain the problems.
Nope. I checked all other fields, too. No defaults.
BTW, it might be simpler to remove the logic from the two controls, and to
put all of the checks in the form's BeforeUpdate event.
I tried that, too and it doesn't resolve the problem.
Did you happen to try a model based off the SQL I posted?
SELECT tblFGsTargetFillWeightsCalculated.txtFacilityID,
tblFGsTargetFillWeightsCalculated.txtProfileID,
tblFGsTargetFillWeightsCalculated.LineID,
tblFGsTargetFillWeightsCalculated.VolWt,
tblFGsTargetFillWeightsCalculated.UOM,
tblFGsTargetFillWeightsCalculated.Density,
tblFGsTargetFillWeightsCalculated.PKTareWtg,
tblFGsTargetFillWeightsCalculated.STDEV,
tblFGsTargetFillWeightsCalculated.Comments, tblFGPhysicalAttributes.UnitCount
FROM tblFGsTargetFillWeightsCalculated INNER JOIN tblFGPhysicalAttributes ON
tblFGsTargetFillWeightsCalculated.txtProfileID =
tblFGPhysicalAttributes.txtProfileID
ORDER BY tblFGsTargetFillWeightsCalculated.txtFacilityID,
tblFGsTargetFillWeightsCalculated.LineID;
KA-BOOOM! That was my head that just exploded!
