Multiple Combo Boxes and its selection

[rukk]

Hi,

I have a question about the Combo Boxes. I have created a form that has 7 combo boxes. The user may select one of the option from two combo's or some times from 5 combo's or sometimes all 7 combos. Based on the selection criteria when the user clicks a button the query result should come.
I have written a query that takes options from all combo boxes with "AND" statement. I have assigned this query to the command button. But this is working when all 7 combo boxes are selected. "OR" doesn't work for this.
How to write a query that takes an "AND" when that combo box is selected.
Thanks

 

[MakeItSo]

hi rukk!
This should get you on the right track:

Dim sSQL as String, Ctl as Control

For Each Ctl In Me.Controls
  If selectedControl.ControlType = acComboBox Then
   If Not IsNull(selectedControl.Value) Then 
    sSQL= ... & " AND "
   End If
  End If
Next

sSQL=left(sSQL, len(sSQL)-5) 
[green]'To remove the last, superfluous " AND "[/green]

Cheers,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[jfgambit]

See the following post...

it describes how to re-write a QueryDef to take into account your multiple comboboxes.

thread702-563930

"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[rukk]

Hi Andreas Galambos,
Thank you for your quick reply. I have a question in this process. HOw to run the "select" sql statement in Access vba. I know the RunSql command but thats only for Action queries. How to run this query after building it.
Thanks.

 

[MakeItSo]

You define a recordset object:
Dim rs as Recordset
set rs= Currentdb.OpenRecordset("SELECT * FROM ..."
...

Or if you want to have a single query e.g. behind a report, you could do this:
Dim qry as QueryDef
Set qry= currentdb.QueryDefs("your query&quot
qry.SQL="SELECT whatever from wherever"
qry.close

This way you can always "overwrite" your stored query with the desired SQL statement...

Greetings,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos