Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Posts
    86

    Unanswered: QRY - How can i query a column in a combobox?

    Morning,
    in my qry. i want to qry a table based on a selection from a combobox, like a filter, the combobox has 2 columns, a text field and an ID field

    ive tried this
    [Forms]![frmScreeningForm]![cboEmployee.Column(2)]

    which i know is incorrect but you can see what im trying to accomplish from it.

    scenario

    thank you



    Smile its monday!

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Hi!

    Try the following as criterion

    eval("[Forms]![frmScreeningForm]!cboEmployee.Column(1)")

    - combos and lists are indexed starting with 0, so to refer to the second column, you use 1. I don't think queries are fond of referring to columns of combos, but using the Eval function should do the trick.
    Roy-Vidar

  3. #3
    Join Date
    Oct 2004
    Posts
    86
    Hi,

    thank you for this, ive tried this but get no results

    ive attached my db for you to look at, sorry its a FE only but the query and forms are there
    Id appriciated if you could have a look see where im going wrong if you can.


    thank you for your help

    Scott
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    You are using the column index 3 for a three column combo. The last column index available in a three column combo is 2.

    If it is the EmplyeeName field you are after, try my initial suggestion.
    Roy-Vidar

  5. #5
    Join Date
    Oct 2004
    Posts
    86
    ive tried this on column 2 which is the column i need to query the query is run by a subfom so the form will be open which is fine

    my scenario:

    cboEmployee selection made "abotts Josh" which is i.e ID 12

    i then want the subform to filter all previous records in tblMaster for records for this employee and then display them in the subform (but as text format not ID)

    ive tried changing the column to 2 but its not displaying anything

    thank you

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I believe my intial suggestion contained the number 1 (one, uno, ein), not 2.

    Again, to refer to the second column of a combo or list, use the index 1, they start at zero (0). So the first column is 0, the second 1, thrid 2...
    Roy-Vidar

  7. #7
    Join Date
    Oct 2004
    Posts
    86
    Thank you, ive change this to 1 and it displays

    #NAME for employee the rest of the fields are correct

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    In the query or form?

    If form, is there a field named EmployeeName in the query, or should it be AgentName?
    Roy-Vidar

  9. #9
    Join Date
    Oct 2004
    Posts
    86
    in the subform it displays #name, it should be agent name this is what it is in the table but the cbo is called employee(not got round to change this yet)
    however ive taken a slightly different slant, in my query ive got
    AgentName(hidden) Department, Compliance agent etc...

    So... cboEmployee is selected and requeries the subform, the subform runs the query and displays the results i want, which is great, however the results are showing the ID, i want it to show the text value, now i have another query which should be in that uploaded version, this query brings all the tables together with the tblMaster so when run it shows the text values, do you know how i can combine this with the subform query?

    sorry to sound confusing to explain in a nutshell i want the sub form to return text values based on the selection from cboemployee

    thanks for your help on this

  10. #10
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    When a form is bound to a table or query (or an SQL string, or the recordset is assigned thru an ADO/DAO recordset) you can only bind the controls of the form to actual field names exposed by those objects.

    Bind the control to the correct field. If this is still returning the ID, I'll guess you're using table level lookups. See http://www.mvps.org/access/lookupfields.htm for why those aren't exactly encouraged. If you continue using them, you'll need to make sure the table containing the real name you're after (what is looked up in the table level lookup) is included and referenced in the query, and select the <whatever>Name field as one of the fields to be outputted.
    Roy-Vidar

  11. #11
    Join Date
    Oct 2004
    Posts
    86
    you know thinking about it, all the problems im getting are because of these lookup links,

    the reason i have this structure is because on the main form i have a cbo Employeename, this then automatically selects the following two cbo Manager and Department, likewise if Department is selected this filters manager cbo for all managers in this department and the same is applied to employee.
    If i was to change the structure how would you recommend i do it?

    thanks

Posting Permissions

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