I'm working on a very CRUD-heavy MS Access application, which has over 50 forms for entering and working with data. This, of course, involves lot of testing to see if a textbox, recordset, etc. has a value and then doing something with it.
To prevent mistakes such as testing for
IF not rst.BOF and rst.EOF or simply having to look up a test I don't remember, I've created a single function that runs all of my empty/no value tests. Simply pass
NOVALUE() an object variable and it uses the
Typename() function to select the correct test and return a true/false. No more thinking about tests and no chance of introducing a bug at this point.
The function's purpose is to see if the user has input/selected something or if a recordset has not returned any records. I don't care whether the
.RecordSource of a cascading combo/listbox has no rows because the object will also have no value. Also, I'm only using native Access controls, so the function will not test a TreeView or other ActiveX control.
Public Function NoValue(ObjectToTest As Object) As Boolean Dim noVal As Boolean NoValue = True If ObjectToTest Is Nothing Then noVal = True Else Select Case TypeName(ObjectToTest) Case Is = "CheckBox" noVal = IsNull(ObjectToTest.Value) Case Is = "ComboBox" If ObjectToTest.ItemsSelected.Count = 0 Then noVal = True Else noVal = False End If Case Is = "Label" If Len(ObjectToTest.Caption) = 0 Then noVal = True Else noVal = False End If Case Is = "Listbox" If ObjectToTest.ItemsSelected.Count = 0 Then noVal = True Else noVal = False End If Case Is = "Recordset" '* ADO If ObjectToTest.State = adStateClosed Then noVal = True ElseIf ObjectToTest.BOF And ObjectToTest.EOF Then noVal = True Else noVal = False End If Case Is = "Recordset2" '* DAO If ObjectToTest.BOF And ObjectToTest.EOF Then noVal = True Else noVal = False End If Case Is = "TextBox" If Len(ObjectToTest.Value & vbNullString) = 0 Then noVal = True End If Case Else ' Only using native access types, not activex, so there shouldn't be anything here ' Should I err.Raise ? End Select End If NoValue = noVal End Function
Here's the reasoning behind the tests:
- Begin with a test for
ObjectToTest Is Nothingto make sure that the object variable has been initialized.
- ADO Recordset: If both .bof and .eof are true, an ADO recordset has no records. I've added the test for closed recordsets because I've occasionally come across them.
- DAO Recordset: Just the standard test for .bof and .eof. There is no way to check for a closed DAO recordset.
- Checkbox: Testing for null, because 0 and -1 (false/true) are values.
- Combobox/Listbox: There are two possible tests that can be performed here, depending on how you want to handle table values that are not in the object's
- Labels: Although these aren't data-bound controls they can have their captions set in code, so I've included them for the sake of consistency -- I really don't want to have to think about testing controls.
- Testing the to see if object's
.ControlSourceIs NULL will return false if the
.ControlSourcehas a value (i.e. there is a value in the underlying table). A combo/listbox with an invalid value in the underlying table (a value that doesn't appear in the
.RowSource) not be NULL, but no item will be selected in the list.
.ItemsSelected.Countwill tell us whether the user has selected an item, regardless of the value in the underlying table. Before the record is saved, the underlying value in the table may be null, but
.ItemsSelected.Countwill reflect the user's selection. This is what I'm interested in.
- Textboxes: A textbox that contains an empty string will not be null, even though it has no value for my purposes. So, the function tests for both NULL and an empty string. I always use the constant
""in my code so there is no chance of confusing
- Are all of my tests correct? Are there any edge-cases that I have missed?
- My SELECT ... CASE should probably contain a "CASE ELSE" to catch any other object that gets passed to the function. Does it make the most sense to raise an error at this point, or to take some other action?
- Anything else I may have missed or not thought of?