'Serving the Microsoft® Access
Looping Through Fields In Unbound Forms
I was helping a customer make some changes to a system. They wanted to add several new tables, including one with over 50 fields. The data was such that they needed a single form to display all the data on one screen. It fit okay, but if I am going to use an unbound form, that means coding 100 assignment lines of code, 50 for reading the data, and 50 for writing. Yuck!
(article continues after sponsor spot)
NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.
The first step I took was to create a blank form of the same style (background colour, etc) as the other forms in the system. I just copied one of the others and deleted most of the objects from it.
Next, I bound the form to the table, and using the Field List that is available in a form's design view, copied all the fields to the blank form. I know, that makes it bound. (another method would have been to start with the table and use the New Object: Autoform toolbar item)
How do we change them all to unbound in one quick action? The method I use is to select all the fields, bring up the property sheet, type an "x" in the control source, press down arrow, then go back up and delete the "x". That works, but if you know a cleaner method, let me know.
Finally, in the property sheet for the form, delete the table in the RecordSource.
Now that we have an unbound form, how do we code the read/writes without having to type the 100 lines of code?
Easy; and I'll demonstrate using some VBA.
First, a reminder of how line-by-line reads and writes work. (these examples only show 4 fields; remember, we are about to deal with a 50 field table!)
Now, let's do this the easy way.
The pre-requisite to this method is that the text boxes have to have the EXACT same names as the fields. If you used the method that I used for creating the unbound fields, this will be true.
If you have never seen this way of coding for fields, you are probably as excited as I was when I first discovered it. This is how you can store a field name in a variable and still get at its data.
You can also do things like this (from a project I am working on as I write this article)
me("a" & format(iX,"00") & format(iY,"00")
This allows me to access fields with names like "a0614" from a form. In my newer project, I have a grid of size 15 x 15 on the form, so a0614 is the cell in the 6th column, 14th row. See the article Referring to Fields and Form Objects for more details.
You should also read Looping Through Form Objects Using the Tag Property.