Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Adding ALL to combobox

    Hi All,

    It's that time again for me top pick your brains...

    I have two comboxes on a main form which links to a subform.
    I have the comboxbox selecting Surname and a Dead tick box, which works fine. The subform changes as it should with whatever is selecting in the comboboxes by means of using a query that links to the combobox selections.

    Now here's my problem - I want to add (All) to the surname combobox.
    I have done this before and it works using a union query linking to the comboxbox source - eg: combosurname is

    Code:
    SELECT Surname, Surname FROM TBL_Wills
    UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Wills;
    But it doesn't appear to ber working, any idea's anyone -
    I have picked my brains and can't see further than the end of my nose with the code..
    what am I doing wrong or is there another way of getting the same results...

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's a wild stab in the dark
    Code:
    CASE WHEN MyComboBoxValue = "(ALL)" THEN * ELSE MyComboBoxValue END
    Alternatively use VBA - far easier imho
    *shrug*
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    ahh i don;t know why I didn't think of that? doh!

    but where would i place this though - it doesn't work if i place it in the query comboxsurname or in the VBA of the combobox update?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    My initial guess would be in the query - but you know your system far better than I do Let me know how you get on / your solution
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2004
    Posts
    287
    my thoughts but can you point me in the right direction, which query wqould it be - i have tried all types and it states CASE can not be used, as I have to use SELECT, etc etc etc

    the combosurname query is actually a union query that adds (All) to the top of the list.
    Last edited by NeilMansell; 06-01-07 at 06:48.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by My thoughts
    Is CASE supported by Access?
    What about Iif statements?
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2004
    Posts
    287
    right i have been testing various options and it appears the new code has to be within the combosurname query. But can you say whether this would still remain an union query or create a new query as I appear to be having problems getting it to work within the union one.
    I have tried the iif statement as well as other things but to no avail.

  8. #8
    Join Date
    Mar 2004
    Posts
    287
    actually i was wrong, i have traced my steps and it appears it needs to go in the query that queries the subform data not the combobox data.

    i am using an union query for the combobox that just displays all records or (All), whereas the actually query (Qry_EditorSub) is the query I need to be working on.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    might be better to use an unbound combo/list box, populate the box yourself (probably in the forms on load event)
    place your code in the box's on click event to interpret whatever and generate the SQL or filter.. or what ever it is you are doing.

    bear in mind the combo/list box wizards 'just' take some of the grind out of getting a box to look OK and work. There's nothing stopping you populating it as you see fit.

    if you are usign the list box as a filter and its a string type you coudl set the 'ALL' item to *........ that way it will find all records automatically

    if you are using the value in the listbox elsewhere in the form then you will need to do a bit of work to find what the user actually wants... you need to make sure that the 'ALL' category cannot be used as a row value... but then again no doubt your RI rules will preclude that
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2004
    Posts
    287
    my problem here i don't believe is the combobox, its the query of the subform.
    basically the subform below runs a query that links to the comboxbox on the main form. the comboxbox is displaying the correct results with (All) at the top and the surnames. But I can't seemt o get to grips with how the subform query should be reading (All) as null or *???

    I am attempting to place coding into the subform query (Qry_EditorSub) but so far can't get it working.

  11. #11
    Join Date
    Mar 2004
    Posts
    287
    right here's the latest update:

    i got it working to the method of selecting the All and filtering all the records but my problem is that all it does is filter ALL the records with the surname being replaced by the name in the comboxbox on the main form.

    Here's my code;
    Code:
    SELECT TBL_Wills.ID, TBL_Wills.MailshotInclude, IIf(Forms!Editor!cboSurname="(All)","*",Forms!Editor!cboSurname) AS Surname, TBL_Wills.Dead, TBL_Wills.Forename, TBL_Wills.Initials, TBL_Wills.Title, TBL_Wills.Address1, TBL_Wills.Address2, TBL_Wills.Address3, TBL_Wills.Address4, TBL_Wills.Postcode, TBL_Wills.[Will date], TBL_Wills.[Will date 2], TBL_Wills.[Will date notes], TBL_Wills.[Codicil Date], TBL_Wills.[Codicil Date 2], TBL_Wills.[Codicil Notes], TBL_Wills.StaffRelative, TBL_Wills.ExecutorOther, TBL_Wills.ExecutorPalmers, TBL_Wills.ExecutorBoth, TBL_Wills.[Executor name], TBL_Wills.[Executor Details], TBL_Wills.[Old Will Revoked], TBL_Wills.[Old Will Revoked 2], TBL_Wills.Notes, TBL_Wills.Benfleet, TBL_Wills.MichaelThomas, TBL_Wills.LargeWill, TBL_Wills.[Will taken by], TBL_Wills.[Date Will taken], TBL_Wills.[Reason Will taken], TBL_Wills.Comments, TBL_Wills.EvolutionCase, TBL_Wills.[Client No in evolution], TBL_Wills.Mailshot, TBL_Wills.Response, TBL_Wills.Location
    FROM TBL_Wills;
    my surname criteria on thw query is set to the following;
    Code:
    Surname: IIf(Forms!Editor!cboSurname="(All)","*",Forms!Editor!cboSurname)
    any idea's?

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    When we want to add criteria to a query we use a WHERE clause - I notice a lack of them here
    George
    Home | Blog

  13. #13
    Join Date
    Mar 2004
    Posts
    287
    sorry i am totally stuck now, i have look up on the net, tried various way of doing this - I'm either in the wrong location (which I believe i am not) or totally coming at this the incorrect way..

    Can you help by stating where i would put the WHERE command in my query, at the moment I have a seperate Surname2: column which holds the iif statement as above and sets the surname depending on this iif.
    I still believe this is the way it is done but can't figure out how?!

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Give this a bash - not really sure it will work but it certainly looks pretty
    Code:
    SELECT surname
    FROM MyTable
    WHERE surname = Forms!Editor!cboSurname
    IIf(Forms!Editor!cboSurname="(All)",""," OR 1=1",)
    George
    Home | Blog

  15. #15
    Join Date
    Mar 2004
    Posts
    287
    nope that doesn't work - errors!
    and are you taking about placing this under Surname2 expression field or totally wiping the query back to SQL Query coding with yours above?

Posting Permissions

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