Table lookup

I have 2 tables.  One table call "tCL"that list all products (text value of CL) and paramets of CL are listed as a string of "BC", with each value of a product separated by a coma. Ex: (8.4,8.6)

I then I have another table tStock that has 3 fields, one for qty, one that looks up from TCL the CL value in a value list so the user can select CL and lastly the third tStock column is BC.

I would like the Stock table to look up BC from the CL table but based on the selected entry of CL in the stock table.

Currently have the stock table CL as a list box with a row source of:

This is working fine.

But how I can I setup the stock table BC to lookup BC from tCL based on the CL selected in the stock table?

Something like:

Is this possible?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
<with each value of a product separated by a coma. Ex: (8.4,8.6)>
This sounds like un-normalized data.

This is all difficult to grasp without a sample database for context.
Can you post a sample database following these guidelines:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide and hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

---->>> I would like the Stock table to look up BC from t

Are you referring to 'lookup fields' in your table's design?  If so, then this can't be done.

You'd have to use a form (in datasheet view if desired), and either join the tables on CL in the form's recordsource query or bind the form to the Stock table and use either combo boxes or a textbox with a DLookup in it's control source...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thandelAuthor Commented:
Thanks  mbizup, I think so... I have a look up now to populare via a look up box but now if I understand not possible based on another value within a table based on that selection. I would need a form... is that correct?
<<  I would need a form... is that correct? >>

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.