databasedev.co.uk - database solutions and downloads for microsoft access

Unbound Forms

Editing Data with Unbound Forms in Microsoft Access

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:

The form in design  view

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.

The Author

Leidago !Noabeb is a computer programmer based in Namibia. He has worked with both opensource and Microsoft technologies for over seven years and specializes in writing communications software. He has made many contributions to various online websites dedicated to web development. He can be reached at: leidago [at] googlemail.com