Working with multiple combo boxes

  • Thread starter Thread starter Calvin
  • Start date Start date
C

Calvin

Hi,

I have developed a form/subform with multiple combo boxes
(a total of five). A user can perform cross-searching
using these combo boxes by pressing a command button. On
the command button, I have put in the code below. It
works fine at the moment, but now I want to more combo
boxes, which will further lengthen the code. I wonder if
there is a better way of doing it.

P.S. I only put a small part of the code

If IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]&[carlaunchquarter]&[carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carstatus]& [carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If
 
You could name your combo boxes in such a way that they can be accessed like
an array. For example cbo1, cbo2, cbo3, etc... Set the tag property of each
combo box to the name of the field they match in the underlying query of the
report, eg. set the tag property of combomodelyear (which is now named cbo1)
to caryear.

Now you can create the where clause within two loops...

Dim ct as integer
Dim NumCbo as integer
Dim WhereClause as string

WhereClause=""
NumCbo=5

For ct=1 to NumCbo
If Not(IsNull(Me("cbo" & ct))) then
WhereClause=WhereClause & Me("cbo" & ct).tag & " & "
Endif
Next

WhereClause=WhereClause & " = "

For ct=1 to NumCbo
If Not(IsNull(Me("cbo" & ct))) then
WhereClause=WhereClause & Me("cbo" & ct) & " & "
Endif
Next

DoCmd.OpenForm "Fcar_outputscreen", , , WhereClause

I haven't tested the above code, but it should illustrate the idea.
HTH
Sam
 
-----Original Message-----
Hi,

I have developed a form/subform with multiple combo boxes
(a total of five). A user can perform cross-searching
using these combo boxes by pressing a command button. On
the command button, I have put in the code below. It
works fine at the moment, but now I want to more combo
boxes, which will further lengthen the code. I wonder if
there is a better way of doing it.

P.S. I only put a small part of the code

If IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]&[carlaunchquarter]&[carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carstatus]& [carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If


.
Click to expand...


you could try building a single query based on your data
then set the criteria in your query to look directly at
your form (where your combo's are) i.e.

criteria 1 would be...

like[forms]![myform].[combo1]

only downfall is that if this combo is empty I find I have
to set the empty combo to * therefore saying select all
from this field.


otherwise you could a single large if endif setting based
on your combo's but then set the forms filter options
based on your selections.


i.e.

if combo1<>"" then
mysetting = feild1 & "='" & combo1 & "'"
end if

if mysetting <>"" then
myfilter = mysetting
filteron=true
else
filteron=false
endif
 
I have tried your suggestion, it doesn't filter any
results. Anything I should have done more here.

thx
-----Original Message-----
You could name your combo boxes in such a way that they can be accessed like
an array. For example cbo1, cbo2, cbo3, etc... Set the tag property of each
combo box to the name of the field they match in the underlying query of the
report, eg. set the tag property of combomodelyear (which is now named cbo1)
to caryear.

Now you can create the where clause within two loops...

Dim ct as integer
Dim NumCbo as integer
Dim WhereClause as string

WhereClause=""
NumCbo=5

For ct=1 to NumCbo
If Not(IsNull(Me("cbo" & ct))) then
WhereClause=WhereClause & Me("cbo" & ct).tag & " & "
Endif
Next

WhereClause=WhereClause & " = "

For ct=1 to NumCbo
If Not(IsNull(Me("cbo" & ct))) then
WhereClause=WhereClause & Me("cbo" & ct) & " & "
Endif
Next

DoCmd.OpenForm "Fcar_outputscreen", , , WhereClause

I haven't tested the above code, but it should illustrate the idea.
HTH
Sam

Hi,

I have developed a form/subform with multiple combo boxes
(a total of five). A user can perform cross-searching
using these combo boxes by pressing a command button. On
the command button, I have put in the code below. It
works fine at the moment, but now I want to more combo
boxes, which will further lengthen the code. I wonder if
there is a better way of doing it.

P.S. I only put a small part of the code

If IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]&[carlaunchquarter]&[carlaunchyear] = [forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carstatus]& [carlaunchquarter]& [carlaunchyear] = [forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If
Click to expand...


.
Click to expand...
 
Try building the string and then adding that to your OpenForm command.

Something like the following, assuming that your current statement works.

Dim strFilter as String

If IsNull(Me.ComboModelYear) = False then
StrFilter = strFilter & "CarYear = [Fcar_outputscreen]![combomodelyear] AND "
End If

If IsNull(Me.ComboMake) = False Then
StrFilter = StrFilter & "CarMake = [forms]![Fcar_outputscreen]![combomake] AND "
End If

....

If Len(strFilter) > 0 then
StrFilter = Left(StrFilter,Len(strFilter)-5)
End IF

IF Len(strFilter) > 0 then
DoCmd.OpenForm "Fcar_outputscreen", , ,strFilter
else
DoCmd.OpenForm "Fcar_outputscreen"
End IF

Now if you want to add another combo and field all you need is to add
IF isNull...
StrFilter = ...
End If
 
Calvin,

Can you post your actual code and I'll have a look.

Oops.. I just noticed that within my second loop you don't need the " & " at
the end of the concatenation line.

HTH
Sam
CALVIN said:
I have tried your suggestion, it doesn't filter any
results. Anything I should have done more here.

thx
-----Original Message-----
You could name your combo boxes in such a way that they can be accessed like
an array. For example cbo1, cbo2, cbo3, etc... Set the tag property of each
combo box to the name of the field they match in the underlying query of the
report, eg. set the tag property of combomodelyear (which is now named cbo1)
to caryear.

Now you can create the where clause within two loops...

Dim ct as integer
Dim NumCbo as integer
Dim WhereClause as string

WhereClause=""
NumCbo=5

For ct=1 to NumCbo
If Not(IsNull(Me("cbo" & ct))) then
WhereClause=WhereClause & Me("cbo" & ct).tag & " & "
Endif
Next

WhereClause=WhereClause & " = "

For ct=1 to NumCbo
If Not(IsNull(Me("cbo" & ct))) then
WhereClause=WhereClause & Me("cbo" & ct) & " & "
Endif
Next

DoCmd.OpenForm "Fcar_outputscreen", , , WhereClause

I haven't tested the above code, but it should illustrate the idea.
HTH
Sam

Hi,

I have developed a form/subform with multiple combo boxes
(a total of five). A user can perform cross-searching
using these combo boxes by pressing a command button. On
the command button, I have put in the code below. It
works fine at the moment, but now I want to more combo
boxes, which will further lengthen the code. I wonder if
there is a better way of doing it.

P.S. I only put a small part of the code

If IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]&[carlaunchquarter]&[carlaunchyear] = [forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carstatus]& [carlaunchquarter]& [carlaunchyear] = [forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If
Click to expand...


.
Click to expand...
Click to expand...
 
Here is my long block of code. Thank you very much.

If IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]&[carlaunchquarter]&[carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carstatus]& [carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[caryear]& [carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carMake]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carMake]& [carlaunchquarter] =[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![combostatus]
&[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carlaunchquarter]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.combostatus)
Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carStatus]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combostatus]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carStatus]& [carlaunchquarter]=[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combomodelyear)
Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[caryear]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combostatus) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[caryear]& [carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combodate) And IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![combostatus]
&[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combodate) And IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[carstatus]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combostatus]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combodate) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combodate) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear]& [carstatus]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carlaunchquarter]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[carstatus]& [carlaunchquarter]=[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear]& [carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear]& [carstatus]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchquarter
]& [carlaunchyear]=[forms]![Fcar_outputscreen]![combodate]
&[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchyear]=[forms]![Fcar_outputscreen]![combostatus]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combostatus]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) And
IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) And
IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.combodate) And
IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]=[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combomake) And IsNull(Me.combodate) And
IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchyear]&
[carstatus]=[forms]![Fcar_outputscreen]![comboyear]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combomake) And IsNull(Me.combodate) And
IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchyear]&
[caryear]=[forms]![Fcar_outputscreen]![comboyear]&[forms]!
[Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.comboyear) And
IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combostatus]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.comboyear) And
IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.comboyear) And
IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]=[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.combostatus)
And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[carlaunchyear] = [forms]![Fcar_outputscreen]![combomake]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.combostatus)
And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[carlaunchquarter] = [forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combodate) And
IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear] = [forms]![Fcar_outputscreen]![combomake]&
[forms]![Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combodate) And
IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchyear]&
[caryear] = [forms]![Fcar_outputscreen]![comboyear]&
[forms]![Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combodate) And
IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchyear]&
[carmake] = [forms]![Fcar_outputscreen]![comboyear]&
[forms]![Fcar_outputscreen]![combomake]"
End If

