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.