In subform, view option list from related third table instead of foreign key

[jdjdjd]

Hi,




Table1 is my checklists.


Table2 is item statuses.


Table3 is checklist items and has foreign keys related to Table1 checklist ids and Table2 item status ids.


In a Table3 subform to a Table1 form, how can I view status options when making changes or additions to records in Table3 in the subform? I don't want to see the foreign keys I want to see the real status names/options.


In other words, I want to see a list of all the items in a specific checklist with their statuses and be able to make any changes to the items and item statuses.


I'm new to Access and have tried different solutions with subforms and comboboxes but can't get it rigth..


Many thanks

[rpeare]

Can you post a sample of your database, I'm not entirely sure what you're asking.

If you have a set of checklists each with different items that must be verified

i.e.

Checklist A
    CheckList Item A
    CheckList Item B
Checklist B
    CheckList Item A
    CheckList Item C
    CheckList Item D
Checklist C
    CheckList Item D
    CheckList Item E
    CheckList Item C
    CheckList Item G

And each checklist item has a status associated with it your table structure wouldn't support that.

If it's simply that you want to record the FK (foreign key) of your status table in your checklist items table you can use a combo box with the BOUND column being 1 COLUMN COUNT of 2 and COLUMN WIDTHS of 0,1 to show only the status text but store the FK.

[jdjdjd]

Thank you for your reply. I have attached a simple demo database but still haven't gotten any further. What I need is to be able to;


1. choose a checklist in the form
2. see a list of all items in this checklist displayed in the form
3. for each item see its status
4. when selecting the item's status (or adding a new item) in this list I want to see an option list of all available status names to be able to add/change status
5. when I change an item's status only this item's status should be changed, not the status name in the status table


Not sure if this is possible at all. I can adapt the structure of the database any way required to achieve this.

[rpeare]

Attached is your example database with a new form/subform allowing the checklist/checklist items form/subform.

However, your data doesn't really mean much because a checklist may be applied to a task, or a series of checklists may be applied to a task so you would like want a form/subform/sub subform arrangement to handle it if you are going to go that deep.

Checklists.zip

EDIT: note if you are creating customized checklists you will want to have the first text box in the subform be a combo box so you can edit the list on the fly.

Errr..

EDIT 2: but then you would not hold the status on the items table but you would need another table that stored the itemID and the status for a particular test result.

[jdjdjd]

Brilliant, that was exactly what I needed - many thanks for your kind help