Problem with a list box with SQL statement as rowsource

[L1sta]

Hi
I've got a form which allows users to search for specific Archive boxes. The form takes input from a text box and outputs to a list box. The listbox's rowsource is a simple SQL statement. This all works fine but I'm having trouble with searches that have no results - All i want is a 'No results found' prompt or equivalent but my attempts have failed.
I'm hoping that there's a really simple answer that I just haven't thought of.
Can anyone help?
Lista

 

[PHV]

but my attempts have failed
Which attempts ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[L1sta]

Hi
I was trying to use isnull on the list box after the SQL statement is run but this just displayed the message whether there were results to display or not. This is the code:
(strSQL1 and strSQL3 are the select commands which run the search - they work fine so I didn't put them in)



Sub sBuildListBox()
' Code that builds the new RowSource for the list box.
If IsNull(Me!txtInput) Then
strSQL = StrSQL1 & Chr(34) & "*" & Chr(34) & strSQL3
Me!lstResults.ColumnCount = 6
Me!lstResults.ColumnWidths = "0cm;3cm;3cm;3cm;3cm;3cm;3cm"
Me!lstResults.RowSource = strSQL

If IsNull(Me!lstResults) Then
MsgBox "There are no results to display"
End If

End If

If Not IsNull(Me!txtInput) Then
strSQL = StrSQL1 & Chr(34) & "*" & Me!txtInput & "*" & Chr(34) & strSQL3
Me!lstResults.ColumnCount = 6
Me!lstResults.ColumnWidths = "0cm;3cm;3cm;3cm;3cm;3cm;3cm"
Me!lstResults.RowSource = strSQL

If IsNull(Me!lstResults) Then
MsgBox "There are no results to display"
End If
End If

 

[PHV]

You wanted this ?
If Me!lstResults.ListCount <= 0 Then
MsgBox "There are no results to display"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[L1sta]

PERFECT!! Thank you!! ^_^