the Manx Man's MS Access Pages

need professional help with a database?
click here

Unbound Forms - Part 1 (030)

I have seen a number of postings in the newsgroups lately, with questions about unbound forms. How do they work? Why use them?

I am not going to open a can of worms and answer the Why!!!

I  am   going to write a series of articles about the how. At least, the way I see it.

I have developed my own particular style of unbound forms. I like them because them give me TOTAL control over a process and make it impossible for the user to "accidentally" mess up data. In order to replace data or add new data the user MUST PURPOSELY click a button called SAVE. I realize that bound forms can be protected with various events. Just remember that I am not going to go into the details of  why!

Here is an example of my way of creating unbound forms.

1. I created a table named "tblCustomers", with 5 fields: CustID (primary, autonumber), CustCompany (text), CustContact (text), CustAddress (text), and CustCity (text)
2. I created a form without a recordsource
3. I created a list box on the form, named "lstData" (on left side of form)
4. The source for the listbox is a query that reads ALL the data from the table, sorted by CustCompany
5. Set the column count for the listbox to 5
6. The column widths are 0;2;0;0;0 so that only the company name shows in the listbox
7. I created 5 unbound textboxes on the right of the form, one for each of the 5 fields from the query.  I gave them the following names; txtID, txtCompany, txtContact, txtAddress, txtCity
8. I gave their labels appropriate captions
9. I made the txtID and its label invisible.  An autonumber field should never be used by the user.  It is just there because it makes things easier for the programmer to create relationships, and, in my case, to make it easier to find records in the table.
10. Now the fun begins.  How do we get the data, from the records that show up in the listbox, over into the text boxes?  I use the AfterUpdate event of the listbox.   (if you do not have experience creating VBA events, please study them before continuing. see the books I recommend here)   See the code below.
11. Finally, I add a button to the form to be used to save changes (called cmdSave, and with an appropriate caption) and put VBA in its OnClick event.  See the code below.

All the code segments assume that you have DAO references active.  If you are not sure what this means, and you are using Access 2000 or higher, click here.

Code for lstData (AfterUpdate event)

Code for cmdSave (OnClick event)

How it Works

When you click on an entry in the listbox, the afterupdate event fires and the data from the listbox gets copied into the textboxes.Notice the use of the column(n) property of the listbox.

If you change the data in one of the textboxes, you need to click the Save button. When you do, the OnClick event fires and I use recordset methods to find and update the record in the table.

What's next? Part 2 of this article will add an Add New button. This adds a bit of a challenge. How does the Save button know whether it is an updated record (needing rst.Edit) or a new record (needing rst.AddNew)?

If you would like the sample database that I created to test this series of articles (zipped Access 2000 version only) please click here and scroll to the bottom of the page you are taken to on my new site.

NOTE:  At this point there is no error protection.  I leave it to the reader to add some.