Two Combo Boxes with "All"

[JohnHarkins]

Hi!

I've done the usual bit with a second combo based on the contents of the first. what I'm having particular difficulty with is adding (and referencing) "(all)" to the items in the first in order to let the second box effectively ignore the contents of the first.

I accomplished the "(all)" with the following string:

SELECT DISTINCT tblPersons1.Dept FROM tblPersons1 UNION SELECT "(all)" FROM tblPersons1;

That works fine.

However, I cannot get the SQL query in the second to select all items in the second combo box to substitute "like '*'" in the criteria to pick up all groups when "(all)" is present in the first combo box.

SELECT DISTINCT tblPersons1.Dept, tblPersons1.Group
FROM tblPersons1
WHERE (((tblPersons1.Dept)=IIf([forms]![frmSelectReports]![cboDept]="(all)","like ""*"" ",[forms]![frmSelectReports]![cboDept])));

I hope someone can help... thee hours fiddling around with various types of quotes, etc. is too much effort for so little reward.

Thanks


John Harkins

 

[jgoodman00]

I think it would be easier to use an If Else Statement:

Dim strSQL As String
If Me.cboDept = "All" Then
strSQL = "SELECT * FROM tblPersons1"
Else
strSQL = "SELECT * FROM tblPersons1 WHERE tblPersons1.Dept = '" & Me.cboDept & "'"
End If

Me.cboPersons.RowSource = strsql
Me.cboPersons.Requery

James Goodman MCP

http://www.james-goodman.co.uk

 

[monarips]

hello, i am also facing the similar problem can u help me in this .
i have a combo box which picks up category_Id from a table category
the values in category_Id fields is 1 and 2
so if a user select 1 then Category name related to 1 is displayed in the text box . so i want a field in combo box "ALL " so that all the vales related to 1 and 2 are displayed when the user selects "ALL"
Plz help me out.
Thanks,
Mona

 

[TonyJollans]

Hi JohnHarkins,

The root cause of your problem is trying to conditionally include operators in your WHERE clause. Try, instead ..

WHERE (((tblPersons1.Dept) Like IIf([forms]![frmSelectReports]![cboDept]="(all)","*",[forms]![frmSelectReports]![cboDept])));

You might find that's not exactly right and you need to tweak it a bit but the important bit is to move the Like outside the Iif.

Enjoy,
Tony

 

[JohnHarkins]

Thanks, Tony!

John Harkins

 

[Lilo27]

This code worked for me. However when I move from record to record, it seems as COMBOBOX2 value becomes set for all records. If I change it to another value, it changes all values for all records. How can you keep the value set for each record?

 

[TonyJollans]

Hi Lilo27,

I think you must be trying to do this on a continuous form. As with any other unbound control there is only one value (or set of values in this case) per form, not per record - and not a lot you can do about it.

Please come back if I've misunderstood.

Enjoy,
Tony