Smart Fields on a Microsoft Access Form

I set up a Microsoft Access database for students to create folder lists for collections. The only data I needed was collection number, box, folder, folder title, date, and notes. The data entry form only needed those six fields. I used unbound text boxes for the fields and in the on-click in the VBA behind the form the values of the text boxes were picked up as variables. An SQL statement then wrote those values to the table. To minimize the amount of data entry the students had to do I used sticky and smart fields on the form.


The folder number is a smart field, and the box number is sometimes a smart field. As noted above when the “Next Folder in Box” button is clicked the folder number text box is not reset to null or zero-length string It is instead set to the folder number variable value plus 1. However if the “Next Box” button is clicked the folder number text box is set to 1 and the box number text box is set to the box number variable plus 1.

What this all means is that if you have

Collection: 5544
Box: 12
Folder: 14

And click “Next Folder in Box” the form will show

Collection: 5544
Box: 12
Folder: 15

However, if you click “Next Box” the form will show

Collection: 5544
Box: 13
Folder: 1

So, once those three fields are filled in at the beginning of a data entry session they never need to be touched again for the entire session, no matter how many folders or boxes are processed.

Here is the VBA code I used:

Dim intBox as integer             ‘Variable to hold the box number.

Dim intFolder as integer          ‘Variable to hold the folder number.

intBox = Nz(Me!txtBox, 0)             ‘Pick up the box number from the form,

                                                   ‘convert to a zero if null.

intFolder = Nz(Me!txtFolder, 0)       ‘Pick up the folder number from the form,

                                                   ‘convert to a zero if null.

‘Code to write the form data to a table would go here.

‘When all tasks are done, reset the form.

‘If “Next Folder in Box” was clicked, add one to the folder number.

intFolder = intFolder + 1

‘If “Next Box” was clicked, add one to the box number

‘and reset the folder number to 1.

intBox = intBox + 1

intFolder = 1

Me!txtBox = intBox              ‘Set the box number control’s value on the

                                           ‘form to intBox.

Me!txtFolder = intFolder        ‘Set the folder number control’s value on the

                                           ‘form to intFolder.