checking to see if controls are empty

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello all,

This should be even simpler than it sounds but its not working! I want to
see if there comboboxes on the form are empty or not, there is a set of combo
boxes that exist across several forms and i want to test if they are empty or
not before i go onto use the values of them. There are 4 comobo boxes called
[Building], [Block], [Flat] and [Room].

Im trying to setup a function called CheckAddress which checks to see if the
value of each of those isNull and have passed them as parameters in the
function. The FormName and surname are further parameters hence the call is

if CheckAddress(FormsName, Me![Building], Me![Block], Me![Flat], Me![Room],
Me![Surname]) Then
'use the values

but if any of the values are null (apart from FormsName) then i get an
Invalid use of null error message.

is there no way i can use a function to check this??

with regards and thanks

Amit
 
Use the Nz Function. I am guess from the field names they are text, so I
used empty strings to return. For numbers, substitiute a zero. The Nz
checks the value passed and if it is Null or 0, it returns the value you
specify, otherwise it returns the value that was passed to it.

if CheckAddress(FormsName, Nz(Me![Building],""),Nz( Me![Block],""),
Nz(Me![Flat],"", Nz(Me![Room],""),
Nz(Me![Surname]),"") Then
'use the values
 
Hi,

What I usually do is check these types of controls during the Form_Load
event so that I can either attempt to re-fill the controls, or cancel the
ability to perform an insert into the database with a null value (such as
disabling the "Save" button or displaying a message box).

While the Nz function is a great test and gives you the ability to use an
alternate value, I suggest checking at the earliest possible time so that you
can deal with it appropriately. Here is what I use duing my Form_Load event.

Private Sub Form_Load()
Dim controlTest As Control

For Each controlTest In Me.Controls
If controlTest.ControlType = acComboBox Then
If controlTest.ListCount <= 1 Then
' Do something here...
End If
End If
Next controlTest
End Sub

For some reason when I am using tables/queries on my combo boxes, empty ones
give a list count of 1 so that is why I used the <= operator. But using this
option doesn't hard code any of the combo box names; therefore if you add any
new ones later on down the road, you won't have to remember to add a check
anywhere else.

Good luck!

Lance
 
Back
Top