select records in form and subform

[Davide]

Dear all,
in the attached database, an analysis is made of several tests. Each test measures a parameter using one or more (usually 2 or 3) methods.


I have been able to create the Test form, in which the user can attach all the parameters (from master data), with the relative methods (again, master data), to an analysis.
Still, I cannot select only the tests which belongs to one analysis from the combobox. I would like the user to see only the records which belongs to that analysis. Suggestions? thanks a lot.

[Micron]

Don't really understand what the business process is but I think there are some design errors here. I interpret your post as

For 1 analysis you perform many tests by measuring one parameter per test (e.g. width) using multiple methods (e.g. use tape measure + use micrometer).
In that case, you have 1 analysis based on many tests based on one parameter using multiple methods (1 to 1 to 1 to many). Your relationships allow for many parameters for one test. Also, it seems that parameters ought to be linked to methods. Not seeing the need for the junction table in that case.

Your design is very difficult to follow (at least for me) because you have all these ID <> fieldName links that are confusing when you don't know the business. Better to adopt a naming convention like ParamID_PK linked to ParamID_FK or ParamID linked to ParamIDfk if you want to default to PK when not in the name. Also, I'd base forms on queries, not tables for multiple reasons, one being it's so much easier to filter records as you are asking for. Here, you're loading the whole table.Then there is the combo columns - user is picking one parameter and seeing the same description. When looking at Alluminio in the combo, how do I know which of the 4 I'm looking at? You should show UM as well?

Very important: don't bind combos to fields when using them for searching/filtering. You might think you're just filtering by making selections when in fact you are altering the underlying data.

If you believe your design is correct then try linking the subform Child and main form fields to something other than what you have. Maybe Parameters?

[Davide]

Hello Micron
thanks for your reply and sorry for the confusion. An analysis is made of some test. Each test associates one outcome to one parameter. For example, an analysis may test Ph, cadmium and cobalt (master data, already present in the parameter table). The measure (outcome) is obtained using different methods. For example, to measure cadmium you may need to adopt methods A and B and so on. So far, I think the design is correct. The users will first create the analysis, then add all the parameters which need to be tested and the methods to test them. Then , they will add the outcome, after measuring it physically:

1 create analysis
2 open the form, select the existing analysis, and add the related parameters which need to be tested
3 add the methods to each test
4 perform test and record results (outcomes), one per test

This last step will be probably done in another form, but may be done in the same. Thanks again for your suggestions.

[Davide]

Hello again
I attach a new version of the DB which may clarify my problem. In form Test1 I would like to see only the tests which belong to the one analysis selected in the combo at the top of the form. So, after the form opens, the user should select one analysis from the combo, and in the form below see the relative tests. If there already are 2 or 3, then he should be able to add the next. If there is none, he should be able to add some.

[Gicu]

Micron:

Very important: don't bind combos to fields when using them for searching/filtering. You might think you're just filtering by making selections when in fact you are altering the underlying data.

Please review the updated file. Again, the "searching" combos cannot be bound to a field.

Cheers,

[mike60smart]

Hi Davide

I went and normalised your tables to deal with Parameters & UM's

When you open the database "frmAnalysis" will open by default.

This allows you to enter details of an Analysis.
Then in the Subform for selecting Parameters - now when you select a Parameter using the 1st Combobox, the 2nd Combobox will only display those UM details specific to the Parameter selected.

There is a "frmParameters" which allows you to add/edit current Parameters & UM Details.

[ArviLaanemets]

On fly!

Change the table Parameter to like tblParameterSelections: ParameterSelctionID, ParametertID, UM (or ParameterSelectionID, ParameterID, UM, [Parameter]MethodID, in case you go for 3b - see about it later);
In form Test:
1. Make changes to control cboParameter: base RowSource on table tblParameterSelections instead of tblParameter, and add a column for UM (like "SELECT par.ParameterID, par.UM, par.Description & ":" & par.UM FORM tblParameterSelections par ORDER BY 3"); Set ColumnCnt = 3; SetColumnWidths = "0;0;2.5"
2. Instead control cboUM use unbound text box, which reads UM value from 2nd field of cboParameter (Or uses DLookup to read it from table tblParameterSelections in case you didn't add 3rd field to combo);
3. Depending on any test method applying to any parameter, or to specific ones only, and on your preferences:
3a) Create a table where valid test methods for every parameter are listed (like tblParameterMethods: ParameterMetmethodID, ParameterID, MethodID). Add a field for ParameterMetmethodID into table Test. Add a combo for ParMetID into form Test, which displays selections depending on selected parameter, and is disabled when no parameter is selected;
3b) Expand your tblParameterSelections table so it contains all valid permutations of ParameterID, UM, and ParameterMethodID. Add a column for ParameterMethodID into RowSource of cboParameter in test form, change the column count to 4 and edit ColumnWidths accordingly, and add description from table Method into field displayed. Add an unbound combo box into test form, which is set equal to ParameterMethodID of cboParameter, and where the description of used method is displayed.

Delete form Scottmaschera TestMethod1!

[Davide]

Hello Mike
thanks a lot for the great work! I did not normalized units of measures, you are right. Only, I would like to be able to select one analysis and the attached tests (if any) and not show all of them in the form. How would you do that?
I tried creating a query and then a form based on the query, but something doesnt work.
Also, I do not think I can reproduce everything you did, for some reasons. How did you bind the sub-sub mask to the sub mask?
Thanks again

[mike60smart]

Hi Davide

Is the attached what you need?

[Davide]

Dea all
I think I have been able to achieve what I wanted (thanks to your suggestions). I have not addressed the UM normalization yet, as correctly suggested by some. Anyway, I attach the database here, for the record. You can select one of the previously created analyses, and then link all the required parameters to test.
Still, my form does not look as good as Mike's: any suggestion on how to improve it?
I like the fact that it shows a methods sub-form per test, but I think it would be better to see one test/parameter at a time in the first sub-form (with the related methods below, as in Mike's). Users should have the possibility to navigate back and forth among the tests of the selected analysis, and add methods as appropriate.
Also, it would be nice the UM beside the parameter in the subform (as Mike did). I can only see it in the combo, but not after parameter selection.
Thanks again,

[mike60smart]

Hi Davide

Is this what you want?LabDB 6.zip