How do i run an if command with multiple field values?

[policechiefwiggum]

Hi All,

I need to write an if command to run based on the value of 4 fields.

i have a form that has a lot of fields (25 at the last count!) from that there are 4 fields which are vital, but the data is not always available.

what i've done is made a form which pops up when the "save" button is clicked. this form has the same 4 fields and where the value is present on the previous form populates and disables that field on the new form, if the value is not avaliable the user will check a tick box to confirm the value is not available.

what i need is a way to run an "if" command that checks for at least 1 of the fields as blank and then runs the "open new form" script, and just continues to save if all the fields are populated.

i could do it as a string of "if ... AND ...." but that would require me to write an if command for each possible scenario, i'm hoping theres a better way!!

Thanks

 

[MajP]

Put a ? in the Tag property of the controls you want to check.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Not isValidData Then
   MsgBox "Ensure fields ..... are filled in"
   DoCmd.CancelEvent
   DoCmd.OpenForm "your form"
 End If
End Sub

Public Function isValidData() As Boolean
  Dim ctl As Access.Control
  isValidData = True
  For Each ctl In Me.Controls
    If ctl.Tag = "?" Then
       If Trim(ctl & " ") = "" Then
         isValidData = False
         Exit Function
       End If
    End If
 Next ctl
End Function

 

[policechiefwiggum]

Thanks Majp,

I've tried this above but its not working, i think this is because when i click the save button, it runs an SQL command, before doing

DoCmd.Close acForm, Me.Name

so the "Private Sub Form_BeforeUpdate(Cancel As Integer)" sub isnt running, can i add this code to the button and get it to call the function?

 

[policechiefwiggum]

Forget my previous post, after messing about and putting the code in various places i've answered my own question - yes i can call the function!

And it works perfectly, so thank you very much )