How to loop through form names and update a database where the form names are variable and without using the Evaluate() function.

Naming form elements the same as the database record element makes sense. It also helps keeps the two scopes aligned which will make troubleshooting and modifications a lot easier. In other words, FORM.Record should equal QueryName.Record. What can we do if the existence of the form elements is dependant on a query result and not always contains the exact same results when editing the represented record?

For example, there are four tables in a database (TablePeople, ListCourse, ListDuty, and BridgeTraining). The TablePeople is tracking the personnel who are accomplishing training and their duty type by ID, ListCourse is listing the courses to be accomplished, ListDuty is a listing of the different duty types in the organization, and BridgeTraining is holding the accomplished course items by ID and the personnel by ID. Each person in TablePeople will have entries in BridgeTraining, but depending on which job they hold in the organization, may have a different number of entries.

TablePeople (PeopleID, PeopleName, DutyID)
ListCourse (CourseID, CourseName)
ListDuty (DutyID, DutyName)
BridgeTraining (RecurID, PeopleID, CourseID, CourseDate)

When selecting people to update, you could just list all course possibilities in the company and cleverly disable input for those that are not available for the specific duty type or conditionally check each one before it is rendered to see if it should appear. You could also filter our those records that are not needed during the UPDATE query. I would prefer to limit the list of possible choices from ListCourse to only those that are specified as a result of ListDuty.

Assuming each CourseName was a single word (not sentence, not just a number, and not starting with a number), if you need more text to explain the course title then add an addition element to ListCourse and call it CourseTitle; we are only interested in CourseName as a name for the input into a form element. So, for brevity, a nice short course listing might look something like:

ListCourse
1 = CPR
2 = ISO9000
3 = HIPAA

When we select the first employee to edit, they only have course ID 1 and 3 required. So our form input has form names CPR and HIPPA. During the update query, we are going to loop over ListCourse and check to see if the corresponding form element exists and update that specific record.

Notice that in the StructKeyExists and in the update query I am not using the Evaluate() function. Search Google for "avoid using evaluate" for more information. This examples presumes that you have already created entries in the BridgeTraining table when you either input the new employee or you changed their DutyID.

If you find this post useful please leave a comment and let me know how you used the information.