Combo boxes in subforms

[Barbj]

I have a database that records results of compliance
audits performed on tasks performed by field staff.

The main tables are:

Tbl Audit Details: [RefNo] - an autonumbering field, and
the primary key. [AuditDate], [Assessor] which is a lookup
field related to a table containing assessor info,
[SiteTeamLeader] - another lookup field, [Work Location] -
Text, [Task] - Text, [Audit Type] - lookup, [Comments] -
Memo.

Tbl Audit Results: [RefNo] - related to RefNo in Audit
Details Table and a primary key, [ElementID] - Related to
Element in Elements table and a primary key, [Compliance] -
A list box with Yes, No or Irrelevant, and [Improvement
Opp] - a check box.

Elements Table - [Element ID] - autonumber, primary key.
[Element] - the actual name of the element, [CategoryID] -
related to a category in the categories table, and
[Description] - a text field describing the element.

There are a number of small tables used as lookup fields
for categories, audit types, assessor names etc.

A form/subform is used for data entry of the audit results
with the fields from Audit Details table on the mainform
and a datasheet entry form containing the fields from
Audit Results. The forms are linked by the RefNo field as
a Master/Child.

When the Improvement Opportunity checkbox is ticked a new
form opens and information detailing the improvement
opportunity is entered. Information from the Audit
Details table are copied to the Improvement Opportunity
Form and the subform is from a table called Improvement
Opportunity Table.

This is where my problem lies.

The Improvement Opportunity table consists of the
following fields:

[ImprovementID] - an autonumbering field and the primary
key. [RefNo] - linked to the RefNo from Audit Details,
[Element] - linked to the Elements Table, and [Details] -
a memo field.

I now want to introduce a field that will hold information
regarding a reference valid to the opportunity - eg. A
standard, work procedure etc. So I have created a new
table called Reference Table.

The reference would differ depending on the element that
the improvement opportunity is related to, but I want the
reference chosen from a drop down list. All very easy so
far, however when the element is selected in the
improvement opportunity subform I want the reference drop
down list to be restricted to a choice of those references
only related to that particular element.

I have tried the information provided in the MSDN
knowledge base regarding the synchronisation of two combo
boxes and all worked well in the subform by itself,
however when opened and data entered through the mainform
a message box appeared asking for the information in the
parameters to be entered again, obviously this is no good
as the whole reason for using combo boxes is to prevent
error in data entry and also to cut down on key strokes.

Hopefully all this makes some sense. Can anyone assist as
I have spent days on this with no result.

Thanks

 

[Steve Schapel]

Barb,

The query which serves as the Row Source of the Reference combobox on
your subform is, I guess, using a reference in its criteria to the
Element control. This reference to the Element control must correctly
designate the form/subform structure, i.e. it might be something like this:
[Forms]![Audit Details]![ImprovementOpportunity subform].[Form]![Element]

For more information on this, see http://support.microsoft.com/?id=209099

--
Steve Schapel, Microsoft Access MVP

I have a database that records results of compliance
audits performed on tasks performed by field staff.

The main tables are:

Tbl Audit Details: [RefNo] - an autonumbering field, and
the primary key. [AuditDate], [Assessor] which is a lookup
field related to a table containing assessor info,
[SiteTeamLeader] - another lookup field, [Work Location] -
Text, [Task] - Text, [Audit Type] - lookup, [Comments] -
Memo.

Tbl Audit Results: [RefNo] - related to RefNo in Audit
Details Table and a primary key, [ElementID] - Related to
Element in Elements table and a primary key, [Compliance] -
A list box with Yes, No or Irrelevant, and [Improvement
Opp] - a check box.

Elements Table - [Element ID] - autonumber, primary key.
[Element] - the actual name of the element, [CategoryID] -
related to a category in the categories table, and
[Description] - a text field describing the element.

There are a number of small tables used as lookup fields
for categories, audit types, assessor names etc.

A form/subform is used for data entry of the audit results
with the fields from Audit Details table on the mainform
and a datasheet entry form containing the fields from
Audit Results. The forms are linked by the RefNo field as
a Master/Child.

When the Improvement Opportunity checkbox is ticked a new
form opens and information detailing the improvement
opportunity is entered. Information from the Audit
Details table are copied to the Improvement Opportunity
Form and the subform is from a table called Improvement
Opportunity Table.

This is where my problem lies.

The Improvement Opportunity table consists of the
following fields:

[ImprovementID] - an autonumbering field and the primary
key. [RefNo] - linked to the RefNo from Audit Details,
[Element] - linked to the Elements Table, and [Details] -
a memo field.

