Limiting Changes to Certain Fields in a Combination Form

[rjgriffin46]

Continuing with my Student Administration base:

I have a 2 part form "Tests and Grades" that I created by dragging a "Test Parameters" table on the top and a "Test and Grades" table on the bottom, during which there was a parent to child link made. The attachment is a screen capture of the form.

The Test Parameters table contains information about each test for the class (eg., title, date, # of questions). It is linked to a Class table from which it identifies via lookup the class number (eg., 2014-1 - There are 3 classes run per year, so you would have 2014-1, 2014-2, 2014-3).

The Test and Grades table, in datasheet format, is where students are selected (from the Student table) and the number of incorrect answers entered. The Students grade for the test is then calculated by expressions in other fields in the table. So you end up, after entering student incorrect answers for a given test, with one Test Parameter record on the top and 50 or so rows of student Tests and Grades results records.

Below are the table relationships:

One	Key Field	Many	Via Field
Class	Class Number	Test Parameters	Class Number
		Students	Class Number
Test Parameters	Test Number(1)	Tests and Grades	StudTestRecord#
Tests and Grades	StudTestRecord#(2)	Students	ID (Student Number)

1. This is a Key field, but not auto number.
2. This is not indicated as a key or auto number field in Design view, but Access assigns a next sequential number when a new record is created.




There is a filter on the entire form: (([Test Parameters].[Class Number] Is Not Null AND [Test Parameters].[Class Number]<>"")). I may have created this filter, however, and the expression builder for this property is grayed outScreen Capture of Test and Grades From for Forum.zip.

Currently on the Tests and Grades form, the Class Number, taken from the Class table, is on the header. If I change the number, I would like the Test Parameter records to reflect those of the selected class. Additionally, I would like only the Students for that class to appear on the drop down list. As it is now, changing the Class Number in the header seems to have no effect - I still see the Test Parameter records for whatever class came up when the form opened & I have to scroll (or go to the last record) to get to the current class' tests. Further, if I have the class number field in the Test Parameters section, I could (accidentally) change it.

So:
1. What is the best way to filter using the Class number on the header of the form to limit the Test Parameter records, Tests and Grades records and Student lookups to those with the Class number entered on the header? Can this be done with filtering or does it require validation expressions? Do I need to write Macro or VBA code?

2. How could I open the form with the current Class as a default (eg., could I modify the form filter indicated above, free form in the Property field)?

Answers and other recommendations are greatly appreciated as always.

[June7]

If you don't want to use the intrinsic Access filter/search utilities, will need code (macro or VBA).

One method to filter is dynamic parameterized query. Review: http://www.datapigtechnologies.com/f...tomfilter.html

Another is VBA to set form Filter and FilterOn properties.

Opening form to a default filter criteria can be done by setting the form Filter and FilterOnLoad properties or with WHERE CONDITION argument of DoCmd.OpenForm.

Controls used to input filter criteria must be UNBOUND.

Advise not to use spaces and special characters/punctuation in naming convention (underscore is exception).

[rjgriffin46]

Thanks for your suggestions which I hope to try today.

[rjgriffin46]

Screen Capture of Test and Grades From for Forum (2).zip
Screen Capture of Test and Grades From for Forum.zip
I added the dynamic parameterized query following http://www.datapigtechnologies.com/f...tomfilter.html. Of course, I did not get the desired results. I included 2 columns, Class Number and TestSequenceNo, sorting in the query by Class Number Descending (so the most recent class is at the top of the list) and TestSequenceNo Ascending.

I have 2 spreadsheets with additional screen shots but I'm not sure I attached.

The form always opens displaying the first Test Parameter record which is Class 2014-1, Test Sequence # 1 and the corresponding Tests and Grades records with the student results in the sub-form. The control (myclasstestselector) does display a drop down list of Class and Test Sequence numbers, which is fine. However, when one is selected, nothing happens - you still see the first record. The exception is if you have advanced the bottom most record control beyond the first record, selecting anything from the drop down returns you to the first record.

I have only 16 test Parameter records so far, 14 for class 2014-1 and 2 for class 2014-2. Sometimes the control drop down only shows the 2014-1 data. Usually then if I close and re-open the form I will see both. Any reason why that should happen?

I did not create the reset button yet.

I notice that I can type the desired class (without the hyphen) in the little Search box on the record control row at the bottom and that takes me to the 1st test record for that class. From there, users can advance forward or backward to the test they want (there's only 15 for now). While not ideal, it might be workable enough...

Any further suggestions?

The other thing I wished to do is prevent update of the Class Number on the Test Parameter record. I tried to set the Enabled property from Yes to No with no luck and then the Locked property from No to Yes, also with no luck. In fact, either or both changes made the form dysfunctional in different ways. I tried writing a validation expression but entering a student with a different class caused no reaction.

[June7]

Post the SQL of the parameterized query. You are trying to select Class and TestSequenceNo combination with the combobox? Don't think that will work. Query can only pull one value from the combobox, can't reference combobox columns in query.

Don't know why the combobox sometimes shows only 2014-1 data. Why is this value with hyphen on form but not in combobox?

You don't want to allow changing the Class where - on the main form combobox or on the subform?

If you want to provide db for analysis, follow instructions at bottom of my post.

[rjgriffin46]

1. Here is the SQL for the parameterized query:

SELECT [Test Parameters].[Class Number], [Test Parameters].TestSequenceNo

FROM Class INNER JOIN [Test Parameters] ON (Class.[Class Number] = [Test Parameters].[Class Number]) AND (Class.[Class Number] = [Test Parameters].[Class Number])

WHERE ((([Test Parameters].[Class Number]) Like [forms]![Tests and Grades].[myclasstestselector] & "*"))

ORDER BY [Test Parameters].[Class Number] DESC , [Test Parameters].TestSequenceNo;

2. "Don't know why the combobox sometimes shows only 2014-1 data. Why is this value with hyphen on form but not in combobox?"

For better or worse, I created a mask for this field (Class Number) hoping that people wouldn't have to type the dash. I eventually tried to set it so that the dash is stored, but not successfully as it seems as there are situations like you see in which the dash does not appear. While annoying, its not a show stopper.

3. "You don't want to allow changing the Class where - on the main form combobox or on the subform?"

On both the form and subform. On the form, the Class is associated with the test parameter record. I think on the form I can just change Enabled to No.

On the sub-form, the Class is associated with the Student and I only want to select a student who is in the Class. I think I can add Class as a 2nd column in the lookup so at least you would see before selecting if you have the right student.

(Additionally, I should not allow more than 1 record to be created per student-but I can deal with that later.)

4. Database Analysis

I can't get the database below 2.585 compressed. Could I email or otherwise send outside of the forum?

Thanks so much for your interest!

[June7]

1. there is only one parameter in the query, do you need to also filter for TestSequenceNo?

2. changing the mask to save the hyphen will not affect existing data, would have to do an edit on those to add the hyphen

3. setting Enable property should work

4. Did you try deleting data? Only need a few records for testing. Can upload to a fileshare site such as Box.com and post link to file.

[rjgriffin46]

My comments in green:

1. there is only one parameter in the query, do you need to also filter for TestSequenceNo?
I only need to sort on TestSequenceNo, not filter.

2. changing the mask to save the hyphen will not affect existing data, would have to do an edit on those to add the hyphen
OK

3. setting Enable property should work
I thought Enable just toggled the updatability of a field on & off...? When selecting students to record their score on a test, I only want to see and be able to select those in the same Class as the Test Parameter records. I didn't get a chance yet to modify the lookup columns when selecting the student, but that should be easy enough so you can a least see the student's class number before you pick a student. As it is now, as soon as you select a student the Class number displays so you'd know right away if you picked the right person. Don't forget, the people doing this will know the students well as there are only about 60 or so at the start of each class.

4. Did you try deleting data? Only need a few records for testing. Can upload to a fileshare site such as Box.com and post link to file.

Here is a link to the file on Box: https://app.box.com/s/2acpuv75c16dfycmr9nm . Its the un-zipped file. All the data is made up test data.

I have it set to open the form in question (Tests and Grades).

Again, I really appreciate your interest and time. Don't hesitate to let me know when you've had enough!

[June7]

There are two comboboxes on the main form header. One is bound to Class Number the other (myclasstestselector) is not a bound control.

Keep in mind that controls used to enter filter criteria must be UNBOUND. So what is purpose of the Class Number combobox?

The myclasstestselector combobox is referencing itself as filter criteria in its own RowSource. That makes no sense. Did you mean to have it reference the Class Number combobox so the Test Sequence numbers will be restricted?

[rjgriffin46]

Sorry I have been unable to work on this. I will try your suggestions and let you know the results.

The Class Number combobox was an attempt to limit the records to those of the class.

I tried to follow the video instructions in creating the myclasstestselector combobox, but seems I erred. What should it reference to function essentially as a filter to select & limit to the desired class?

[June7]

If you want Test Sequence numbers limited to a selected Class then myclasstestselector combobox must reference Class combobox for filter criteria. The Class combobox should be UNBOUND for use in selecting class as a filter parameter, otherwise you change the data in the record.