Require a Combo box to be selected if another field Equals 0

[JPed]

Hi there.

I have a form (PostProductionForm) which is used to input data about finished goods which come off a production line. There are three possible outcomes which can occur after the production: Passed, Reprocessed, Failed and the numbers for these are entered in 3 text boxes on the form (QtyReprocessedTxt, QtyFailedTxt, QtyPassedTxt).

These text boxes are auto populated with a 0 from the table (PostProductionTbl) so there aren't blanks.

What I am trying to do is use an IF function (I think) which says that if Quantity Reprocessed is equal to zero when the submit button is pressed, the "Reason for reprocessing" combo (RPReasonCombo) must have something selected (i.e. can't be blank) or it won't go to a new record.

Any ideas?

Cheers

 

[pbaldy]

I'd use the before update event of the form:

http://www.baldyweb.com/BeforeUpdate.htm

using your test of course.

 

[JPed]

I'd use the before update event of the form:

http://www.baldyweb.com/BeforeUpdate.htm

using your test of course.

I tried putting this in:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.QtyReprocessedTxt & vbNullString) = 0 Then
  MsgBox "You need to fill out Reason for R/P"
  Cancel = True
  Me.QtyReprocessedTxt.SetFocus
End If
End Sub

But I'm assuming there should be something in there which checks to see if ReasonForRPCombo is blank or not. I've tried putting things like "ReasonForRPCombo Is Not Null" but it's just throwing error messages back at me. Where am I missing it :x?

Tried variations on this but it has no effect either

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.QtyReprocessedTxt = 0 And Me.ReasonForRPCombo Is Null Then
  MsgBox "You need to fill out Reason for R/P"
  Cancel = True
  Me.QtyReprocessedTxt.SetFocus
End If
End Sub

 

[pbaldy]

More like

If Len(Me.SomeControl & vbNullString) = 0 And Len(Me.OtherControl & vbNullString) = 0 Then

 

[JPed]

More like

If Len(Me.SomeControl & vbNullString) = 0 And Len(Me.OtherControl & vbNullString) = 0 Then

So this sort of works, when I don't touch the QtyReprocessedTxt box, (which contains a 0 as default value from the table it is linked to). However if I type in a 3, and then change it back to 0 it will get stuck giving me the message box unless I choose somethng in the Reason for RP Combo box?

Is there a way around this or should I just leave a blank option in the combo box which can be chosen as a quick fix.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.QtyReprocessedTxt & vbNullString) <> 0 And Len(Me.RPReasonCombo & vbNullString) = 0 Then
  MsgBox "You need to fill out Reason for R/P"
  Cancel = True
  Me.RPReasonCombo.SetFocus
End If
End Sub

 

[JPed]

I have tried putting this in the next record command on my form and this works better when there is just the Reprocess Reason - but I also would like to do exactly the same for a Failure reason but am not sure how to link the two in a way that compares them seperately. For just RP I currently have the following and it works:

Private Sub NextRecordCmd_Click()
If (QtyReprocessedTxt <> 0) And Len(Me.RPReasonCombo & vbStringNull) = 0 Then
  MsgBox "You need to fill out Reason for R/P"
  Cancel = True
  Me.RPReasonCombo.SetFocus
Else
    On Error Resume Next
    DoCmd.GoToRecord , "", acNext
    QtyReprocessedTxt.SetFocus
    SampleWeightOutTxt.SetFocus
End If
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
End Sub

Basically is it possible/how would I be able to combine two If functions on the same command button? I've tried duplicating certain parts of the code with the names for Failure Reasons in, however this ends up stopping it even doing the Reprocess check correctly. I'm hoping to have it so that if you have 3 RP items and a reason, and 1 Failed item and no reason it will give a notification saying you need to fill in Failed Reason etc

Fixed I think!:

'------------------------------------------------------------
' NextRecordCmd_Click
'
'------------------------------------------------------------
Private Sub NextRecordCmd_Click()
 
If (QtyReprocessedTxt <> 0) And Len(Me.RPReasonCombo & vbStringNull) = 0 Then
  MsgBox "You need to fill out Reason for R/P"
  Me.RPReasonCombo.SetFocus
 
ElseIf (QtyFailedTxt <> 0) And Len(Me.FailureReasonCombo & vbStringNull) = 0 Then
  MsgBox "You need to fill out Reason for Failure"
  Cancel = True
  Me.FailureReasonCombo.SetFocus
 
Else
    On Error Resume Next
     DoCmd.GoToRecord , "", acNext
    QtyReprocessedTxt.SetFocus
    SampleWeightOutTxt.SetFocus
End If
 
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
 
End Sub