The previous two installments dealt with how to view data with unbound forms and how to add records to a table using unbound forms. In this final installment dealing with unbound forms, we are going to look at how to edit or change and delete a record. I’ve modified the contact view form a little so that it looks something like this:
As you can see I’ve only added two buttons to the form. These buttons are to delete and update records. Because we are using a list box to view the records contained in the table, we have to update it in real-time when deleting a record. Here’s the code that is responsible for implementing this algorithm:
Private Sub btndelete_Click() Dim dbs As DAO.Database, sql As String, rCount As Integer Set dbs = CurrentDb If recid.Caption = "" Then MsgBox "Please select a contact to delete." Else sql = "DELETE * FROM contact WHERE cid=" & recid.Caption dbs.Execute sql, dbFailOnError rCount = dbs.RecordsAffected If rCount > 0 Then MsgBox "Contact has been deleted" 'update listbox conInfo.Requery 'reset textfields txtname.SetFocus txtname.Text = "" txtage.SetFocus txtage.Text = "" txtoccupation.SetFocus txtoccupation.Text = "" txtemail.SetFocus txtemail.Text = "" lbaddress.SetFocus lbaddress.Text = "" recid.Caption = "" End If End If End Sub
So, what does this code mean? First the current database is set
Set dbs = CurrentDb
Then it checks to see if the user has selected a record for deletion:
If recid.Caption = "" Then MsgBox "Please select a contact to delete." Else
If the user did not select a record then they get a message saying "Please select a contact to delete". The code then stops executing:
If the user has selected a contact to delete then the code defines a SQL statement that includes the ID of the contact (stored in the recid label of the form):
sql = "DELETE * FROM contact WHERE cid=" & recid.Caption
Now we use the SQL statement to remove the record from the table:
dbs.Execute sql, dbFailOnError
To verify that the record has actually been removed, we simply check the recordsaffected property of the database object which returns the number of records that have been affected by the query.
rCount = dbs.RecordsAffected
If the affected number of records is greater then zero then we know that the record has been deleted so, we show a confirmation message to the user stating exactly that:
If rCount > 0 Then MsgBox "Contact has been deleted"
Finally we update the listbox to reflect the change and also clear all the text fields:
'update listbox conInfo.Requery 'reset textfields txtname.SetFocus txtname.Text = "" txtage.SetFocus txtage.Text = "" txtoccupation.SetFocus txtoccupation.Text = "" txtemail.SetFocus txtemail.Text = "" lbaddress.SetFocus lbaddress.Text = "" recid.Caption = ""
To update a record is even simpler, the user simply selects the record that needs to be updated and then make the changes that they want to make. Then click on the update button that has the following code:
Private Sub Command11_Click() Dim dbs As DAO.Database, sql As String, rCount As Integer Set dbs = CurrentDb sql = "UPDATE contact SET name=" & txtname.Text _ & ",address=" & lbaddress.Text & ",email=" & txtemail.Text _ & ",occupation=" & txtoccupation.Text _ & ",age=" & txtage.Text & "WHERE cid=" & recid.Caption dbs.Execute sql, dbFailOnError rCount = dbs.RecordsAffected If rCount > 0 Then MsgBox "Contact updated" 'update listbox conInfo.Requery End If
Once the user clicks on the update button, the SQL is run. This SQL statement simply matches the table field names to the fieldnames of the form and then transfers the data contained in the form fieldnames to the table:
sql = "UPDATE contact SET name=" & txtname.Text _ & ",address=" & lbaddress.Text & ",email=" & txtemail.Text _ & ",occupation=" & txtoccupation.Text _ & ",age=" & txtage.Text & "WHERE cid=" & recid.Caption
The rest of the code simply shows a confirmation message to the user and then updates the listbox.