Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2005
    Posts
    196

    Unanswered: Add value to Combo box with SQL statement

    I have created a combobox with the following SQL Statement as the rowsource:
    Code:
    SELECT First(tbl1.User) AS [User Field] FROM tbl1 GROUP BY tbl1.User;
    Basically, tbl1 has multiple entries from each user and the above statement lists each different user just once (so rather than having 20 different instances of user1, user1 is only listed once).

    This works exactly as I want it to. However, I would like to add the value <<ALL>> to the result, so I can show all the users in the table, with <<ALL>> as the top selection, without adding a record to tbl1 with the user <<All>>. I will then code the variable I use in a different SQL statement to be a wildcard if <<ALL>> is selected. Is this possible? Cheers.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select DISTINCT?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2005
    Posts
    196
    Yes, it seems the following SQL does the same thing in a much simpler way:

    Code:
    SELECT DISTINCT tbl1.Entered FROM tbl1;
    Many thanks for that - that could have saved me an hour yesterday!

    Is there a way to Add the value "<<ALL>>" to the result, without it being in tbl1?

  4. #4
    Join Date
    May 2007
    Posts
    14
    You can try to add “ALL” like Default Value in combobox.

  5. #5
    Join Date
    Aug 2005
    Posts
    196
    Many thanks for your reply - I already tried that but putting a default value doesn't appear to do anything at all. I also tried the following in the row source:
    Code:
    "<<ALL>>" AND (SELECT DISTINCT tblAuditTrail.AuditTrailEnteredBy FROM tblAuditTrail;)
    This generated an error - am I on the right lines?

  6. #6
    Join Date
    Aug 2005
    Posts
    196
    Worked it out - For the benefit of anyone searching in future: Rather than type the SQL into the row source, I set an ADO connection when the form opened. Then I opened the recordset with the sql string above and set a string variable called strRowSource. I Set strRowsource to equal "<<ALL>>;" and then using a loop, added all the other value to the string with the ; character. I then set this variable as the row source.

Posting Permissions

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