custom analysis fields

[sillysod]

Hi there,

I want the user to be able to define an unlimited number of custom analysis fields

so i have a table which holds an analysis category name, and another code indication the area of the database that a particular category relates too.
eg the Sales Person category relates a Customer, and therefore the user is able to enter a sales person against a customer.

now i have got this working quite well, having another table which holds the selections of the user, which are displayed on a datasheet subform. on the customer screen

Now im having problems adding the ability to store preset values
Ie user needs to be able to enter possible sales persons, and then the user has a drop down box on the form, and selects from the list of values

So, i have a table which holds possible values for each category

I hve added the list t the datasheet no problems, however im having trouble narrowing the rows of the list box down to just show values that apply to the category in question

im using this as the row source of my combo box, but it just shows all the values stored and not a filtered list as required

SELECT tblAnalysisValueLists.Value, tblAnalysisValueLists.AnalysisName
FROM tblAnalysis INNER JOIN tblAnalysisValueLists ON tblAnalysis.Category = tblAnalysisValueLists.AnalysisName
WHERE (((tblAnalysisValueLists.AnalysisName)=[tblAnalysis]![Category]));


Anyone able to tell me why this doesnt narrow the results as needed?

Thanks

 

[PHV]

just show values that apply to the category in question
Which category ?
FROM tblAnalysis INNER JOIN tblAnalysisValueLists ON [blue]tblAnalysis.Category = tblAnalysisValueLists.AnalysisName[/blue]
WHERE (([blue](tblAnalysisValueLists.AnalysisName)=[tblAnalysis]![Category][/blue]));
The 2 blue snippets are redundant...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244