If IsNull(Me.combodate) And IsNull(Me.comboyear) And
IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus] = [forms]![Fcar_outputscreen]![combomodelyear]
&[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combodate) And IsNull(Me.comboyear) And
IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[carstatus] = [forms]![Fcar_outputscreen]![combomake]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combodate) And IsNull(Me.comboyear) And
IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear] = [forms]![Fcar_outputscreen]![combomake]&
[forms]![Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combomake) And
IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter] = [forms]![Fcar_outputscreen]!
[combostatus]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combomake) And
IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter] = [forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combomake) And
IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[caryear] = [forms]![Fcar_outputscreen]![combostatus]&
[forms]![Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.combostatus) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchyear]
= [forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.combostatus) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchquarter
] = [forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.combodate) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus] =
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combomake) And IsNull(Me.comboyear) And
IsNull(Me.combostatus) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear] =
[forms]![Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.comboyear) And
IsNull(Me.combostatus) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake] =
[forms]![Fcar_outputscreen]![combomake]"
End If

If Not IsNull(Me.combomake) And Not IsNull
(Me.combomodelyear) And Not IsNull(Me.combostatus) And
Not IsNull(Me.combodate) And Not IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[caryear]& [carstatus]& [carlaunchquarter]&
[carlaunchyear] =[forms]![Fcar_outputscreen]![combomake]&
[forms]![Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.carMake) And IsNull(Me.carModel) And IsNull
(Me.caryear) And IsNull(Me.carstatus) And IsNull
(Me.carlaunchquarter) And IsNull(Me.carlaunchyear) And
IsNull(Me.carsegment) Then
MsgBox "Sorry, no matching records."
Cancel = True
End If

