If Yes, second field is required

[hardhitter06]

Access 2003.

Can someone quickly show me what I need to change in my code so that if my combo box is selected "Yes", this second field is required.

Yes/No Field: ApproverYesNo
2nd Field:Approver


Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.ApproverYesNo = "Yes" Then
Me.frmInputState.Form.Approver IsNull
MsgBox ctrl.Approver & " Cannot Be Left Empty!"
Cancel = True
Exit Sub
End If
End Sub


Thank you.

 

[pbaldy]

If it's a Yes/No field:

If Me.ApproverYesNo = True Then

and your test of Approver is off. I'd do this:

If Me.ApproverYesNo = True AND IsNull(Me.frmInputState.Form.Approver) Then

 

[hardhitter06]

Paul,

Is this what you meant?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.ApproverYesNo = "Yes" And IsNull(Me.frmInputState.Form.Approver) Then
MsgBox ctrl.Name & " Cannot Be Left Empty!"
Cancel = True
End If

End Sub

 

[pbaldy]

My trite answer is "if it works, yes, if it doesn't, no". I don't see that working though. Like I said, if the data type of the field is Yes/No, you want True rather than "Yes". You might also be able to use Yes without the quotes, or -1.

I didn't notice in your original post that ctrl isn't created or set there, so unless it's set somewhere else, that won't work. You'd have to specify the name. If you have a loop of the controls where that variable is valid, then it should work.

 

[hardhitter06]

Ok,

What I did was tried to use some code from something else that basically checked to see if a field was null and I mashed it together with what I'm looking for.

Um, the field was a Yes/No, but then I changed it to Lookup Value and typed in Yes and No for the first two cells.

Does that change your recommendations on what to use now?

"Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.ApproverYesNo = True And IsNull(Me.frmInputState.Form.Approver) Then
MsgBox Me.Approver & " Cannot Be Left Empty!"
Cancel = True
End If

End Sub"

This piece of code is not working, it keeps giving me an error pointing Me.ApproverYesNo, after the first IF

 

[pbaldy]

Is that the name of the control containing the value?

 

[hardhitter06]

That is the drop down field (ApproverYesNo). If Yes, the next field is
Approver that I want the user to have to fill out if they selected Yes to begin with.

 

[Scooterbug]

When using a combo box, you have to reference the column number that the data is in. Since a combo box can be driven by a row source, it can actually contain several different data.

To reference, the sytax is like this:

me.controlname.column(0)

That would reference the first column of the combo box.

 

[hardhitter06]

Now the part of the code frmInputState is being highlighted with an error.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.ApproverYesNo.Column(0)) = True And IsNull(Me.frmInputState.Form.Approver) Then
MsgBox Me.Approver & " Cannot Be Left Empty!"
Cancel = True
End If

End Sub

I have tried this line:

If (Me.ApproverYesNo.Column(0)) = True And IsNull(Me.Approver) Then (NO ERROR BUT DIDNT WORK)

I also tried this:

If (Me.ApproverYesNo.Column(0)) = True And IsNull(Me.frmInputState.Form.Approver) Then
MsgBox (Me.frmInputState.Form.Approver) & " Cannot Be Left Empty!" (ALSO DIDN't WORK)

Not sure what I'm missing here, anyone?

 

[Scooterbug]

Can you post a copy of your database? I think it would go a long way to be able to actually see it...I know I do better with something to actually work with than with just words

 

[hardhitter06]

Thanks! Its the State Input Form

 

[hardhitter06]

Let me clarify to...if the user selects YES to approver, THEY MUST ENTER into the approver field

 

[Scooterbug]

Figured out what the problem is...first, in the tblUsers, get rid of of the lookup on the ApproverYesNo field. Change it to a check box. Using Lookups at the table level is bad and causes headaches

Second, here is the correct code:

If Me.ApproverYesNo = -1 And IsNull(Me.Approver) Then
  MsgBox "Approver field Cannot Be Left Empty!"
  DoCmd.GoToControl "Approver" 
  Cancel = True
  End If

I put a line in there to send the cursor to the Approver field so that the user can enter it without having to do any extra mouse clicking. Feel free to get rid of the gotocontrol line if it's not your cup of tea.

 

[hardhitter06]

Perfect and yes I like that it directs the cursor to that field...thanks so much!