Data Forms

Say that you’re doing the same-old, same-old thing of launching a data form as an add/edit view off of a grid on a tab. But in addition to the data-bound fields for the record you’re adding/editing, you also need to populate/update a one-to-many table—for example, adding/updating one record per user on a given team, in that 1:M child table.

You could do that SQL update in the grid’s OnAddedRow or OnEditedRow events, by passing the record’s ID value back down in a global variable; but conceptually that’s not where the SQL belongs. Rather, you’d like to be doing the 1:M update in the data form itself. But the problem is that any code you put into the OK button’s click event will run even when the form fails validation (so the form doesn’t close on its own, but just sits there, mocking you). And the AXFormValidate doesn’t return a value until after it’s finished running; and again, even when it returns False, the OK click event code still runs.

The way around that is to iterate through the controls on the form, looking for ones which have their Required property set, but no value entered by the user. If that validation fails, then exit the click event without running the 1:M SQL:

Function ValidateForm

     Dim i
     Dim blnValidated

     blnValidated = True
     On Error Resume Next
     For i = 0 To ControlCount - 1
          Select Case TypeName(Controls(i))
               Case "PickList", "Memo",  "LinkEdit", "DateTimeEdit", _
                  "Edit", "LookupEdit"
                    If Controls(i).Required And _
                       Controls(i).Text = "" Then
                         blnValidated = False
                         Exit For
                    End If
          End Select
     Next
     On Error Goto 0

     ValidateForm = blnValidated

End Function

Sub cmdOKClick(Sender)

     ...

     If Not ValidateForm Then
          Exit Sub
     End If

     ...

     ' SQL for doing 1:M Insert/Update

     ...

End Sub

It’s kludgy, but because you’re iterating through the controls looking for blank Required ones rather than hard-coding that test, it doesn’t make the form any less maintainable: As long as any new control’s Type is listed in the Select Case statement, you don’t even need to know that that validation function is there.