Me.Form.Refresh
-----Original Message-----
Calvin,

Can you post your actual code and I'll have a look.

Oops.. I just noticed that within my second loop you don't need the " & " at
the end of the concatenation line.

HTH
Sam
I have tried your suggestion, it doesn't filter any
results. Anything I should have done more here.

thx
-----Original Message-----
You could name your combo boxes in such a way that
Click to expand...
they
can be accessed like
an array. For example cbo1, cbo2, cbo3, etc... Set the tag property of each
combo box to the name of the field they match in the underlying query of the
report, eg. set the tag property of combomodelyear (which is now named cbo1)
to caryear.

Now you can create the where clause within two loops...

Dim ct as integer
Dim NumCbo as integer
Dim WhereClause as string

WhereClause=""
NumCbo=5

For ct=1 to NumCbo
If Not(IsNull(Me("cbo" & ct))) then
WhereClause=WhereClause & Me("cbo" & ct).tag
Click to expand...
& "
& "
Endif
Next

WhereClause=WhereClause & " = "

For ct=1 to NumCbo
If Not(IsNull(Me("cbo" & ct))) then
WhereClause=WhereClause & Me("cbo" & ct) & " & "
Endif
Next

DoCmd.OpenForm "Fcar_outputscreen", , , WhereClause

I haven't tested the above code, but it should illustrate the idea.
HTH
Sam

"Calvin" <[email protected]> wrote
Click to expand...
in
message
Hi,

I have developed a form/subform with multiple combo boxes
(a total of five). A user can perform cross- searching
using these combo boxes by pressing a command
Click to expand...
button.
On
the command button, I have put in the code below. It
works fine at the moment, but now I want to more combo
boxes, which will further lengthen the code. I
Click to expand...
wonder
if
there is a better way of doing it.

