Hierarchical combo boxes - how do I add records?

[a75537]

I'm trying to build a database application(Access 97), to help different departments build internal audits.

Each audit can have multiple sections, and each section contains multiple questions.

I've built 3 tables:

AUDIT - AuditIndex(Autonumber), AuditType

AUDITSECTION - AuditSectionIndex(Autonumber), AuditIndex(Foreign Key to AUDIT), AuditSectionTitle, AuditSectionOrder

QUESTIONS - QuestionIndex(Autonumber), AuditSectionIndex(Foreign Key to AUDITSECTION), AuditIndex(Foreign Key to AUDIT), Question.

I've completed building a form which has a combo box for the AUDIT which displays the AuditType. A second combo box displays the AuditSectionTitle and the AuditSectionOrder from AUDITSECTION based upon the selection of the AUDIT combo box. This is accomplished using a query where AUDITSECTION.AuditIndex = [Forms]![Audits]![cboAuditType].[Value].

The questions for each section are displayed in a subform based upon the selection of the AUDITSECTION - second combo box. The subform uses a query with the criteria QUESTIONS.AuditSectionIndex = [Forms]![Audits]![cboSection].[Value].

I need to be able to add/edit/delete new audits, new audit sections and new questions. Whenever I try to set the 'LimitToList' property to 'No' on the combo boxes I get an error stating it can't be changed because the first visible column isn't equal to the bound column. I need the bound column to be equal to the primary key so I can use it in the queries for the 2nd combo box, and subform.

Has anyone else encountered this problem with hierarchical combo boxes?

 

[Trendsetter]

I would not use the LimitToList property to add records such as this but would use a series of forms which would be linked to the on_DblClick event of the combo.
If this is not clear I can send you something similar.

 

[a75537]

Please send me your solution. You can either post it here or send it to me at a75537@yahoo.com.

My preference would be to have everything displayed on the same form, but I may use your solution if this is not possible.

Thanks.