Message box based on one combo box populated and another combo box blank

[pjs101974]

Hello,

I am hoping that someone out there can steer me in the right
direction.

Here is the scenario:

I have a main form and a subform
within it. The subform contains two combo boxes:

cboUnitDefects (contains
multiple options to describe product failures)
cboFailureLocation (contains


multiple options to define a physical location)

What I am looking to do
is:
If cboUnitDefects is populated AND cboFailureLocation is
not, a message box requiring the population of cboFailureLocation comes up
and tells the operator to select a failure location. So far, the following code
was used with no success within the subform's "Before Update"
property:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If
(Len(Me.cboFailure_Location & vbNullString) = False) And
(Len(Me.cboUnitDefects & vbNullString) = True) Then
MsgBox "Please Enter
a Failure Location", vbOKOnly
Cancel = True
End If
End Sub

Thank
you for your help in advance!
--Phill

[pbaldy]

Does this work?

If Len(Me.cboFailure_Location & vbNullString) = 0 And Len(Me.cboUnitDefects & vbNullString) > 0 Then

[pjs101974]

Hi Paul,

Thank you for your help on this! That did the trick and it works if the user tries to close the form. The only other issue is that if a user tries to add another record, the message will come up, but a "run time error 2105" is displayed. Is there any way around this one?

Thank you once again!!!

[pbaldy]

What is the text of that one? What is the sequence of events?

[pjs101974]

Hi Paul,

The text states: Run Time Error 2105: "you can't go to the specified record"

The sequence of events is:
1: The operator makes a selection in cboUnitDefects.
2: Instead of selecting a failure location using cboFailure_Location, the operator simply clicks a command button which is set up to go to a new record.

Is there a possibility of having the focus go to the cbo rather than displaying the error?

[pbaldy]

I think the before update code is stopping the update, which won't allow the form to go to a different record. You could put the same test there.

[pjs101974]

Hi Paul,

Doing that one still produces the same results. I even tried it in the "Before Update" event.

[pbaldy]

What's the code behind the button now? You want to abort if the combo test isn't met.

[pjs101974]

What's the code behind the button now? You want to abort if the combo test isn't met.

Hi paul,

Here is the code behind the button:

Private Sub cmdAddIssue_Click()
If Len(Me.cboFailure_Location & vbNullString) = 0 And Len(Me.cboUnitDefects & vbNullString) > 0 Then
MsgBox "PLEASE ENTER A FAILURE LOCATION - MTP, ESS, OR ATP", vbOKOnly, ""
Cancel = True
End If
DoCmd.GoToRecord , , acNewRec
End Sub

[pbaldy]

That lets the GoToRecord happen no matter what. You want:

Private Sub cmdAddIssue_Click()
If Len(Me.cboFailure_Location & vbNullString) = 0 And Len(Me.cboUnitDefects & vbNullString) > 0 Then
MsgBox "PLEASE ENTER A FAILURE LOCATION - MTP, ESS, OR ATP", vbOKOnly, ""
Else
DoCmd.GoToRecord , , acNewRec
End If
End Sub

[pjs101974]

That lets the GoToRecord happen no matter what. You want:

Private Sub cmdAddIssue_Click()
If Len(Me.cboFailure_Location & vbNullString) = 0 And Len(Me.cboUnitDefects & vbNullString) > 0 Then
MsgBox "PLEASE ENTER A FAILURE LOCATION - MTP, ESS, OR ATP", vbOKOnly, ""
Else
DoCmd.GoToRecord , , acNewRec
End If
End Sub

Paul, you nailed it! Thank you!!!!!!!

[pbaldy]

Happy to help!