Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19

    Unanswered: Foreign Keys in ComboBox

    What do you do to show the name in the ComboBox instead of the actual value, which is just the id? I know of bound colums and setting the size to 0 for one column, BUT I have 2 comboBoxes, one filled with all columnnames of the table and the other gets filled with all the values from that column which is selected by the first. Thats works fine as long the datatype is "text" but what do I do when a foreign key colum gets selected? Somehow a query should then get triggered which gives me the right value to display (and the Id as well, but hidden, cause I need it later)
    Are there any standard ways to solve this kind of problem?

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    If this is for viewing data only, then you could add the column type to the first combo box and when populating the second, make sure the query converts the values to text using cstr().

  3. #3
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19
    Thanks for your reply, but getting the datatype of the column is not enought, I also need to run a second query to get the value to display in the combo Box. I found ADOX which gives me the type of keys in a column, so if type is foreign key -> do query. But how to determin on which table?

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    This is confusing, but I will take another shot at it. I am assuming that your foreign key is not of the text data type. In your first combo box, you could specify the field name, field type, and field to show in box 2. That way, you just have to show the third field and if that field to show is of field type long, then you could use cstr().

  5. #5
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19
    Thanks again, but I think I made the Problem not clear enough:
    in the first Combobox I have the foreign key and you're right, it's not text, it's a long(auto-increment). Let's assume it's an user Id. In the second comboBox I dont't want to show all userIds in that table but the first name of the users. So I need a query like "select firstName from user where Id=idSelected". But how do I find out that I need the table "user" for this colum and let't say the table "customer" for the next Id? And a different query if its a customerId couse then I need the second name? And no query at all if the selected columname is a not a foreign key?
    All I can think of is:
    If the key.type = foreign get the key name,
    look for a query called "keyNameGetThingToDisplayInComboBox" (Which is the select statement from above) and get the second Combobox to display the results of that query.
    But I hoped that there is a more straight forward way for this sort of thing? I'm a Newbie with Access but this must be a standard problem, no?

  6. #6
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19
    And maybe I should mention that I'm looking for a general solution, there are many comboboxes to be filled this way, so I don't want to write special code for everyone.

    Any help would be very appreciated!

  7. #7
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    The way I understand it is you have one combo box (cb1) that lists fields from various tables. When an item is selected from that cb1, you want a second combo box (cb2) to be populated with all records for the chosen field with the exception of keys. If a key field is chosen, you want to be able to show a different field of your choosing where the keys match. You need enough information in cb1 to make this happen.

    Let's look at the possible queries needed for cb2. I will use a couple examples:

    If "Customer Name" is in cb1 then cb2 should contain all customer names: Select CustName from Customers

    If "User" is in cb1 then cb2 needs to show the names of users instead of the userID: Select UserName from Users

    I am guessing that you have a table with a list of the fields to show in combo box 1. That table should have the following fields: Name_To_Show, Column_Name and Table_Name.

    For my examples, the date would be thus:
    Name_To_Show Column_Name Table_Name
    Customer Name CustName Customers
    User UserName Users

    AfterUpdate of cb1, you can create the sql statement for cb2 and requery cb2.

    cb2.rowsource = "Select " & cb1.column(1) & " From " & cb1.column(2)
    cb2.requery

    So far, these examples are pretty much the same. You gave an example: select firstName from user where Id=idSelected

    I have no idea from where idSelected comes.

    Let's go back to the user table, and let's assume that you have a UserPhone table to list all of the customer's different phone numbers (cell, work, home, cottage, camp, spouse, next of kin). These tables are linked by UserID. If your cb1 has userid from the phone table, then you want to show only the phone numbers for a specific user. So far, we only have 2 fields and neither contain the user id. From where would I select the userid. From where are you getting the idSelected in your example?

    If you can explain that to me, then life should be grand.

  8. #8
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19

    Lightbulb

    Yes, we're getting there!

    >have one combo box (cb1) that lists fields from various tables.

    It lists all the fieldnames of one Table(tbl1)

    >When an item is selected from that cb1, you want a second combo box (cb2) to be populated with all records for the chosen field with the exception of keys. If a key field is chosen, you want to be able to show a different field of your choosing where the keys match. You need enough information in cb1 to make this happen

    Exactly, but I don't need the information in cb1 (couse a key field might never get selected), but if it makes things easier, its ok.

    >I am guessing that you have a table with a list of the fields to show in combo box 1

    No, I'm getting them direcly of the table with:
    For Each f In rs.Fields
    f.name
    Next
    There a lots of table which need this sort of thing, so I want Access to tell me all the things it can, rather than creating new tables myself, and I think it can tell me to which table this key is linked.
    So cb1 has (until now) only one column (column(0)) with the column name in it.
    But supose it has a second one (column(1)) with the name of the table it is linked to and a third one (column(2)) with the name of the field of that linked table which I would like to show, say [users].[first_name].
    Then cb2 shows "Select " & cb1.column(2) & ", id, From " & cb1.column(1)

    -- Right, I can see we're getting somewhere here (thinking while typing) ------

    cb2 also has 2 colums, one with the id, one with the cb1.column(2) values, but only shows the latter. Ok.

    ---- (you can hear the cogwheels in my brain working)---

    BUT: I don't want to show all users from the usertable, only the ones who are in tbl1. So I thought of doing a query for each entry in tbl1 "What is the username of the user with this id?" and that was the afore mentioned selectedId. Should I do a query like "SELECT username, id FROM usertable WHERE id IN (SELECT DISTINCT user FROM tbl1)? Could work. But I still need the name of the field (username in this case) stored somewhere. Maybe its easier to make a query like "tbl1query" and than search for a query with a name with the table name in it for each table....

    That helped a lot so far - nearly there ...

  9. #9
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Sorry, but I am i Canada. I cannot hear the cog wheels turning on the other side of the atlantic. My hearing is not that good.

    Why do you want a form like this? Are you trying to have one form that can lookup any data in your system?

  10. #10
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19
    Originally posted by jmrSudbury
    Why do you want a form like this? Are you trying to have one form that can lookup any data in your system?
    Its a search form, if you select the columname in cb(1) and a value from cb(2) than a new form with all that data opens. I need a form like that for a lot of tables in the db. That doesn't have to be the same form all the time, but it would help a lot if it is as similar than possible. But I gather that there isn't a standard solution for it and I have to think about it myself - right?

  11. #11
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Just a thought, but if you are searching through many records, it is fastest to search on fields that are indexed. If you are letting your users search using any field, then you may be inclined to index every field, but this will slow down your add new records and updates. In my Customer table, there are only a few fields that are worth using as a search parameter. No one has ever asked to search based on telephone number unless that is used as the primary key (which is indexed).

    Your design sounds powerful, but how worried are your users about speed?

    My largest search form has 7 combo boxes that each are used to search based on different fields or combinations of fields from one or more table.

  12. #12
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    19
    You're right, I haven't really thought about speed. I have no experience with Access, so I was going to develop the hole thing first and than (if there are any complaints about speed) to tune it later. But maybe that isn't the best way if it means redesign and recoding. It's just that I don't know what makes Access slow down and what not. Is your search with the 7 comboboxes fast enough? And that is only over indexed fields? And how many entries does you database have? Is it Access 2k?
    Because I really do have 6 combos on my form, 3 for selecting a column and 3 for the corresponding values in it, linked with 2 and/or. So far there are only some hundreds of entrys in the tables and it was doing ok - but there is more data to come.

    Got thing with the foreign_keys worked out, but I'm sure I didn't do it the easiest way..

Posting Permissions

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