Looping Through Fields In Unbound Forms

DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News

Tables

Queries

Forms

Reports

Modules

Miscellaneous

Subscribe

Free Samples

Videos

Services

Links

About Us

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.

NOTES:

A pre-requisite to this article is my series on using unbound forms. That series starts at this link.

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!)

'--- READING DATA
Set rst = CurrentDb.OpenRecordset("select * from tblCustomers where CustID=" & txtID)
'--- transfer data from table fields to text boxes
me.txtCompany = rst!CustCompany
me.txtContact = rst!CustContact
me.txtAddress = rst!CustAddress
me.txtCity = rst!CustCity
rst.Close '--- close the recordset
Set rst = Nothing '--- reclaim the memory the recordset was using

'--- WRITING DATA
Set rst = CurrentDb.OpenRecordset("select * from tblCustomers " & _
   "where CustID=" & txtID)
'--- do we add a new record or save an existing one
If rst.eof and rst.bof Then
   rst.AddNew
Else
   rst.Edit
End If
'--- transfer data from text boxes to table fields
rst!CustCompany = me.txtCompany
rst!CustContact = me.txtContact
rst!CustAddress = me.txtAddress
rst!CustCity = me.txtCity
rst.Update '--- save the record
rst.Close '--- close the recordset
Set rst = Nothing '--- reclaim the memory the recordset was using

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.

'--- READING DATA
Set rst = CurrentDb.OpenRecordset("select * from tblCustomers " & _
   "where CustID=" & txtID)
'--- transfer data from table fields to text boxes
dim fld as Field
for each fld in rst.Fields
   me(fld.name) = rst(fld.name)
next fld
rst.Close '--- close the recordset
Set rst = Nothing '--- reclaim the memory the recordset was using

'--- WRITING DATA
Set rst = CurrentDb.OpenRecordset("select * from tblCustomers " & _
   "where CustID=" & txtID)
'--- do we add a new record or save an existing one
If rst.eof and rst.bof Then
   rst.AddNew
Else
   rst.Edit
End If
'--- transfer data from text boxes to table fields
dim fld as Field
for each fld in rst.Fields
   rst(fld.name) = me(fld.name)
next fld
rst.Update '--- save the record
rst.Close '--- close the recordset
Set rst = Nothing '--- reclaim the memory the recordset was using

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.

me(fieldname)
gives you the value of a textbox or combobox or listbox, etc, on a form

rst(fieldname)
gives the value from a field in a record from a table

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.

Happy Coding

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.