lookup column based on another lookup column (1 Viewer)

Hi All,

I need help please, I have a table called [Returns Table], on this table i have a lookup column called Hospital Name which looks up from the table [Hospital List] what i want to do is to create another lookup column that looks up the account manager but based on the hospital name

we have 3 people per hospital name i want to limit the people by hospital

is this posible

many thanks

boblarson said:

Lookups should not be used at table level. See here for more about that.

You can use them at FORM level, but should not at table level.

Click to expand...

appologise

this is being updated from a form but i dont know where to go from there

thanks

boblarson said:

It would basically be a "Cascading Combo" if I am getting what you're saying. So, perhaps this sample will be of use.

Click to expand...

Hello

Will a cascading combo box update my table, this is the reason i am using lookup in a table so a drop down comes on my form will update the table

boblarson said:

Yes, if you bind the combos to the fields. They should be retrieving the ID (and displaying text) and storing the ID number.

Click to expand...

sorry for the questions but how do you bind a column? i have created a combo box that looks up off my hospital list but it wont update my returns table

Plus thanks for the quick replys

boblarson said:

When you click on the control while the form is in design view - on the properties dialog you should see (under the DATA tab) a property called RECORD SOURCE. Use the drop down to select the field to bind to.

Click to expand...

Pal i could kiss you????

This has been bugging me for a long time many thanks have rep'd
as you were helpful , fast and understanding (Newbie at access)

Hey Bob - I have a similar request in that I want am wanting to enter a value in one field (on a form) and it pulls in the corresponding reference from a separate linked table. Can this be done by entering the value as opposed to selecting it from a combo box?

In Bob's absence I'll chip in.
You normally use a combo to restrict the available options to ones that exist.
So if you had a list of customers and wanted to list outstanding invoices for a specific customer, there are two or three ways of achieving this.
1. Have an unbound text box and simply enter the customer name. Make some code go and find if there is a customer called "Smiffs" and then tell you user no such customer, because the user can't spell
2. Have a combobox that lists all you customers, your user starts to type Smi and it fills out the rest "Smiths" - a valid customer. They select Smith's only to be told by your code that there aren't any outstanding invoices form Smiths.
3. As number 2, BUT you make the rowsource of the combo box only list customers with outstanding invoices. This means they can only look for those. No need for complicated messages saying "There are no records" !

Does that help ?