If controls are null exit sub else run search

[Sharkiness]

Hi Guys,

I pinched some code from another thread but I thought I would start a new one as the other thread was quite old.

    For Each ctl In Me.Detail.Controls
 
        If ctl.ControlType = acTextBox Then
            If IsNull(ctl.Value) Then
                MsgBox "No information has been provided", vbOKOnly
                Me.lstResults.RowSource = ""
                Exit Sub
            Else
            ListSearchresults
            End If
 
        End If
    Next ctl

The problem I am having is that the message appears even if one of the textboxes have an entry. I want the message to flag only if all textboxes are incomplete.

If i enter a value in a textbox that i know exists, the results appear, and the message box but then the disappear due to the code

me.lstresults.rowsource=""

the reason i have this is that if someone searches for results they appear, but then if they clear the boxes i want the rowsource to be empty with the message flagging.

Otherwise the lstresults populates with all records as I have a few 'Like' search parameters in the code 'Listsearchresults'.

I dont want these like results to show as people should have access to records without a need to search on them.

Any help would be great.

 

[Kiwiman]

Howzit

You will want to perform the loop until it comes to an end as all are empty or until it finds 1 with a value in it - then do your actions

Something like

Dim iCnt as integer

iCnt = 0
 For Each ctl In Me.Detail.Controls
 
        If ctl.ControlType = acTextBox Then
            If IsNull(ctl.Value) Then

            Else
                icnt = icnt + 1
                Exit For
            End If
 
        End If
    Next ctl

If icnt = 0 then
   MsgBox "No information has been provided", vbOKOnly
   Me.lstResults.RowSource = ""
Else
   ListSearchresults
End if

 

[Sharkiness]

Worked a charm,

Thanks Kiwiman