Required Property

[madctch]

How would I go about making text boxes required to have a value not equal to null according to other values entered on the form.Ex. If field1 equals 1 then make field2 not not able to receive null.

 

[Remou]

You can use the Before Update event to validate the entry.

 

[TheAceMan1]

How are ya madctch . . .

Put a question mark [purple]?[/purple] in the [blue]Tag property[/blue] of the controls you wish to validate. Then in the forms [blue]BeforeUpdate[/blue] event, copy/paste the following:

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, Ctl As Control
   
   DL = vbNewLine & vbNewLine
   
   For Each Ctl In Me.Controls
      If Ctl.Tag = "?" And IsNull(Ctl) Then
         Msg = "'" & Ctl.Name & "' is Required before Saving!" & DL & _
               "Go back and enter a value" & DL & _
               "   or" & DL & _
               "Hit 'ESC' to Abort Saving . . ."
         Style = vbInformation + vbOKOnly
         Title = "Required Data Error! . . ."
         MsgBox Msg, Style, Title
         Ctl.SetFocus
         Cancel = True
      End If
   Next[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[madctch]

That works wonderfully! I am only having one problem. I am putting the assigning of the question marks into the lost focus event of another text box.

 If IsEmpty(Name1) = False Then
Contract1.Tag = "?"
GCCM1.Tag = "?"
DateCompleted1.Tag = "?" 
End If

Then on the AfterUpdate event of the form I am resetting all the values of the tags back to nothing. The problem occuring is that simply having the cursor in the field changes the tags of the others to "?

 

[TheAceMan1]

madctch . . .

Forget the lost focus & afterupdate thingy.

In [blue]design view[/blue] enter the question marks directly, then replace the code with the following:

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, Ctl As Control
   
   DL = vbNewLine & vbNewLine
   
   [purple][b]If Trim(Me!Name1 & "") = "" Then[/b][/purple]
      For Each Ctl In Me.Controls
         If Ctl.Tag = "?" And IsNull(Ctl) Then
            Msg = "'" & Ctl.Name & "' is Required before Saving!" & DL & _
                  "Go back and enter a value" & DL & _
                  "   or" & DL & _
                  "Hit 'ESC' to Abort Saving . . ."
            Style = vbInformation + vbOKOnly
            Title = "Required Data Error! . . ."
            MsgBox Msg, Style, Title
            Ctl.SetFocus
            Cancel = True
         End If
      Next
   [purple][b]End If[/b][/purple][/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[madctch]

That's it! Thanks for the help.

 

[TheAceMan1]

madctch . . .

Forgive me but there's a line of code missing (in [purple]purple[/purple]):

[blue]   If Trim(Me!Name1 & "") = "" Then
      For Each Ctl In Me.Controls
         If Ctl.Tag = "?" And IsNull(Ctl) Then
            Msg = "'" & Ctl.Name & "' is Required before Saving!" & DL & _
                  "Go back and enter a value" & DL & _
                  "   or" & DL & _
                  "Hit 'ESC' to Abort Saving . . ."
            Style = vbInformation + vbOKOnly
            Title = "Required Data Error! . . ."
            MsgBox Msg, Style, Title
            Ctl.SetFocus
            Cancel = True
            [purple][b]Exit For[/b][/purple]
         End If
      Next
   End If[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[BJNK]

AceMan thats exactly what I have been looking for as well. I was just wondering if there is a way to also have an over ride function with this? My boss wants it so that any new customer added has to have ALL of their info added, unless its a one time customer who just wants to make one order and we dont need all of the info included.

So I was contemplating having say an override code on the form and if the code is entered it will ignore the errors?

Is this possible? Or maybe if the CurrentUser is = to select users it will ignore this?

Any thoughts/ideas are appreciated!

 

[TheAceMan1]

BJNK . . .

How about a MsgBox with the options to:

[blue]Click 'Yes' to Perform Full Validation
Click 'No' to Perform Partial Validation
Click 'Cancel' to Abort Saving[/blue]

Only thing is the MsgBox would occur for each save. However it can be made to default to the first option so that hitting the enter key is all thats required.

Or you could add an unbound checkbox to the form to tag partial validation. This checkbox would be reset after saving.

BTW . . . since your return I've noticed some logic is reversed in the code. Currently validation is only performed if [blue]Name1[/blue] is empty. This is easily fixed but I await your answer to the secnarios above.

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[TheAceMan1]

BJNK . . .

Here's the code with the messagebox . . .

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, Ctl As Control, MsgAns As Integer
   
   DL = vbNewLine & vbNewLine
   
   If Trim(Me!Name1 & "") = "" Then
      Msg = "'Name1' is a required field and can't be empty!"
      Style = vbInformation + vbOKOnly
      Title = "Validation Error! . . ."
      MsgBox Msg, Style, Title
      Me!name1.SetFocus
      Cancel = True
   Else
      Msg = "Click 'Yes' to Perform Full Validation" & DL & _
            "Click 'No' to Perform Partial Validation" & DL & _
            "Click 'Cancel' to Abort Saving"
      Style = vbQuestion + vbDefaultButton1 + vbYesNoCancel
      Title = "User Action Required! . . ."
      MsgAns = MsgBox(Msg, Style, Title)
      
      If MsgAns = vbYes Then
         For Each Ctl In Me.Controls
            If Ctl.Tag = "?" And IsNull(Ctl) Then
               Msg = "'" & Ctl.Name & "' is Required before Saving!" & DL & _
                     "Go back and enter a value" & DL & _
                     "   or" & DL & _
                     "Hit 'ESC' to Abort Saving . . ."
               Style = vbInformation + vbOKOnly
               Title = "Required Data Error! . . ."
               MsgBox Msg, Style, Title
               Ctl.SetFocus
               Cancel = True
               Exit For
            End If
         Next
      ElseIf MsgAns = vbCancel Then
         Cancel = True
      End If
   End If

End Sub[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[BJNK]

That will work. Not exactly what ol boss man was looking for but it gets the job done! Thank you!