FILTERING A LISTBOX WITH COMBO BOXES. 1

[dougal1269]

OK guys here it is, this one is a toughie at least for me.

The concept: have a locations list box filtered by combo box results and have the locations list box filter another list box containing IP addresses for that location.

The problem: with so many fields acting on each other my code looks horrible, there has got to be a better way, oh and I also need the IP Addresses to come out in order (just for the fun of it)

The ground work:
Tables:
tblRegion (RegID,nameRegion)
tblServiceArea (SAID, nameSA, fldRegID)
tblFacilityCode (FCID, nameFacCode, fldSAID)
tblLocation (LocID, nameLoc, fldFCID, fldBuildID)
tblBuilding (BuildID, nameBuild, fldFCID, fldLocID)
tblCloset (ClosetID, nameCloset, fldBuildID)
tblEquiptment (IPAddress, nameDevice, luReg [lookup combo box to tblRegion], luSA [tblServiceArea], luFac [tblFacilityCode], luBuild [tblBuilding], luCloset [tblCloset]

note: ok so all the information is located in the tblEquiptment, so how do I get the filtering to work. Oh yes this is an extensive database these are just a few of the tables.

If anyone has any sugestions, clues, answers it would be great.

Dougal
Ita erat quando hic adveni
"It was that way when I got here"

Dougal
Sentio aliquos togatos contra me conspirare.
I think some people in togas are plotting against me.

 

[PeteJohnston]

I've got myself a standard way of doing this which I cut&paste into each new application. There are several combo boxes, one for each foreign key on the main file. The code builds an SQL select statement and uses it to populate the list box as follows:

If Not IsNull(Me.cboCustomer) Then
  txtFilter = txtFilter & " CustomerNumber = '" & Me.cboCustomer & "' AND"
End If
If Not IsNull(Me.txtInvFrom) Then
  txtFilter = txtFilter & " InvoiceDate >= " & Me.txtInvFrom & " AND"
If Not IsNull(Me.txtPaymentFrom) Then
   txtFilter = txtFilter & " PayDate >= " & Me.txtPaymentFrom & " AND"
End If
    
' Strip trailing AND if required
If Right(txtFilter, 3) = "AND" Then
  txtFilter = Trim(left(txtFilter, Len(txtFilter) - 3))
End If
strSQL = "SELECT * FROM tblInvoices"
If Len(txtFilter) > 0 Then
   strSQL = strSQL & " WHERE " & txtFilter
End If
        
'Add ORDER BY clause
strSQL = strSQL & " ORDER BY " & mstrSortOrder
lstInvoices.RowSource = strSQL
lstInvoices.Requery

This application uses a combo box for the customer table and a couple of dates for from and to invoice date. If nothing entered the field holds NULL so it doesn't add it to the WHERE clause so no selection is done on that column of the main table. The sort sequence depends on which column heading you click but it is just held as a text string eg. "IPAddress ASC". You'll need some other code elsewhere to set it up if you want to let them change the sequence.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[dougal1269]

thanks ... I will try this out. {P

Dougal
Sentio aliquos togatos contra me conspirare.
I think some people in togas are plotting against me.

 

[lamago]

HI,

I new posting here, I have 6 combo boxes and I used the procedure you described but it seems pretty slow to generate the list, is there a way to speed it up? The problem is I have 3 tables on which the information is found. Here's my code, any ideas to speed it up?

Private Sub ApplyFilterstoForm()
Dim ctl As ComboBox
Dim strFilter As String

'set the control Filter

strFilter = ""
On Error Resume Next
For Each ctl In Me.Controls
If ctl.Tag = "Filter" And (Not (IsNull(ctl) Or ctl = "")) Then
If IsNumeric(ctl) = True Then
strFilter = strFilter & "(([" & Mid(ctl.Name, 4, 25) & "])=" & ctl & ") AND "
Else
strFilter = strFilter & "(([" & Mid(ctl.Name, 4, 25) & "])='" & ctl & "') AND "
End If
End If
Next ctl


strFilter = " WHERE (" & Left(strFilter, Len(strFilter) - 5) & ")"
'strTryGroup = ", " & Left(strTryGroup, Len(strTryGroup) - 2)



sqlString = "SELECT DISTINCT [" & strShowField & "] FROM [" & strCurScores & "] " _
& "INNER JOIN (ScannedAnswers_BatchTracks INNER JOIN [Select Codes] ON ScannedAnswers_BatchTracks.[Select Code] = [Select Codes].Code) " _
& "ON [" & strCurScores & "].BatchNo = ScannedAnswers_BatchTracks.BatchNo " _
& strFilter & ";"


End Sub

 

[PeteJohnston]

The loop thru the .Controls collection is a reasonable way to build the WHERE clause but I doubt whether it is that which makes it slow. I think a DISTINCT clause with two INNER JOINs is going to be pretty slow whichever way you do it. If you create a query that does the same thing as the SQL you are building on the fly how fast is it? Is there any way that you can simplify the SQL?

Different comment
I don't think the bit of code which adds WHICH to the selection string will work if you don't select any of the controls. In this case the string will have length zero. You should only add the WHERE if Len(strFilter) is greater than zero.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)