Applying user entered data from one field to another

[Turbojohn]

Hi,

I am currently building a stock control system in access. I have a form with which the user selects a stock item from 3 combo boxes (stock name, stock weight and stock grade). The combo box links to a table of stock items. A stock item is made up of a name, weight and grade (all separate fields in the stock items table).

I wish to setup the form so as the user selects the stock name from the first combo box. Based on the value selected by the user, the appropriate stock weight(s) assoicated with that stock name will be displayed in another combo box.

Basically, I wish to take the result from one field in a form and apply its result to a query(?) in another field so as to filter(?) my result accordingly.

Hope that makes sense. Any suggestions?

Cheers
Turbojohn

 

[Michael J Ross]

Hi,

You need to look at cascading combos, this link should help:

http://office.microsoft.com/en-gb/assistance/HA011730581033.aspx

 

[Turbojohn]

Hi,

You need to look at cascading combos, this link should help:

http://office.microsoft.com/en-gb/assistance/HA011730581033.aspx

Thanks for the link!

It worked well for the stock weight but I am having slight difficulty with the stock grade. It gives me the values I want, but duplicates these twice. This is understandable since I have 2 entries. I have tried using DISTINCT, however, an error message is given of a conflict with my ORDER BY clause. The row source (or query) for my combo box is:

SELECT DISTINCT [Stock Items].Grade
FROM [Stock Items]
WHERE ((([Stock Items].Weight)=[Forms]![Enter Stock]![Stock Weight]))
ORDER BY Val(Left([Grade],(InStr(1,[Grade],"/")-1)));

And the error message I get is:

ORDER BY clause Val(Left([Grade],(InStr(1,[Grade],"/")-1))); conflicts with DISTINCT

The Val function is required since the grades consist of two parts separated by a "/".

Any ideas on how to solve this problem?

Many Thanks
Turbojohn

 

[Michael J Ross]

No, sorry I can't help you with that one. I just use distinct when I have multiple values which is working for you.