Counting Blank Fields as a Variable

[bsnalex]

I hope someone here can help--I know a very limited amount of VBA.

What I'd like to is create a function that can look at the fields on a form and determine how many of the fields are blank/null (3 fields are dropdowns, 2 are freetext, 1 is free-number). I'm sure it would be something to do with a For Each/Next command, I just don't know the appropriate syntax. Basically I want to look at Field 1 and if it's blank mark a 1, go to field 2, if it's blank mark a 1...after all the fields are determined, add up the "1s" . The end result is I want to take the result of the function and insert it as criteria into the query that runs the search function in the background (the search results finds matches in the search criteria-- I want to count the number of matches and display results n-1-- if that makes sense.

Screenshot of design mode:

The fields [SHID] and [CarryVal] wouldn't need to be counted as they are invisible in the form mode. Thanking anyone in advance.

 

[bsnalex]

Never mind-- a few minutes after i posted I came up with the following code-- it might be a bit long-winded, but it works...

note-- I added a field call "CritVal" into my source table. This code adds up the non-blank fields and inserts that value into [CritVal]. That value is then used as criteria to run against the query.

Private Sub Command16_Click()

Dim V1 As Variant
Dim V2 As Variant
Dim V3 As Variant
Dim V4 As Variant
Dim V5 As Variant
Dim V6 As Variant

DoCmd.RefreshRecord

If Nz(Forms![frmSearchTrainees]![SHSNAME], 0) = 0 Then
    V1 = 0
    Else
    V1 = 1
End If

If Nz(Forms![frmSearchTrainees]![SHFNAME], 0) = 0 Then
    V2 = 0
    Else
    V2 = 1
End If

If Nz(Forms![frmSearchTrainees]![SHSPEC], 0) = 0 Then
    V3 = 0
    Else
    V3 = 1
End If

If Nz(Forms![frmSearchTrainees]![SHHEI], 0) = 0 Then
    V4 = 0
    Else
    V4 = 1
End If

If Nz(Forms![frmSearchTrainees]![SHPROG], 0) = 0 Then
    V5 = 0
    Else
    V5 = 1
End If

If Nz(Forms![frmSearchTrainees]![SHCHRT], 0) = 0 Then
    V6 = 0
    Else
    V6 = 1
End If

Me.CritVal = V1 + V2 + V3 + V4 + V5 + V6
End Sub

I realise there's probably a shorter way of doing this, but it works

 

[CJ_London]

you can make it simpler

1. if your code is in the frmSearchTrainees form then you do not need forms!frmSearchTrainees

2. you don't need the if statements

If Nz(Forms![frmSearchTrainees]![SHSNAME], 0) = 0 Then
V1 = 0
Else
V1 = 1
End If

can be replaced with

V1=abs(nz(shsname,0)<>0)

of if just testing for nulls

V1=abs(not isnull(shsname))

 

[sneuberg]

I don't understand what you are trying to do, but it you just want a count of the the empty text and combo boxes you could do something like. The code would be run in the form's module.

Dim ctrl As Control
Dim CtrlCount As Long
For Each ctrl In Me
    If ctrl.ControlType = acComboBox Or ctrl.ControlType = acTextBox Then
        If Len(ctrl & vbNullString) = 0 Then
            CtrlCount = CtrlCount + 1
        End If
    End If
Next ctrl
MsgBox CtrlCount

 

[arnelgp]

in addition to mr.sneuberg's function, you should also add Tag to the controls (textbox, combobox) which you don't want to include, since you have a calculated textbox there, i think.

Dim ctrl As Control Dim CtrlCount As Long
For Each ctrl In Me.Controls
If ctrl.ControlType = acComboBox Or ctrl.ControlType = acTextBox Then
If Trim(ctrl.Tag & "") = ""
If Len(ctrl & vbNullString) = 0 Then
CtrlCount = CtrlCount + 1
End If
End If
End If
Next ctrl
MsgBox CtrlCount

 

[bsnalex]

Thanks everyone for the responses. Sneuberg's was what I was going for in the simplest way, but I appreciate the other suggestions.