Tuesday, January 4, 2011

Sticky 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 collection number is a sticky field, and the box number is sometimes a sticky field. After the data is entered in the form if the “Next Folder in Box” button is clicked the value of each field is picked up as a variable and written to the table. Then the text boxes on the form for collection number and box number are set to the value picked up as the variable for the field. The other text boxes, except folder number, are set to either null or zero-length string.

Here is the VBA code I used:

Dim strColl as string               ‘Variable to hold the collection number.

strColl = Nz(Me!txtColl, "")   ‘Pick up the collection number from the form, 
                                          'convert to zero length string if null.

‘Code to write the form data to a table would go here.
‘When all tasks are done, reset the form.

Me!txtColl = strColl               ‘Set the collection number control’s value on the form 
                                           'to strColl.

No comments:

Post a Comment