Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2004
    Posts
    86

    Unanswered: Help storing combbox text value not ID

    Hi,

    I have a db (attached)
    which has three comboboxs which are all related to their relevent tables.
    i.e Employee combobox links to employee table etc...

    The question:

    the three comboboxes output to a table calls CMS Compliance Table, (which is where i want to store my records)
    however the data stored in their is the "ID" from the tables and not the text values of the fields.
    I want to be able to store the text values in the table
    for example i select (the numbers are the ID column which is hidden)

    cbo 1: Joe Bloggs, 24
    cbo 2: Joes Manager, 2
    cbo 3: Joes Dept, 31

    I want the text value stored in the table and not the ID
    can anyone help me on this please?

    thank you for any assistance

    Scott.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    why do you use IDs then? just don't use them?
    ghozy.

  3. #3
    Join Date
    Oct 2004
    Posts
    86
    I use, ID's because for example

    i select an employee and the team manager and department are automatically populated with the relevent details,

    cbo 1: joe bloggs
    cbo 2: jos managers
    cbo 3: joes dept.

    each field has and ID and text column i wanna store the text column in a table but all i can get is the ID column
    ive tried changing the bound column which causes errors

  4. #4
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Hi

    In the properties for your combo boxes you have set the bound column as column 1. The first column in the result set from your query is the id. Hence that is the value of the combo box even though you have hidden it and displayed the other two columns instead.

    The solution is simply to set the bound column to 2 (on the data tab of the properties dialog) or adjust your SELECT query so that the name is the first column.

    However as a comment about best practice it is normal to store the foreign key (id) and not any of the attributes. One reason (unlikely in your case) is if the name changes (oh, I don't know - perhaps a female emplyee gets married). The id remains the same and you automatically retrieve the new name. Another reason is that you are certain of the backward audit trail. Say you have more than one 'Fred' on your source table, you don't know which one it is.
    Rod

    fe_rod@hotmail.com

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I agree with Rod.
    and if you need the name for displaying in messagebox or something you can have it like:
    Code:
    msgbox YourCombobox.Column(0)
    will give you the first column (index 0=first column) information in your combobox.
    ghozy.

  6. #6
    Join Date
    Oct 2004
    Posts
    86
    Rod, Ghozy,

    thank you for your replies,

    I appriciate the benefits and good practice of a normalised database, however on this occasion, i use the table to export into a excel pivot table hence the requirements for text value of the comment box. the form is used for data input only at the moment.

    do you know if i can get the text into the table or can you suggest a way of using a normalised database, but when exporting it fetches the text values via a query.

    thank you for your help?

  7. #7
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    well you could add the table that you keep names into your query and since ID fields are connected you could add a Name field from the name table. and you could export this field instead of ID number.
    ghozy.

  8. #8
    Join Date
    Oct 2004
    Posts
    86
    excellent, ill try this, i may need some help in constructing this query as once i have the query done, i need to work out how i can export this to excel.

  9. #9
    Join Date
    Oct 2004
    Posts
    86
    Ghozy,

    Sorry im a bit lost, do you mean ammend my SELECT query or create a query that will bring all the data together?
    if its the later can you explain how i can do this becauase when i try this in the wizard it says i cant bring in data from two different tables

    thank you

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I don't know the structure of yor project, so I can't be helping too detailed. but you could create a query without wizard and add the tables you want into this query. then select the fields you want to display. then export this query to excel.
    ghozy.

  11. #11
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    ghozy,

    Methinks this man knows too much, is he here to test us. I never said 'normalised' and I'm sure neither did you. The use of the 's' indicates he comes from the east side of the Pond.

    scottemotive: yes, wonderful example, one of the few situations where you need to carry the actual, resolved value forward to the next process. Pivot tables, eh? In my days in market research they used to call them cross tabs.
    Rod

    fe_rod@hotmail.com

  12. #12
    Join Date
    Oct 2004
    Posts
    86
    Rod,

    Great observation, Yes im from UK, thank you for your appriciation, however im really just finding my way with this database, ive not really created one before so im learning things and possibilities as i go.

    I have two paths i can either stick with a database that stores the values (ID's) to which i will need a query to tie in the information when i export

    or i need someone to look at my code for the three comboboxes, i can change the bound column on the employee cbo and this will store the selected result (i.e the employee text) but this causes problems with the selection code of my other cbo's even after changing the bound columns on them.

    your thoughts and help are appriciated

  13. #13
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    maybe if you can attach the table or at least explain the structure, we could come up with a SQL query for you.
    ghozy.

  14. #14
    Join Date
    Oct 2004
    Posts
    86
    great thanks

    ill attach now

  15. #15
    Join Date
    Oct 2004
    Posts
    86
    attached is my table and also my form, however this was a copy paste into a blank database so it may not run as intended.
    Attached Files Attached Files

Posting Permissions

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