Access form validation

[MrChameleon]

Hi,

I have an Access 2010 DB with a specific form on which there are 9 combos. I have a button I want greyed out until all of the combos have a valid selection made. I've played around with a couple of ways of achieving this, such as a crazy IF not (isnull(cmb1)) and (isnull(cmb2))...etc THEN cmd1.enabled=true but this is really unwieldy and finding exactly the right event to place the code in is problematic (unless put in every combos AfterUpdate event which just makes the form's code module horrendous).

I can't seem to find a simple method of achieving this. I did write a loop with a flag variable which cycled through all the form's controls and if they were combo type set the Boolean flag to false if it found one that had no value) and only set the enabled property of the button to true if the flag was true at the end of the loop. That seems an entirely quicker and more sensible way of doing it but I still can't figure where's the best event to put this code in - the user could update the combos in any order and I don't want to force it.

Any ideas gratefully received.

 

[pbaldy]

I would create a public function on the form that tested them all and set the enabled appropriately. Call that from the after update event of all the combos, and perhaps the current event to catch the changing of records.

 

[MarkK]

What you need is a single subroutine that does that logic, and enables or disables the UI features of the form, and then you need to call if from any event that may impact the outcome. Imagine this pattern....

Private Sub ResetForm()
[COLOR="Green"]   'this routine customizes the UI for the current state of the form[/COLOR]
   Me.cmdOK.Enabled = Not IsNull(Me.SomeCombo) And Not IsNull(Me.SomeOtherCombo)
   If Me.cmdOK.Enabled Then 
      Me.tbMessage = "Data looks complete."
   Else
      Me.tbMessage = "Looks like some data is still missing."
   End If
End Sub

Private Sub SomeCombo_AfterUpdate()
   ResetForm
End Sub

Private Sub Form_Current()
   ResetForm
End Sub

Private Sub SomeOtherCombo_Click()
   ResetForm
End Sub

That way multiple events on the form can affect the overall UI presentation, but, you only have to write the code that modifies that presentation once.
hth
Mark

 

[MarkK]

Lol, like what Paul said.

 

[The_Doc_Man]

In the form's declaration area, put nine flags.

Dim CBO1 As Boolean, CBO2 As Boolean, CBO3 As Boolean, ..., CBO9 As Boolean

In the form's OnCurrent routine, set each flag to False. After doing so, execute

TheButton.Enabled = CBO1 AND CBO2 AND CBO3 AND ... AND CBO9

Associate ONE of the CBOn flags with each of the nine combo boxes. In the LostFocus routine for each combo, test whether the content of that one combo box is valid. If so, set one of the CBOn flags to True and then do a Copy/Paste of that ugly 9-way AND statement. Don't loop. You'll spend forever trying to do something that is too simple for an excessively elegant solution.

Once ALL of the combo box LostFocus routines have passed favorable judgment on the individual controls, that long, ugly - but easily copied & pasted - AND will enable the button no matter WHICH order the combos were updated. The command button should look very different when enabled than when disabled, so it should be easy to tell when it has happened.

 

[arnelgp]

Convert the code you have into Public Function.
On the forms load event:

=yourFuncname()

And on each combos afterupdate event:

=yourFuncname()

On form's current event

=yourFuncname()