Test for Null or "" VBA code. Get Run Time Error 6 Overflow.

[Phred]

Access 2007, SQL Server 2008 R2, Development not Production.

I have a modal form called "Add New Property" When the end user wants to create a new Property record they use this screen. See screen snap. There is a lot of Validation code running in the background as each field is filled out. The very last test before the data is saved to the dbo_Property table is this last check to make sure none of the fields is null.

The code executes when the user click the "Save This Property" button. My code used to run fine. Now I get an error message "Run Time Error 6 Overflow."



I have several problems.

If IsNull(Me.CBOSelCounty) _
           Or (Me.SelectkPIN) _
           Or (Me.CBOSelTown) _
           Or (Me.TownshipNO) _
           Or (Me.CycleName) _
           Or (Me.CBOSelClass) _
           Or (Me.CountyDesc) _
           Or (Me.CountyLOA) _
           Or (Me.CBOkAtty) _
           Or (Me.PropActDT) _
           Or (Me.ChkAddDetails) _
           Or (Me.CBOCtyNM) = True Then
            Call MsgBox("All visible fields must be filled out." _
                        & vbCrLf & "" _
                        & vbCrLf & "Please enter missing data." _
                        & vbCrLf & "" _
                        , vbExclamation, "Missing Data")
        End If

1. I think my code is clunky, and inefficient. There must be a better way.
2. My code does not check for "", only null.
3. I don't know if this is a case for NZ. I don't really understand NZ and I have no experience with it.
4. I am not looking necessarily to make my code work. I am looking for a better approach. But if you have a solution, hey I'm game.

My goal is to test each field on the form to make sure it is populated before running a SQL INSERT INTO query.

I have not included all of the other code as this is the only segment that is non-functional. If you need it I can post.

Thanks,



Fred

[June7]

Why not bound form?

Think need the IsNull() on each control, although not sure about checkbox. Is it set for Triple State?

Think should remove = True.

Can use looping code that will look at every control without explicitly listing the controls. Set Tag property with an indicator value (such as: Required) and in a conditional If Then validate only the controls that have the indicator.