An attempt at explaining my database design is below, but essentially my question is:
I have a combo box on a form that filters a subform. It works beautifully, but it changes a record in a related table to whatever the last chose in the combo box was. It's always the same record that it modifies; it always inserts the last choose of the combo box.
I'd like to understand why it happens; how I can prevent it; and if I can't how I can force it to reset.
I'm going to try very hard to explain my database so it makes sense; just not sure that's entirely possible.
I have four tables
tblEnvElements - a list of 15 environment elements (vegetation, fauna, water, etc).
tblAssessIssues - a list of 50+ assessment questions. It has a field "EnvElement" that looks up tblEnvElements.
tblProjects - a list of 200+ projects to be assessed
tblAssessProjects - uses an append query to combine tblAssessIssues and tblProjects, giving me 50+ assessment questions for each of the 200+ projects.
I then have a form (frmAssessment) with a subform (frmAssessmentSUB).
frmAssessment data comes from a query based on the four tables (to give me words instead of ID numbers).
frmAssessmentSUB data comes from a similar query.
The form opens with code, filtering to a single project. I have a combo box (data from tblEnvElements) for environmental element on the main form, linked to the subform. That is, the form opens to a project; you choose an element (say water) on the combobox; the sub-form then shows the assessment questions for water for that project. Choose a different element, different set of question in the subform.
(Note; I'm not a programmer; half the time I haven't a clue what I'm doing; so I'm sure there's a better way to do this - but it is what it is.)
I have a combo box on a form that filters a subform. It works beautifully, but it changes a record in a related table to whatever the last chose in the combo box was. It's always the same record that it modifies; it always inserts the last choose of the combo box.
I'd like to understand why it happens; how I can prevent it; and if I can't how I can force it to reset.
I'm going to try very hard to explain my database so it makes sense; just not sure that's entirely possible.
I have four tables
tblEnvElements - a list of 15 environment elements (vegetation, fauna, water, etc).
tblAssessIssues - a list of 50+ assessment questions. It has a field "EnvElement" that looks up tblEnvElements.
tblProjects - a list of 200+ projects to be assessed
tblAssessProjects - uses an append query to combine tblAssessIssues and tblProjects, giving me 50+ assessment questions for each of the 200+ projects.
I then have a form (frmAssessment) with a subform (frmAssessmentSUB).
frmAssessment data comes from a query based on the four tables (to give me words instead of ID numbers).
frmAssessmentSUB data comes from a similar query.
The form opens with code, filtering to a single project. I have a combo box (data from tblEnvElements) for environmental element on the main form, linked to the subform. That is, the form opens to a project; you choose an element (say water) on the combobox; the sub-form then shows the assessment questions for water for that project. Choose a different element, different set of question in the subform.
(Note; I'm not a programmer; half the time I haven't a clue what I'm doing; so I'm sure there's a better way to do this - but it is what it is.)
