Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box List Items (2003 (11.6566.6568) sp2)

    Hi there,

    I'm playing around with Combo boxes with the view to giving users the option of selecting not just an individual item, but also depending on what has been chosen in the other Combo boxes, the ability to select "All Data" items so as to change the filtered set of records. I've seen and been trying to use a union query in the combo box Row Source with mixed results. I have two combo boxes, one which has two field selections, the first being the key column and the second the data item I wish to display and the second combo box which has one field. I've attached the DB for interrogation as it will probably make more sense than my explanation of the problem.

    The combos work like this. 1st combo box selection is "Gross Premiums" and 2nd combo box selection is "2000" - Filters accordingly.

    User then selects "Claims" and "All Years", which filters again to what it says.

    User then selects 2001, and this filters the claims by YOA 2001

    User then selects "All Transactions" in the first combo and everything goes blank.

    I can't fathom how to make the first combo work so that if "All Transactions" is selected, all transactions are displayed. I can make it work for the second combo box. The reason I believe, lies with the union query. The source for the second combo box is

    select "<<All Years>>" as [YOA] from [Mapped Data] union SELECT [Mapped data].[YOA] FROM [Mapped data];

    and the reference to this in the list box query criteria for the field of [YOA] is

    IIf([Forms]![Form1]![Combo4]="<<All Years>>",([Mapped data].[YOA]),[Forms]![Form1]![Combo4])

    The "<<All Years>>" is new to me, I saw it fleetingly on another system, but am unable to contact the person who wrote it. I would actually just like to put an * in in its place, but am having trouble with that.

    The first combo box has the following source code

    SELECT 0 as [CD], "<<All Transactions>>" as transaction_type FROM [tbl:LPSO Transaction Codes] UNION (SELECT [tbl:LPSO Transaction Codes].CD, [tbl:LPSO Transaction Codes].Transaction_type FROM [tbl:LPSO Transaction Codes]) ORDER BY CD;

    and I tried a similar criteria setting in the list box query to the first as follows:-

    IIf([Forms]![Form1]![Combo0]="<<All Transactions>>",[Mapped data].<!t>[Transaction code],[Forms]![Form1]![Combo0])

    The problem I believe lies with the 0, but I've tried a number of alternatives both in the union and the list box query and keep ending up with the same result.

    I would appreciate any solutions anyone has to this.

    Cheers,

    Niven
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box List Items (2003 (11.6566.6568) sp2)

    Combo0 has two columns, the first one is hidden but this contains the actual values. If the user selects <<All Transactions>>, the value of the combo box is 0. So you must change the condition to

    IIf([Forms]![Form1]![Combo0]=0,[Mapped data].<!t>[Transaction Code],[Forms]![Form1]![Combo0])

    I'd do this in Query1 and set the Row Source of the list box to Query1.

    Here is the complete SQL for Query1:

    SELECT [Mapped data].Synd, [Mapped data].[Risk Code], [Mapped data].[Processing Yr], [Mapped data].YOA, [Mapped data].CCY, [Mapped data].<!t>[Transaction code], [Mapped data].SumOfValue, [Mapped data].Transaction_type, IIf(Forms!Form1!Combo0="<<All Transactions>>",Null,Forms!Form1!Combo0) AS Expr1
    FROM [Mapped data]
    WHERE ((([Mapped data].YOA)=IIf([Forms]![Form1]![Combo4]="<<All Years>>",[Mapped data].[YOA],[Forms]![Form1]![Combo4])) AND (([Mapped data].<!t>[Transaction code])=IIf([Forms]![Form1]![Combo0]=0,[Mapped data].<!t>[Transaction Code],[Forms]![Form1]![Combo0])));

    It would also be possible to return all values if the user clears the combo box.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box List Items (2003 (11.6566.6568) sp2)

    Hans,

    Many thanks for that nice & simple as usual!

    Cheers,

    Niven <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box List Items (2003 (11.6566.6568) sp2)

    Hans,

    Sorry, what's the best way to place an Asterix as the default value in the Combo Boxes? i.e. instead of saying "<<All Data>>", just have *

    Cheers,

    Niven

  5. #5
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box List Items (2003 (11.6566.6568) sp2)

    Hans,

    Sorry again, please disregard my last post as I've just worked it out!

    Cheers,

    Niven <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •