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.