The code modifications for this stage involve adding the Delete logic, and then adding the
Event Handlers for Add, Save, and Cancel, along with the Button Display logic for Add and
Browse Mode. Finally, it's time to tie the access to the physical database file to the
project directory. This and the initial mode setup is performed in Form Load.
Delete Logic
The Delete Method is a single instruction that will remove the current row from the table. It's nice to allow the user the option of cancelling the delete and this code is provided using the Message Box function. The Delete method DOES NOT update the User Interface, so a call to MoveNext is in order. Rather that code the MoveNext directly and be forced to handle the possibility of EOF, just call the cmdMoveNext_Click Event Handler.
Private Sub cmdDelete_Click()
Dim rc As Integer
rc = MsgBox("Are you Sure?", vbYesNo + vbQuestion,
"Delete Record?")
If rc = vbNo Then
Exit Sub
End If
dtaAddress.Recordset.Delete
cmdNext_Click
End Sub
The Add Logic places the application in "Add Mode". This disables the Add, Delete, and Move buttons, and restricts the user to Save and Cancel options. AddNew blanks the bound controls.
Private Sub cmdAdd_Click()
dtaAddress.Recordset.AddNew
AddMode
End Sub
The user should now fill in the blank controls and select one of the two options available. Commit the Add by pressing Save or Cancel the Add by pressing Cancel. Pressing Save will perform a basic edit on txtName. This should be extended to each required field. The Update is performed, which will write the content of the bound controls to the table. The AddNew will place the row at the end of the table, and unless the reposition takes place, the current row (the one being displayed when Add was selected), will be redisplayed. Another oddity of this sequence is that if you code the MoveLast, the Update is not required as any Move method implicitly performs an Update.
Private Sub cmdSave_Click()
If txtName.Text = "" Then
MsgBox "At Least Enter the Name",
vbCritical + vbOKOnly, "Error Detected"
txtName.SetFocus
Exit Sub
End If
dtaAddress.Recordset.Update
dtaAddress.Recordset.MoveLast
BrowseMode
End Sub
The Cancel option will Cancel the AddProcess and reposition to the current row, that is the row that was displayed before requesting the Add.
Private Sub cmdCancel_Click()
dtaAddress.Recordset.CancelUpdate
BrowseMode
End Sub
After the Save or Cancel is complete, the application must be placed back in Browse Mode.
Some additional logic is built into this phase to locate the database in the project directory. The Form Load initialization step also places the application in Browse Mode to start.
Private Sub Form_Load()
Dim strFileSpec As String
strFileSpec = App.Path
If Right(strFileSpec, 1) <> "\" Then
strFileSpec = strFileSpec & "\"
End If
strFileSpec = strFileSpec & "Address.mdb"
dtaAddress.DatabaseName = strFileSpec
BrowseMode
End Sub
The button control is trivial. When in Add Mode, disable all, except Save and Cancel. When in Browse Mode, enable all except Save and Cancel. In the final version, the Visible property will be set rather than the Enabled property for each of the buttons.
Private Sub AddMode()
cmdSave.Enabled = True
cmdCancel.Enabled = True
cmdDelete.Enabled = False
cmdAdd.Enabled = False
cmdFirst.Enabled = False
cmdLast.Enabled = False
cmdNext.Enabled = False
cmdPrev.Enabled = False
End Sub
Private Sub BrowseMode()
cmdSave.Enabled = False
cmdCancel.Enabled = False
cmdDelete.Enabled = True
cmdAdd.Enabled = True
cmdFirst.Enabled = True
cmdLast.Enabled = True
cmdNext.Enabled = True
cmdPrev.Enabled = True
End Sub
Download the Completed Example