Validation For Form (1 Viewer)

IgnoranceIsBliss

Registered User.
Local time
Today, 07:58
Joined
Jun 13, 2019
Messages
35
Hi - I have a form that has the below controls
2 combo boxes
1 listbox
2 text boxes
1 button

What is the best way to ensure that when the button is pressed all controls have input/selection made?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:58
Joined
Oct 29, 2018
Messages
21,358
Hi. The recommended approach is to use the form’s BeforeUpdate event for validating records.
 

IgnoranceIsBliss

Registered User.
Local time
Today, 07:58
Joined
Jun 13, 2019
Messages
35
Hi. The recommended approach is to use the form’s BeforeUpdate event for validating records.

When does the before update event fire? Would that go for each control or for the button?
 

Micron

AWF VIP
Local time
Today, 10:58
Joined
Oct 20, 2018
Messages
3,476
Depends. Bascialy you can write a bunch of If statements or a loop through those controls. If there are or will be more of them in the future, they could be included in the loop but that could be either good or bad. If they are bound, then you have to worry about when the check is made and what has to happen on pass or fail. Then does that happen every time it finds a problem (so that could be 5 times for you) or just once?

I see that responses appear in advance view - I never saw that before.
Oh well, as I was composing an answer for how, not when, I'll leave it at this.
 

IgnoranceIsBliss

Registered User.
Local time
Today, 07:58
Joined
Jun 13, 2019
Messages
35
Depends. Bascialy you can write a bunch of If statements or a loop through those controls. If there are or will be more of them in the future, they could be included in the loop but that could be either good or bad. If they are bound, then you have to worry about when the check is made and what has to happen on pass or fail. Then does that happen every time it finds a problem (so that could be 5 times for you) or just once?

I see that responses appear in advance view - I never saw that before.
Oh well, as I was composing an answer for how, not when, I'll leave it at this.

No controls will be added. 3 of the controls are bound

I was originally thinking of writing IF statements in the button press event and basically if validation fails show a message to the user as why it failed and stop code execution for the button press.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:58
Joined
Oct 29, 2018
Messages
21,358
When does the before update event fire? Would that go for each control or for the button?
No, none of them. This event fires whenever a record on a form needs to be saved to the bound table. So, it will/should catch all the controls that were changed.
 

Micron

AWF VIP
Local time
Today, 10:58
Joined
Oct 20, 2018
Messages
3,476
Then on form update event something like
Code:
Dim ctl As Control

For Each ctl in me.Controls
  If ctl.ControlType = acTextbox or ctl.ControlType = acListbox Or ctl.ControlType = acCombobox Then
    If Nz(ctl,0) = 0 Then
      msgbox "You forgot one"
      Cancel = True
      Exit Sub
    End If
  End if
Next
This can get much more elaborate, such as
- reporting all at one time in a list
- using attached label captions rather than sometimes cryptic control names (the above makes no mention of which are missing)
- skipping some controls and not others
 

IgnoranceIsBliss

Registered User.
Local time
Today, 07:58
Joined
Jun 13, 2019
Messages
35
No, none of them. This event fires whenever a record on a form needs to be saved to the bound table. So, it will/should catch all the controls that were changed.

Okay, I misunderstood your question. None of the information is being saved to a table. However a few controls pull information from a table.

On the button press a report is created that uses data from the form, which is why I want to ensure nothing is null/empty. So the report has all data that it should.
 

IgnoranceIsBliss

Registered User.
Local time
Today, 07:58
Joined
Jun 13, 2019
Messages
35
Then on form update event something like
Code:
Dim ctl As Control

For Each ctl in me.Controls
  If ctl.ControlType = acTextbox or ctl.ControlType = acListbox Or ctl.ControlType = acCombobox Then
    If Nz(ctl,0) = 0 Then
      msgbox "You forgot one"
      Cancel = True
      Exit Sub
    End If
  End if
Next
This can get much more elaborate, such as
- reporting all at one time in a list
- using attached label captions rather than sometimes cryptic control names (the above makes no mention of which are missing)
- skipping some controls and not others

That is short and sweet. I would like to notify the user of the specific control tho. Is there a way to provide that? And if multiple are blank just providing the first null/empty control is fine.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:58
Joined
Oct 29, 2018
Messages
21,358
Okay, I misunderstood your question. None of the information is being saved to a table. However a few controls pull information from a table.

On the button press a report is created that uses data from the form, which is why I want to ensure nothing is null/empty. So the report has all data that it should.
Well, if the data is not being saved to a table, then the BeforeUpdate event won't help you. And since you simply don't want to open/print the report if there's incomplete data, then the "best" place, I think, to put your validation is in your code where you initiate opening/printing the report. For example, if you have a button to print/view the report, then you can put the validation routine there. Cheers!
 

IgnoranceIsBliss

Registered User.
Local time
Today, 07:58
Joined
Jun 13, 2019
Messages
35
Well, if the data is not being saved to a table, then the BeforeUpdate event won't help you. And since you simply don't want to open/print the report if there's incomplete data, then the "best" place, I think, to put your validation is in your code where you initiate opening/printing the report. For example, if you have a button to print/view the report, then you can put the validation routine there. Cheers!

Would this validation for each control be advisable?

Code:
If Me.combo0.ListIndex = “-1” Then
  MsgBox “Please select from combo box”
  Me.combo0.SetFocus
  Exit Sub
End If
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:58
Joined
Oct 29, 2018
Messages
21,358
Would this validation for each control be advisable?

Code:
If Me.combo0.ListIndex = “-1” Then
  MsgBox “Please select from combo box”
  Me.combo0.SetFocus
  Exit Sub
End If
It all depends on your goal. The above example should work except you could just simply check if a Combobox is Null. For example:
Code:
If IsNull(Me.ComboName) Then
 

Micron

AWF VIP
Local time
Today, 10:58
Joined
Oct 20, 2018
Messages
3,476
How did we go from "3 controls are bound" to none are bound?

Unbound controls still have a Before Update event, yes? So with some bound and some not, either event could be used, I think.

This is getting confusing. Maybe a db copy would keep this from getting too much longer.
 

Users who are viewing this thread

Top Bottom