I now want to introduce a field that will hold information
regarding a reference valid to the opportunity - eg. A
standard, work procedure etc. So I have created a new
table called Reference Table.

The reference would differ depending on the element that
the improvement opportunity is related to, but I want the
reference chosen from a drop down list. All very easy so
far, however when the element is selected in the
improvement opportunity subform I want the reference drop
down list to be restricted to a choice of those references
only related to that particular element.

I have tried the information provided in the MSDN
knowledge base regarding the synchronisation of two combo
boxes and all worked well in the subform by itself,
however when opened and data entered through the mainform
a message box appeared asking for the information in the
parameters to be entered again, obviously this is no good
as the whole reason for using combo boxes is to prevent
error in data entry and also to cut down on key strokes.

Hopefully all this makes some sense. Can anyone assist as
I have spent days on this with no result.

Thanks

 

[Barbj]

Thanks Steve,

I did as you suggested and the error disappeared.
Unfortunately, even though I set up the requery macro
after update of the element combo box, the reference field
did not limit to those related to the element. As the
fields are bound to my improvement opportunity table I
also set the requery macro to the oncurrent event in the
subform itself.

I set up a query based on the improvement opportunity
table with the parameter as described. Another problem is
that now when I open the form and select the element and a
reference (even though the list is not limited), when I
close and then reopen the form, the data in the subform is
no longer in the form view.

It has, however, been stored in the table. Any
suggestions?

Thanks again.
Barb

-----Original Message-----
Barb,

The query which serves as the Row Source of the Reference combobox on
your subform is, I guess, using a reference in its criteria to the
Element control. This reference to the Element control must correctly
designate the form/subform structure, i.e. it might be something like this:
[Forms]![Audit Details]![ImprovementOpportunity subform]. [Form]![Element]

For more information on this, see http://support.microsoft.com/?id=209099

--
Steve Schapel, Microsoft Access MVP

I have a database that records results of compliance
audits performed on tasks performed by field staff.

The main tables are:

Tbl Audit Details: [RefNo] - an autonumbering field, and
the primary key. [AuditDate], [Assessor] which is a lookup
field related to a table containing assessor info,
[SiteTeamLeader] - another lookup field, [Work Location] -
Text, [Task] - Text, [Audit Type] - lookup, [Comments] -
Memo.

Tbl Audit Results: [RefNo] - related to RefNo in Audit
Details Table and a primary key, [ElementID] - Related to
Element in Elements table and a primary key, [Compliance] -
A list box with Yes, No or Irrelevant, and [Improvement
Opp] - a check box.

Elements Table - [Element ID] - autonumber, primary key.
[Element] - the actual name of the element, [CategoryID] -
related to a category in the categories table, and
[Description] - a text field describing the element.

There are a number of small tables used as lookup fields
for categories, audit types, assessor names etc.

A form/subform is used for data entry of the audit results
with the fields from Audit Details table on the mainform
and a datasheet entry form containing the fields from
Audit Results. The forms are linked by the RefNo field as
a Master/Child.

When the Improvement Opportunity checkbox is ticked a new
form opens and information detailing the improvement
opportunity is entered. Information from the Audit
Details table are copied to the Improvement Opportunity
Form and the subform is from a table called Improvement
Opportunity Table.

This is where my problem lies.

The Improvement Opportunity table consists of the
following fields:

[ImprovementID] - an autonumbering field and the primary
key. [RefNo] - linked to the RefNo from Audit Details,
[Element] - linked to the Elements Table, and [Details] -
a memo field.

I now want to introduce a field that will hold information
regarding a reference valid to the opportunity - eg. A
standard, work procedure etc. So I have created a new
table called Reference Table.

The reference would differ depending on the element that
the improvement opportunity is related to, but I want the
reference chosen from a drop down list. All very easy so
far, however when the element is selected in the
improvement opportunity subform I want the reference drop
down list to be restricted to a choice of those references
only related to that particular element.

I have tried the information provided in the MSDN
knowledge base regarding the synchronisation of two combo
boxes and all worked well in the subform by itself,
however when opened and data entered through the mainform
a message box appeared asking for the information in the
parameters to be entered again, obviously this is no good
as the whole reason for using combo boxes is to prevent
error in data entry and also to cut down on key strokes.

Hopefully all this makes some sense. Can anyone assist as
I have spent days on this with no result.

Thanks

.

 

[Steve Schapel]

Barb,

Off the top of my head... it sounds like the subform may be going to a
new record, which could happen, for example, if its Data Entry property
has been set to Yes. Another possibility is that the Link Master Fields
and Link Child Fields properties of the subform have not been set up
correctly.