Hiding Rows in List Box

[rat_b76]

Hi All,

I have a list box linked to an SQL search.

On certain items if searched returns blank rows, is there any code which can hide these blank rows so only ones with values are returned?

Cheers & Beers
rat_b76

 

[WayneRyan]

rat,

Use the Where clause to weed out the blank rows.

Wayne

 

[rat_b76]

Hi Wayne,

I know what you mean except I am new to coding SQL this is what I have, can you let me know what I need to include specifically?

Dim strSQLa As String, strOrdera As String, strWherea As String
Dim dbNma As Database
Dim qryDefa As QueryDef
Set dbNma = CurrentDb()
lstCustInfo.ColumnCount = 4
lstCustInfo.ColumnWidths = "0cm;2cm;12cm;3cm"

strSQLa = "SELECT Data.Record,Data.CallReference, Data.CallProblem, Data.CallAuthor " & _
"FROM Data"

strWherea = "WHERE"

strOrdera = "ORDER BY Data.Record;"

If Not IsNull(Me.txtCriteriaCallR) Then
strWherea = strWherea & " (Data.CallReference) Like '*" & Me.txtCriteriaCallR & "*'  AND"
End If

If Not IsNull(Me.txtCriteriaCallP) Then
strWherea = strWherea & " (Data.CallProblem) Like '*" & Me.txtCriteriaCallP & "*'  AND"
End If

If Not IsNull(Me.txtCriteriaCallA) Then
strWherea = strWherea & " (Data.CallAuthor) Like '*" & Me.txtCriteriaCallA & "*'  AND"
End If

strWherea = Mid(strWherea, 1, Len(strWherea) - 5)

Me.lstCustInfo.RowSource = strSQLa & " " & strWherea & "" & strOrdera
Me.lstCustInfo.SetFocus
End If

Any advice, much appreciated.

Cheers & Beers

 

[WayneRyan]

rat,

How can that return blank rows? There are only two possibilities.

1) You enter no criteria in the text boxes. There is not even a
where clause and you will return ALL records.

2) If you enter in any of the criteria, then by definition, it
can't return a null row, cause the criteria will match.

By the way, when do you do the lstCustInfo.Requery

Wayne

 

[rat_b76]

Wayne,

Sorry I should of give you more information. I have a form which has 2 tabs (it for recording IT problem solutions on 1 tab and the possible associated IT Service Centre call on the other). Some solutions may not have an associated call with the solution.

I think I have worked out a way that would possibly rectify the problem, which I should of done at the start, by creating 2 tables, and creating a relationship between record numbers. Not trying to put it in to one table?

On your requery question, I have the following which opens the form with the selected details'

Private Sub lstCustInfo_DblClick(Cancel As Integer)
If IsNull(Me.lstCustInfo) Then
Exit Sub
Else
End If
If frOptionSearch.Value = 1 Then
DoCmd.OpenForm "Data", , , "[Record] = " & Me.lstCustInfo
DoCmd.Close acForm, "Search"
Exit Sub
Else

DoCmd.OpenForm "Data", , , "[Record] = " & Me.lstCustInfo
DoCmd.Close acForm, "Search"
Forms!Data![Associated Logged Call].SetFocus
End If
End Sub