P.S. I only put a small part of the code

If IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]&[carlaunchquarter]&[carlaunchyear] = [forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carstatus]& [carlaunchquarter]& [carlaunchyear] = [forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull
Click to expand...
(Me.combomodelyear)
Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus] &
[carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If




.
Click to expand...
Click to expand...


.
Click to expand...
 
Dim ct as integer
Dim NumCbo as integer
Dim WhereClause as string

WhereClause=""
NumCbo=5

'construct where clause
For ct=1 to NumCbo
If Not(IsNull(Me("cbo" & ct))) then
WhereClause=WhereClause & Me("cbo" & ct).tag & " = " & Me("cbo" &
ct) & " AND "
Endif
Next

'remove the final AND if WhereClause contains stuff
If Len(WhereClause)>0 Then
WhereClause=Left(WhereClause,Len(WhereClause)-5)
End If

'Open the form
DoCmd.OpenForm "Fcar_outputscreen", , , WhereClause

This assumes your combo boxes are named cbo1, cbo2, cbo3.... and that the
field name related to each combo is entered in the combos tag property per
previous post.

HTH
Sam

Calvin said:
Here is my long block of code. Thank you very much.

If IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]&[carlaunchquarter]&[carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carstatus]& [carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[caryear]& [carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carMake]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carMake]& [carlaunchquarter] =[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![combostatus]
&[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carlaunchquarter]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.combostatus)
Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carStatus]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combostatus]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carStatus]& [carlaunchquarter]=[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combomodelyear)
Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[caryear]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combostatus) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[caryear]& [carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combodate) And IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![combostatus]
&[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combodate) And IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[carstatus]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combostatus]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combodate) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear]& [carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combodate) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear]& [carstatus]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]& [carlaunchquarter]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[carstatus]& [carlaunchquarter]=[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear]& [carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear]& [carstatus]=[forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchquarter
]& [carlaunchyear]=[forms]![Fcar_outputscreen]![combodate]
&[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchyear]=[forms]![Fcar_outputscreen]![combostatus]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combostatus]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) And
IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchyear]=[forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) And
IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.combodate) And
IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]=[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combomake) And IsNull(Me.combodate) And
IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchyear]&
[carstatus]=[forms]![Fcar_outputscreen]![comboyear]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combomake) And IsNull(Me.combodate) And
IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchyear]&
[caryear]=[forms]![Fcar_outputscreen]![comboyear]&[forms]!
[Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.comboyear) And
IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combostatus]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.comboyear) And
IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]=[forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.comboyear) And
IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]=[forms]![Fcar_outputscreen]![combomodelyear]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.combostatus)
And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[carlaunchyear] = [forms]![Fcar_outputscreen]![combomake]&
[forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.combostatus)
And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[carlaunchquarter] = [forms]![Fcar_outputscreen]!
[combomake]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combodate) And
IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear] = [forms]![Fcar_outputscreen]![combomake]&
[forms]![Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combodate) And
IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchyear]&
[caryear] = [forms]![Fcar_outputscreen]![comboyear]&
[forms]![Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.combostatus) And IsNull(Me.combodate) And
IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchyear]&
[carmake] = [forms]![Fcar_outputscreen]![comboyear]&
[forms]![Fcar_outputscreen]![combomake]"
End If

If IsNull(Me.combodate) And IsNull(Me.comboyear) And
IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus] = [forms]![Fcar_outputscreen]![combomodelyear]
&[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combodate) And IsNull(Me.comboyear) And
IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[carstatus] = [forms]![Fcar_outputscreen]![combomake]&
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combodate) And IsNull(Me.comboyear) And
IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake]&
[caryear] = [forms]![Fcar_outputscreen]![combomake]&
[forms]![Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combomake) And
IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[carlaunchquarter] = [forms]![Fcar_outputscreen]!
[combostatus]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combomake) And
IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter] = [forms]![Fcar_outputscreen]!
[combomodelyear]&[forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.comboyear) And IsNull(Me.combomake) And
IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus]&
[caryear] = [forms]![Fcar_outputscreen]![combostatus]&
[forms]![Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.combostatus) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchyear]
= [forms]![Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.combostatus) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carlaunchquarter
] = [forms]![Fcar_outputscreen]![combodate]"
End If

