If statement to make fields required, with a twist.

[aarondewberry]

All

I have 4 fields in my form. Before adding a new record, I need two of the fields, plus any one of the other two fields, to be completed.

The required fields are:-
field1
field2
field3
field4

To enable the agent to move records, I need Fields 1 & 2 to always have data entered, in addition to either field 3 or 4.
So either a mixture of fields 1,2,3 or 1,2,4 will enable the moving of records, adding of a new record etc..
Else a message box will appear stating they cannot move records and to fill in the required fields.

Hope i'm being clear.

Thanks

 

[lameid]

Fields 1 and 2 are easy. Make them required in the table.

On the form's beforeupdate property you can test to see if fields 3 and 4 are there and if not, cancel the update. If you are familiar with VBA reading the beforeupdate help topic should give you enough. Otherwise post back.

 

[postermmxvicom]

I believe you can achieve all of this at the table level. I had a similar problem and PHV gave me a way to do it in thread702-1385313

I needed exactly one of two field to be populated. No more, no less. Much like your fields 3 and 4. I am sure that could be modified to meet your needs.

Also, I added a custom message for when the user failed to meet the requirements in the table, so that they would know what needed to be entered.

Aaron

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[lameid]

Brilliant... to remove the mutually exclusive part, you don't need the first half. Adapting for the OP...

Not IsNull(field3 & field4)

 

[TheAceMan1]

How are ya aarondewberry . . .

For this test you'll have to remove [blue]Required[/blue] if set, for all the four fields in the table. Enough of us perform our own validation in the forms [blue]Before Update[/blue] event. This is what I present here.

Note: using required in the table does work, however it pops-up a system message that more often is not friendly enough (to me its even ugly). Custom validation allows you to pop just the messages you want, which fit your schema!

To get on with this, perform the following:
[ol][li]Disable or remove any code you have to deal with validation of the four fields (we can't afford any interaction here!).[/li]
[li]Copy/paste the following routine to the [blue]code module[/blue] of the form ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Private Sub ValRequired()
   Dim Msg1 As String, Msg2 As String, Style As Integer
   Dim fldName1 As String, fldName2 As String
   Dim fldName3 As String, fldName4 As String
   Dim Title As String, DL As String, fldName As String, flg As Boolean
   
   fldName1 = "[purple][b]FieldName1[/b][/purple]"
   fldName2 = "[purple][b]FieldName2[/b][/purple]"
   fldName3 = "[purple][b]FieldName3[/b][/purple]"
   fldName4 = "[purple][b]FieldName4[/b][/purple]"
   
   Style = vbCritical + vbOKOnly
   Title = "Field With No Data Required!"
   DL = vbNewLine & vbNewLine
   
   Msg1 = "'" & fldName & "' is Required in order to save!" & DL & _
          "You'll have go back and fix this! . . ."
   Msg2 = "'" & fldName3 & "' or '" & fldName4 & "' " & _
          "is Required in order to save!" & DL & _
          "You'll have go back and fix this! . . ."
   
   If Trim(Me(fldName1) & "") = "" Then
      fldName = fldName1
      MsgBox Msg1, Style, Title
      flg = True
   ElseIf Trim(Me(fldName2) & "") = "" Then
      fldName = fldName2
      MsgBox Msg1, Style, Title
      flg = True
   ElseIf Trim(Me(fldName3) & "") = "" Then
      fldName = fldName3
      MsgBox Msg2, Style, Title
      flg = True
   ElseIf Trim(Me(fldName4) & "") = "" Then
      fldName = fldName4
      MsgBox Msg2, Style, Title
      flg = True
   End If
   
   If flg Then Cancel = True
   
End Sub[/blue]

[/li]
[li][blue]Thats it![/blue][/li][/ol]
Give it a whirl and let me know . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886