Form design question

[sumdumgai]

I have form that has, among other things, 15 controls that are preloaded with data that represent keys to a table. Associated with each of these controls is a data entry control. As the user enters this data and clicks an 'update' button, the associated key is used to find the corresponding record in the table and updates that record with the data that was entered.


My question is, is there anything I can do programmatically if the number of 'key' controls changes (i.e., a key/data entry combination is either deleted or added) instead of doing a form redesign?


Thanks.

 

[theDBguy]

Anytime there’s a possibility of something changing, I tend to think if the design should be changed into a table rather than a fixed format like code or form. It’s easy enough to add or delete records from a table rather than in your code or on a form. With a table, you could use a continuous form or a loop in your code.

 

[jdraw]

If I'm understanding, with 15 controls representing 15 different find and update scenarios, you may want to let user pick the find in field from a combo, then have code to do action(s) on that field/record in your table. Only one or a few such controls.

As theDBguy says, it is easier to edit a table that modify form design. It might help readers if you showed an example of your form and the logic for an update action.

You may get some ideas from this Allen Browne article.

 

[sumdumgai]

Think of this as a course/student/grade update form. When the form opens, the user enters a course number. The form is then loaded with course information coming from the course table and then it is loaded with all of the students enrolled in that course. The student list comes from a table that is updated with student names periodically. The user then has a form with all student names and they then only have to enter a grade for each student (15 of them being listed) on the form. The loop occurs after all grades have been entered, with each iteration finding the course/student record and updating the grade. This form design allows all of the students to be listed and the user to enter all grades for those students instead of requiring the user to enter both course number and student number for each update.


Thanks for your suggestions.

 

[jdraw]

It's seems to be which order is "best for the user".

Consider the timeframe semester/year has been determined regardless of which approach is used.
You could identify the Student; then all of his/her current courses; then show/enter the Grade for this Student/Course combination.
OR
You could identify the Course; then all of the Students currently in this course; then show/enter the Grade for this Course/Student combination.



My guess is it is getting the form set up with some related combos and a text box and button for example. The rowsources of the combos would be under your control.

 cboCourse    cboCourseStudent     GradeToBeAssigned  Button to update record

Just some ideas for consideration.

 

[theDBguy]

Think of this as a course/student/grade update form. When the form opens, the user enters a course number. The form is then loaded with course information coming from the course table and then it is loaded with all of the students enrolled in that course. The student list comes from a table that is updated with student names periodically. The user then has a form with all student names and they then only have to enter a grade for each student (15 of them being listed) on the form. The loop occurs after all grades have been entered, with each iteration finding the course/student record and updating the grade. This form design allows all of the students to be listed and the user to enter all grades for those students instead of requiring the user to enter both course number and student number for each update.


Thanks for your suggestions.

Hi. This makes sense. How does it apply to what you described in your original post about “15 controls being populated?” You make it sound like you’re using unbound controls rather than a continuous form to display the students.

 

[sumdumgai]

By 15 controls, I meant the fields in the form that are populated by a query. The query retrieves a student name record set from a table and populates the form fields with those names. I might be use wrong nomenclature for the form parts. So, those form fields are unbound. After the data entry are complete, an update query updates another table.

 

[theDBguy]

By 15 controls, I meant the fields in the form that are populated by a query. The query retrieves a student name record set from a table and populates the form fields with those names. I might be use wrong nomenclature for the form parts. So, those form fields are unbound. After the data entry are complete, an update query updates another table.

I guess your difficulties stem from using unbound controls and using an UPDATE query to store the data entries on the form. Whereas if you use bound controls on a continuous form, you wouldn’t have to worry if the number of students becomes less or more than 15 per subject/course.

 

[sumdumgai]

Sounds like continuous forms is what I need. Too bad I'm just getting my feet wet in Access & Forms. Are there any good examples of continuous form usage?

 

[theDBguy]

Hi. Not in front of a computer now but I’ll try to post a link later if no one does by then.

 

[isladogs]

You could have a look at my Student Target Grade Explorer example app.

HTH

 

[sumdumgai]

Thanks to everyone who contributed.