Using Module or function to find null values

[Azhar.iq]

Hi everyone,

I am currently learning to develop using Access and VBA, and is also working on a project.

Now i have a form with couple of textboxes bound to a table. When the user opens the form to enter records, i want to write a function that would go through the textboxes to check whether the textboxes are left NULL. Now i can write code on button click for each form, but i was wondering if its possible to write a function that could be called for each form that i have to check for null values.

Thanks.

 

[Ranman256]

I have users enter into an 'entry table', then validate the fields, then if all is correct I run an append query to the master table.

usage:
if IsValidForm() then 'do stuff
'run data process macro
else
'user must correct entries
end if


'---------------
'access
'---------------

public Function IsValidForm() as boolean
dim vMsg

  select case true
       case txtName = "" 
          vMsg = "Client Name is missing"

       case isnull(cboState )
          vMsg = "State is missing"
      cboState.setfocus

  end select
  if vMsg <>"" then msgbox vmsg,vbCritical,"Required"
  IsValidForm =vMsg =""
end sub

 

[The_Doc_Man]

I'm not sure that a form's unbound textbox can ever be null - though I know it can be empty. (There is a distinction.) Forms impose an automatic default value of blanks for display purposes. If the textbox isn't bound then it won't have a source of nulls.

On the other hand, the NZ function with a default of "" (an empty string) is a pretty good test for whether the box contains anything at all. IF NZ(x,"") = "" .... pretty much unequivocal.

 

[Azhar.iq]

Is there a way to use a loop to go through all the textboxes, no matter how many are there in a particular form (to check for Null or empty values)?

 

[Cronk]

Not tested

function TestForNulls(frm as Form)
dim ctl as control
for each ctl in frm.controls
if ctl.controltype = actextbox then
if isnull(ctl) then
[whatever]
endif
endif
next ctl
end function

 

[Solo712]

Is there a way to use a loop to go through all the textboxes, no matter how many are there in a particular form (to check for Null or empty values)?

Sure you can check it. This is how you circle through all the textboxes on a form:

Private Sub TextboxesCheck()
   Dim frm As Form, ctrl As Control
 
   Set frm = Forms("frmMyForm")   '<--your form name goes here
   For Each ctrl In frm.Controls
       If TypeName(ctrl) = "TextBox" Then
           if isNull(ctrl) Then
                ' do whatever you want to do 
            End if     
        End If
   Next ctrl
   End Sub

Best,
Jiri

 

[Azhar.iq]

Thanks guys for the response.

So lets say i want this function to execute on some button click, i just write (type) the function name in that sub, right??

 

[Cronk]

No. Right click on the button in design mode and select "Build Evcnt". Or select the button properties, select the Events tab and select the onClick event.