Capturing results of SQL to ListBox 2

[TeresePamela]

I have a form where the user inputs the data they want to search for and clicks a "search" button which runs an SQL statement. The results of the SQL is the rowsource for a listbox. All is ruuning fine except I want to display a msgbox stating "No Records Found" if none were found. I cannot figure out how/where to do this. Basically I don't think I understand where SQL is holding the results of the query and how it's passing it to the form, so I don't know what code to put where :-( A sample of the coding:

strSQL="SELECT gidata.recordid, gidata.filnr, gidata.descript" & "FROM gidata"
strwhere = "WHERE"
If Not IsNull(Me.txtfileNr) Then
strwhere = strwhere & "(gidata.dfilnr) like '*" & me.txtfilnr & "*'"
strOrder = "ORDER BY gidata.filnr;'
End If

Me.lstGenDetails.rowsource = strSQL & " " & strWhere & "" & strOrder

Can anyone enlighten me?

As always, all input is appreciated.

Pam

 

[ByteMyzer]

Try this modified code:

strSQL="SELECT gidata.recordid, gidata.filnr, gidata.descript" & "FROM gidata"
strwhere = "WHERE"
If Not IsNull(Me.txtfileNr) Then
  strwhere = strwhere & "(gidata.dfilnr) like '*" & me.txtfilnr & "*'"
   strOrder = "ORDER BY gidata.filnr;'
End If

Me.lstGenDetails.rowsource = strSQL & " " & strWhere & "" & strOrder
[b]Me.lstGenDetails.ReQuery
If Me.lstGenDetails.ListCount = 0 then MsgBox "No Records Found"[/b]

 

[TheAceMan1]

How are ya TeresePamela . . .

Another variation . . . . .

[blue]   Dim Msg As String, Style As Integer
   Dim Title As String, cnt As Long
   
   strSQL = "SELECT gidata.recordid, gidata.filnr, gidata.descript" & "FROM gidata"
   strWhere = "WHERE"
   
   If Not IsNull(Me.txtfileNr) Then
      strWhere = strWhere & "(gidata.dfilnr) like '*" & Me.txtfilnr & "*'"
      strOrder = "ORDER BY gidata.filnr;'"
      cnt = [purple]DCount("[recordid]", "gidata", strWhere)[/purple]
   Else
      cnt = [purple]DCount("[recordid]", "gidata")[/purple]
   End If
   
   Me.lstGenDetails.RowSource = strSQL & " " & strWhere & " " & strOrder
   
   If Not cnt Then
      Msg = "No Records returned!"
      Style = vbInformation + vbOKOnly
      Title = "No Records . . . . ."
      MsgBox Msg, Style, Title
   End If[/blue]

See Ya! . . . . . .

 

[TeresePamela]

Thanks aceman1 and bytemyzer. Both worked fine - now to decide which to use

Thanks guys I appreciate it.

Pam