Data Validation for Enabled Fields Only (1 Viewer)

[veraloopy]

I have a form for users to enter criteria for queries & reports that has 12 text boxes that are either enabled or disabled depending on a dlookup from my Reports table based on the report name.

I then have various command buttons so each report can either be printed, previewed, exported to Excel, exported to PDF, etc, etc.

I need some code so that when I click to run the report, it checks all 12 fields to see which ones are enabled and if so, check on those enabled fields to make sure that the user has entered some information in.

I have come up with this so far but getting error 438 Object doesn't support this property or method (If ctl.Enabled = True Then)

[FONT=Arial]Dim ctl As Control[/FONT]
[FONT=Arial]For Each ctl In Me.Controls[/FONT]
[FONT=Arial]If ctl.Enabled = True Then[/FONT]
[FONT=Arial]  If ctl = "" Then[/FONT]
[FONT=Arial]      MsgBox "You must enter all criteria required "  [/FONT]
[FONT=Arial]      GoTo Err_ReportExcel_Exit[/FONT]
[FONT=Arial]  End If[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]Next ctl[/FONT]

I've tried searching this site and google but i keep getting swamped with results on how to do basic data validation.

Any help would be greatly appreciated

 

[pbaldy]

When you loop all controls you run the risk of running into one that doesn't have the property you're testing. You might first test the ControlType for things like acTextBox so you only test controls of the appropriate type.

 

[delikedi]

Your code examines all controls on the form, among which are controls that do not have the .enabled property, such as labels.

My suggestion:

Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        If ctl.Enabled = True Then
            If ctl = "" Then
                MsgBox "You must enter all criteria required "
                GoTo Err_ReportExcel_Exit
            End If
        End If
    End If
Next ctl

 

[veraloopy]

Dim ctl As ControlFor Each ctl In Me.Controls If ctl.ControlType = acTextBox Then If ctl.Enabled = True Then If ctl = "" Then MsgBox "You must enter all criteria required " GoTo Err_ReportExcel_Exit End If End If End IfNext ctl

This has removed the error but then my report just runs even though I have left all the fields blank... I 'm not getting my msgbox pop up asking me to complete all fields

 

[pbaldy]

You want a test like the one here:

http://www.baldyweb.com/BeforeUpdate.htm

You're only testing for an empty string, not Null.