Linking Combo Box contents

[bigred0269]

Hi.
I have created a database for an old office filing system in work.
There are 2 tables – Files & File Contents.
Table ‘Files’ has 5 fields: ID; Number (e.g. 1/03); Title; Status (e.g. Live/Closed – created a list on the relevant form using the Lookup wizard when creating the table) and Referenced To – at some point in the future all these files will be closed and this last field will show what all future correspondence is filed on.
Table ‘File Contents’ basically lists all correspondence on every file. But, some of the files have categories exclusive to them.
In the form ‘File Contents’ if someone was inputting new correspondence I would like the category lists to show just the relevant category for that file, i.e. if File 1/03 Training is selected then the 3 Training Categories will show and if File 5/03 is selected then the 2 categories relevant to it are shown.
But I don’t know how to do that…..
Should I create new tables for each of the ‘Files’ showing the categories or would one category table do?
I would be grateful of any help/advice you can give me.
Thanks.
Philip

 

[M8KWR]

You could do this a couple of ways, but i would like to try and get more info out of you, so i can advise the best way for you.

When you say each file has own category, is the category related the the file number, so for example a file number of 1/03 - the "1" represents the category?

Even if you create a single table which lists the categories and the you would have nothing to relate it to back in the record, which is needed.

So if the "1" does represent training, then you would have to create a single table, with lets say 3 field ( IDNo, Cat No, Descrition). The IDNo, as an autonumber The Cat No would be the linking field to the record, so you would have multiple entries of "1", but a description next to it.

In your form you would have a combo box looking at this new table that you create, but in the record source it would look at the first digit of the "number" field in your record to decide what options the user has to select from.

I hope this makes sense and is what you require, if not let me know. But if it is and you need further info off how to set it all up then please let me know.