Required Field on Form

[Sed]

Is there an Access Doctor in the house? I've been working on this all of yesturday and of no success. I need you guys expertise. I thought that this exercise is simple, but apparently it's not.

OK, here it is... I have 6 fields on my form that I want to make required field. i.e. Lead_Time, Order_date, due_date, MPA Name, Responsible Supplier and Supplier approval name. But here's a catch, If the supplier agree to pay, they would mark a "yes" and if "yes" than the field would be required.

I've started on the Lead_Time field and that didn't work. Please help.
Much appreciated.

Private Sub txtLead_Time_BeforeUpdate(Cancel As Integer)
If Me.txtLead_Time = "" Or IsNull(Me.txtLead_Time) Then
MsgBox "Lead_Time is a required Field", vbCritical, vbOKOnly + vbDefaultButton2, "Missing Data"
Me.txtLead_Time.SetFocus
DoCmd.CancelEvent
End If
End Sub

 

[CEH]

Looks like another If...then.. statement would do it...
Private Sub txtLead_Time_BeforeUpdate(Cancel As Integer)
If checkbox = true then
If Me.txtLead_Time = "" Or IsNull(Me.txtLead_Time) Then
MsgBox "Lead_Time is a required Field", vbCritical, vbOKOnly + vbDefaultButton2, "Missing Data"
Me.txtLead_Time.SetFocus
DoCmd.CancelEvent
else if checkbox = false
end if
End If
End Sub

You might also look into using the "Tag" method.... Use your "If" statement to set the value on the tag property... then run the code for all controls requiring tagged controls.

For example.....
If checkbox = True then
me.txtLead_Time.tag = "*"
Else me.txtLead_Time.tag = ""
end if
Then use a validation code.....

 

[Sed]

thanks Curtis, I will test this out, but by looking at your code, I notice that there's 2 end if back to back. should I remove one of it? and Also, I'm getting a compile error message for "Else If" ...


Also, I don't understand the Tag Method, you mind teaching. sounds like a good idea.

 

[CEH]

Actually just drop the line..... else if checkbox = false

 

[Sed]

OK, I just did and it's not working. hmmmm
this is what I have.
Private Sub txtLead_Time_BeforeUpdate(Cancel As Integer)
If CheckBox = True Then
If Me.txtLead_Time = "" Or "o" Or IsNull(Me.txtLead_Time) Then
MsgBox "Lead_Time is a required Field", vbCritical, vbOKOnly + vbDefaultButton2, "Missing Data"
Me.txtLead_Time.SetFocus
DoCmd.CancelEvent
End If
End If
End Sub

 

[Sed]

the "o" is "0", I forgot to change it before I paste.

 

[pbaldy]

I'm sure Curtis can sort out the syntax problem so I won't interrupt on that. I'll point out a conceptual problem though. I wouldn't use the before update event of the control, I'd use the before update event of the form to test all the values. If the user simply tabs through an individual control, the before update event won't fire, because no change occurred to the contents. That leaves you vulnerable to them having left it empty but your code not catching it.

 

[Sed]

Pbaldy, please explain. I'm not sure if I understand

 

[CEH]

As Paul said....better on the form. I like this function

Public Function FieldValidate()
'Place an asterisk (*) in the Tag Property of the text boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:
'modified to add Label text to message box instead of field name
'ctl.Controls(0).Caption instead of ctl.Name
'This will make much more sense to a user

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control, Source As String, Cancel As Integer, Answer As Variant

nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls

If ctl.ControlType = acTextBox Or ctl.ControlType = acOptionGroup Or ctl.ControlType = acComboBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Controls(0).Caption & "' field" & nl & _
"You can't save this record until this data is provided" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
Answer = "False"
Me.txtValid = Answer
Cancel = True
  Exit For
    Else: Answer = "True"
    Me.txtValid = Answer
  
        End If
            End If
                Next
                Set ctl = Nothing
End Function

With this you could then put, on your before update of the form...

If checkbox = true then
FieldValidate
else
end if

And with the code above you put a hiden textbox called "txtValid" on the form. If the checkbox is true it will run the Function FieldValidate() If the checkbox is false it won't.
To look further there are MANY ways to validate fields on the forum... a search on "Validate" will give you many results.

 

[Sed]

Forgive me, this is the first time attempting to do required field. I'm pretty rusty.
So, I assume you want me to copy the code over (of which I did) now, how and where should I replace the "txtLead_Time".

 

[pbaldy]

My point is that if you try to do your validating in the textbox events, you run the risk of the code not running if the user skips that textbox. Try your code and simply enter through the textbox without entering anything. Your code doesn't fire, but the textbox is still empty. The best place to validate data is the before update event of the form.

 

[Sed]

Paul,

I'm lost...I tried every which way and still doesn't work. am I missing something?

 

[Sed]

I think I got it. This works too.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(txtLead_Time) Or txtLead_Time = "" Then
MsgBox "Lead_Time is a Required Field, Please enter Lead_Time", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
txtLead_Time.SetFocus
Cancel = True
Exit Sub

ElseIf IsNull(cboOrder_Date) Or cboOrder_Date = "" Then
MsgBox "Order_Date is a Required Field, Please enter Order_Date", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
cboOrder_Date.SetFocus
Cancel = True
Exit Sub

ElseIf IsNull(cboDue_Date) Or cboDue_Date = "" Then
MsgBox "Due_Date is a Required Field, Please enter Due_Date", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
cboDue_Date.SetFocus
Cancel = True
Exit Sub

ElseIf IsNull([Planner_ID]) Or [Planner_ID] = "" Then
MsgBox "MPA Name is a Required Field, Please enter MPA Name", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
[Planner_ID].SetFocus
Cancel = True
Exit Sub

Else
Cancel = False
End If

 

[Sed]

One more favor... this is hard. I have a field that ask "supplier agree to pay?" with a "yes" or "no" choice and another field "supplier approval name". If they check "yes" the I need that field to be a required field else they can leave it blank.

Is this possible?

 

[pbaldy]

If Me.ControlName = "Yes" Then
  'your standard test code for the other control here
End If

If the control is a checkbox, the test would be

If Me.ControlName = True Then

 

[Sed]

I'm I missing something? This is not work for me. see below

If Me.sup_agree_yes = "Yes" Then
MsgBox "Supplier_Approval_Name is a Required Field, Please enter Supplier_Approval_Name", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
End If
Cancel = True
Exit Sub

 

[pbaldy]

Define "is not work". For starters, you didn't test the other control (the one that would be required if this one is "Yes").

 

[Sed]

Sorry, I mean that it didn't do what I wanted. Does this look right to you?
By the way how was Vegas?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.sup_agree_yes = True Then
If IsNull([Supplier_Approval_Name]) Or [Supplier_Approval_Name] = "" Then
MsgBox "Supplier_Approval_Name is a Required Field, Please enter Supplier_Approval_Name", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
End If
Cancel = True
Exit Sub
Cancel = False
End If

End Sub

 

[pbaldy]

I would expect these lines to be inside the inner If/Then so they're only executed if the control is empty:

Cancel = True
Exit Sub

 

[Sed]

this works like a charm...thanks

ElseIf Me.sup_agree_yes = False Then
ElseIf IsNull(Supplier_Approval_Name) Or [Supplier_Approval_Name] = "" Then
MsgBox "Supplier_Approval_Name is a Required Field, Please enter Supplier_Approval_Name", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
[Supplier_Approval_Name].SetFocus
Cancel = True
Exit Sub