Help needed with combo box and form building.

[kimberlin]

I know I'm going to have to change the fields in some of my tables so that the foreign key fields just show a number instead of a description after reading some of the posts against using look-up fields in tables on here. No problems with that. So now I'm hitting the problem of how do I get a meaningful word or phrase appearing in a combo-box or form if i've got to change all these features? My main problem is going to be job titles as I've got a many-to-many relationship between those and the workers who are going to have to go into the database, so there's a junction table containing TradeID as a foreign key. I know that the guys I work with are going to want to search by job names and not numbers, so pulling the TradeID into the combo-box is going to be meaningless for them (and I doubt that I'll be able to remember which number refers to which trade either!). I'm also going to have to alter my existing sub-forms as they are pulling details from the same look-up TradeID. How do I go about making meaningful text appear in all those boxes instead of numbers?

 

[pbaldy]

Try the combo box wizard. It should build a combo with a row source that includes both fields, with the ID field hidden. The user will see the text, but the combo will store the ID.

 

[kimberlin]

I think I see what you're getting at but the table I'm going to have to draw that information from only has the fields:
WorkerTradeID
WorkerID
TradeID
level

so if I draw the TradeID and level fields into the combo, the only words which will come up are: qualified, improver, mate, skilled and general; which isn't going to give anything away as to whether they are plumbers, electricians, labourers etc and those words are in the table which the TradeID refers to. I was advised that I needed to have those trades in a separate table as there are some people on our firm's books who are capable of working in more than one trade and all the trades will have more than one person capable of performing them. I need to be able to get things so that the combo box shows things like "plumbers mate" which doesn't happen even though I've tried using the wizard as you suggest.

 

[pbaldy]

That looks like the junction table. I would expect that to be the control source of the combo, but the row source would typically refer to some other table that listed the trades. I assume you have a "Trades" table with TradeID and the description you're looking for?

 

[kimberlin]

Yes, I do have a trades table which just has the "TradeID" and "Trade" as its fields linked to the junction table but the "level" field is only in the junction table, so I don't see how I can put "trade" and "level" into the same combo box if they're in two different tables.

 

[pbaldy]

Sounds like you need two combos, one to select trade and one for level. If you don't have a Levels table, the second combo could be a values list.

 

[kimberlin]

OK, I see what you're getting at. Would it make things easier if I was to move the levels field from the junction table into the trades table or would that muck things up too much? Also, if I do that how would I get on with using the sub-form for trade on my data entry form since there wouldn't be a direct relationship between the workers and trade tables?