Unbound Forms in MS Access

I have developed my own particular style of MS Access 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.

I created a table named "tblCustomers", with 5 fields: CustID (primary, autonumber), CustCompany (text), CustContact (text), CustAddress (text), and CustCity (text)
I created a form without a recordsource
I created a list box on the form, named "lstData" (on left side of form)
The source for the listbox is a query that reads ALL the data from the table, sorted by CustCompany
Set the column count for the listbox to 5
The column widths are 0;2;0;0;0 so that only the company name shows in the listbox
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
I gave their labels appropriate captions
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.
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.
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.

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.

Code for lstData (AfterUpdate event)

txtCompany = lstData.Column(1)
txtContact = lstData.Column(2)
txtAddress = lstData.Column(3)
txtCity = lstData.Column(4)
txtID = lstData.Column(0)

Code for cmdSave (OnClick event)

Dim rst As Recordset Set rst = CurrentDb.OpenRecordset("select * from tblCustomers where CustID=" & txtID)
rst.Edit
    rst!CustCompany = txtCompany
    rst!CustContact = txtContact
    rst!CustAddress = txtAddress
    rst!CustCity = txtCity
rst.Update
rst.Close
Set rst = Nothing

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

Further Suggestions:

I leave it to the reader to add some error protection.

If you would like the sample database that I created to test this series (Access 2000 version only) please visit this page. The sample database is stored in a 21K zip file, so you will need WinZip, or similar.

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.