DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

Unbound Forms in MS Access - Part 4

Let's make it harder for the user to avoid the proper sequences.

Currently, a user can start to add a new record, then change their mind and switch to editing an existing record. This will knock the New Record flag out of synchronization. We could add code to keep it up to date, however, I prefer to force the proper sequence of steps.

I've set the default Enabled property of the text boxes to False. This dims them, and makes it impossible to change the contents of the text boxes.

When a user clicks on Add, the text boxes are enabled, the New Record flag is set to True, and the list box and some of the buttons are disabled. Now the user has to finish adding the new record (including the Save process) or they have to Clear the Add.

This requires us to add another new button, which I will call cmdEdit, with a caption of "&Edit". The VBA code in the button's OnClick event is shown below. This button's VBA code enables the text boxes, puts the chosen record's data in the text boxes, sets the New Record flag to false, puts the focus on the first text box, and disables the list box and several of the buttons.

There are also several additional little tips in the code segments below. I'll let you discover them on your own.

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.

(article continues after sponsor spot)



Code for cmdEdit (OnClick event)

'--- allow editing of the current record only if
'--- one is selected in the list box
If lstData.ItemsSelected.Count <> 1 Then Exit Sub

'--- enable the text boxes and the Save button
txtCompany.Enabled = True
txtContact.Enabled = True
txtAddress.Enabled = True
txtCity.Enabled = True
cmdSave.Enabled = True
'--- set the focus to the first text box
txtCompany.SetFocus
'--- disable the list box, the Add New button, the Edit button, and the Exit button
'--- must be done after the setfocus as we cannot disable something that has focus
lstData.Enabled = False
cmdAddNew.Enabled = False
cmdExit.Enabled = False
cmdEdit.Enabled = False

New version of code for cmdClear (OnClick event)

'--- clear all the text boxes and set the txtID to 0
'--- and the new flag to false
txtCompany = Null
txtContact = Null
txtAddress = Null
txtCity = Null
txtID = 0
chkNew = False
'--- enable the list box and the Add New button and the Close button
'--- must be done before moving focus to the list box
lstData.Enabled = True
cmdAddNew.Enabled = True
cmdExit.Enabled = True
'--- set the focus to the list box
lstData.SetFocus
lstData = lstData.ItemData(0)
Call lstData_AfterUpdate
'--- disable the text boxes
txtCompany.Enabled = False
txtContact.Enabled = False
txtAddress.Enabled = False
txtCity.Enabled = False
cmdSave.Enabled = False
cmdEdit.Enabled = True

New version of code for cmdSave (OnClick event)

'--- only process Save if there is data in company name
If IsNull(txtCompany) Then
     MsgBox "need a company name"
     txtCompany.SetFocus
     Exit Sub
End If

Dim rst As Recordset
'--- use the primary key (always an autonumber field) to find the record
'--- if it is a new record, this will find no records, as txtID will be 0
Set rst = CurrentDb.OpenRecordset("select * from tblCustomers " & _     "where CustID=" & txtID)
'--- do we add a new record or save an existing one
If chkNew = True Then
     rst.AddNew
Else
     rst.Edit
End If
'--- transfer data from text boxes to table fields
rst!CustCompany = txtCompany
rst!CustContact = txtContact
rst!CustAddress = txtAddress
rst!CustCity = txtCity
rst.Update '--- save the record
rst.Close '--- close the recordset
Set rst = Nothing '--- reclaim the memory the recordset was using
chkNew = False '--- reset the new flag
'--- enable the list box and the Add New button and the Close button
'--- must be done before moving focus to the list box
lstData.Enabled = True
cmdAddNew.Enabled = True
cmdExit.Enabled = True
'--- make sure the newest data is in the list box
lstData.Requery
'--- set the focus to the list box
lstData.SetFocus
lstData = lstData.ItemData(0)
Call lstData_AfterUpdate
'--- disable the text boxes and the Save button, and make Edit button enabled
txtCompany.Enabled = False
txtContact.Enabled = False
txtAddress.Enabled = False
txtCity.Enabled = False
cmdSave.Enabled = False
cmdEdit.Enabled = True

Code for Form_Open event    this code shows how to force the list box to select the first item in the list.

lstData.SetFocus
lstData = lstData.ItemData(0)
Call lstData_AfterUpdate

Code for the Double Click on the List Box

Private Sub lstData_DblClick(Cancel As Integer)

      '--- a double click on the list box is the same as
      '--- clicking on the Edit button
      Call cmdEdit_Click

End Sub

Further Suggestions:

There is a lot of duplicated code for enabling/disabling, etc. You could create some common routines to centralize this code. Maybe have a parameter that determines what gets set. There is probably also a lot of potential for cleaning up the code, and making it more efficient.

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.
 

 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy