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?
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?
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().
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?
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.
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.
>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
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....
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?
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.
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..