If IsNull(Me.combomake) And IsNull(Me.combomodelyear) And
IsNull(Me.combodate) And IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus] =
[forms]![Fcar_outputscreen]![combostatus]"
End If

If IsNull(Me.combomake) And IsNull(Me.comboyear) And
IsNull(Me.combostatus) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear] =
[forms]![Fcar_outputscreen]![combomodelyear]"
End If

If IsNull(Me.combomodelyear) And IsNull(Me.comboyear) And
IsNull(Me.combostatus) And IsNull(Me.combodate) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carmake] =
[forms]![Fcar_outputscreen]![combomake]"
End If

If Not IsNull(Me.combomake) And Not IsNull
(Me.combomodelyear) And Not IsNull(Me.combostatus) And
Not IsNull(Me.combodate) And Not IsNull(Me.comboyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[caryear]& [carstatus]& [carlaunchquarter]&
[carlaunchyear] =[forms]![Fcar_outputscreen]![combomake]&
[forms]![Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.carMake) And IsNull(Me.carModel) And IsNull
(Me.caryear) And IsNull(Me.carstatus) And IsNull
(Me.carlaunchquarter) And IsNull(Me.carlaunchyear) And
IsNull(Me.carsegment) Then
MsgBox "Sorry, no matching records."
Cancel = True
End If

Me.Form.Refresh
-----Original Message-----
Calvin,

Can you post your actual code and I'll have a look.

Oops.. I just noticed that within my second loop you don't need the " & " at
the end of the concatenation line.

HTH
Sam
I have tried your suggestion, it doesn't filter any
results. Anything I should have done more here.

thx
-----Original Message-----
You could name your combo boxes in such a way that they
can be accessed like
an array. For example cbo1, cbo2, cbo3, etc... Set the
tag property of each
combo box to the name of the field they match in the
underlying query of the
report, eg. set the tag property of combomodelyear
(which is now named cbo1)
to caryear.

Now you can create the where clause within two loops...

Dim ct as integer
Dim NumCbo as integer
Dim WhereClause as string

WhereClause=""
NumCbo=5

For ct=1 to NumCbo
If Not(IsNull(Me("cbo" & ct))) then
WhereClause=WhereClause & Me("cbo" & ct).tag & "
& "
Endif
Next

WhereClause=WhereClause & " = "

For ct=1 to NumCbo
If Not(IsNull(Me("cbo" & ct))) then
WhereClause=WhereClause & Me("cbo" & ct) & " & "
Endif
Next

DoCmd.OpenForm "Fcar_outputscreen", , , WhereClause

I haven't tested the above code, but it should
illustrate the idea.
HTH
Sam

message
Hi,

I have developed a form/subform with multiple combo
boxes
(a total of five). A user can perform cross- searching
using these combo boxes by pressing a command button.
On
the command button, I have put in the code below. It
works fine at the moment, but now I want to more combo
boxes, which will further lengthen the code. I wonder
if
there is a better way of doing it.

P.S. I only put a small part of the code

If IsNull(Me.combomake) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carstatus]&[carlaunchquarter]&[carlaunchyear] =
[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomodelyear) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carMake]&
[carstatus]& [carlaunchquarter]& [carlaunchyear] =
[forms]!
[Fcar_outputscreen]![combomake]&[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull (Me.combomodelyear)
Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[carstatus] &
[carlaunchquarter]& [carlaunchyear] =[forms]!
[Fcar_outputscreen]![combostatus]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If

If IsNull(Me.combomake) And IsNull(Me.combostatus) Then
DoCmd.OpenForm "Fcar_outputscreen", , , "[caryear]&
[carlaunchquarter]& [carlaunchyear]=[forms]!
[Fcar_outputscreen]![combomodelyear]&[forms]!
[Fcar_outputscreen]![combodate]&[forms]!
[Fcar_outputscreen]![comboyear]"
End If




.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top