Query based on visible/not visible cascade combo boxes

[mexttor]

Hello,

I am trying to create a query based on 5 inter-dependent combo-boxes. At the criteria of each field than i want to "filter" i put something like [Forms]![FormName]![cbo1].

My main problem is that these combo boxes are all set to "not visible" (except the first one) at the begging and based on the selection of each upstream combo, the next one become visible. Eg: I select a section, then the lines in that section become visible and i select a line, then the equipment referring that line becomes visible and so on.

However, not every time i get to choose data from all 5 combos and probably the last 2 will never show up, since they have no data to display.

I believe that access doesn't know how to query non-visible data from combo-boxes... I also tried something like "Like IIf([forms]![FormName]![cbo1]="","*",[forms]![FormName]![cbo1])

If i put a criteria for a query only for the visible fields, it works...

Any ideas how can i solve this issue?

 

[arnelgp]

Like IIf(Trim([forms]![FormName]![cbo1] & "")="","*",[forms]![FormName]![cbo1])

 

[mexttor]

Hello,

Thanks for your quick reply. Unfortunately, i tried your solution and the query is returning only one entry... Based on my example, it should return 3 entries..

 

[JHB]

Show the whole SQL-string, some sample data and the result you want.

 

[mexttor]

I managed to figure out a solution using VBA:

I created a string variable to compile my "WHERE" statement from the SQL code so that I can use as criteria only the visible combos:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim sqltext As String
Dim qdfNew As DAO.QueryDef
Dim strWhere As String

Set db = CurrentDb

strWhere = ""

If Me.cboZona & vbNullStr <> vbNullStr Then
strWhere = strWhere & "((Tasks.[Zona/Sectie]) = '" & Me.cboZona & "'" & ")"
End If
If Me.cboLinie & vbNullStr <> vbNullStr Then
strWhere = strWhere & " AND ((Tasks.Linie)= '" & Me.cboLinie & "'" & ")"
End If
If Me.cboCelula & vbNullStr <> vbNullStr Then
strWhere = strWhere & " AND ((Tasks.Celula)= '" & Me.cboCelula & "'" & ")"
End If
If Me.cboMasina & vbNullStr <> vbNullStr Then
strWhere = strWhere & " AND ((Tasks.Masina)= '" & Me.cboMasina & "'" & ")"
End If
If Me.cboComponent & vbNullStr <> vbNullStr Then
strWhere = strWhere & " AND ((Tasks.Component)= '" & Me.cboComponent & "'" & ")"
End If

sqltext = "SELECT Tasks.Pierderea, Tasks.[Cauza BD], Tasks.[Descrierea Problemei], Tasks.[Cauza Radacina], Tasks.Contramasura1, Tasks.Contramasura2, " & _
"Tasks.Contramasura3 , Tasks.Contramasura4, Tasks.Contramasura5, Tasks.[Timp Oprire], Tasks.[Timp Reparatie], Tasks.[Timp Cautare], Tasks.ID, " & _
"Tasks.Data , Tasks.[Zona/Sectie], Tasks.Linie, Tasks.Celula, Tasks.Masina, Tasks.Component FROM Tasks WHERE (" & strWhere & "); "


DoCmd.SetWarnings False
db.QueryDefs.Delete ("qryFindRCFA")

Set qdfNew = db.CreateQueryDef("qryFindRCFA", sqltext)
DoCmd.OpenReport "Find RCFA", acViewReport

Basically, it checks if i have information in the combos and computes a string playing the role after the WHERE clause in the SQL. After this, i'm computing the whole SQL string required for the query, create a query and then open a report based on that query.