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

Adding Data with Unbound Forms in Microsoft Access

In this, the second installment of articles dealing with unbound forms in Microsoft Access, we will look at how to add new contacts to the contact table using an unbound form. The form has the following components:

• txtname – Holds the name of the contact
• txtage – holds the age of the contact
• txtemail – holds the email address of the contact
• txtoccupation – holds the occupation of the contact
• lbaddress – holds the address of the contact

In addition there are two command buttons, one that adds a new record and another that resets or clears the Microsoft Access form fields:

First the user fills in the required data and then press the “Add Contact” button. This button then runs the following code:

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstcontact As ADODB.Recordset
Dim strCnn As String

'Check that all fields are filled in
txtname.SetFocus
If txtname.Text = "" Then
err = err + 1
MsgBox "Please fill in the name box!" & err
End If

txtage.SetFocus
If txtage.Text = "" Then
err = err + 1
MsgBox "Please fill in the age box!"
End If

txtemail.SetFocus
If txtemail.Text = "" Then
err = err + 1
MsgBox "Please fill in the email box!"
End If

txtoccupation.SetFocus
If txtoccupation.Text = "" Then
err = err + 1
    MsgBox "Please fill in occupation box!"
End If

lbaddress.SetFocus
If lbaddress.Text = "" Then
err = err + 1
MsgBox "Please fill in the address box!"
End If

'if no errors insert data
If err < 1 Then
' Open a connection.
    Set cnn1 = New ADODB.Connection
   mydb = "c:\contacts.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
    cnn1.Open strCnn

' Open contact table.
    Set rstcontact = New ADODB.Recordset
    rstcontact.CursorType = adOpenKeyset
    rstcontact.LockType = adLockOptimistic
    rstcontact.Open "contact", cnn1, , , adCmdTable


'get the new record data
rstcontact.AddNew
        rstcontact!name = txtname
        rstcontact!email = txtemail
        rstcontact!age = txtage
        rstcontact!occupation = txtoccupation
        rstcontact!address = lbaddress
        rstcontact.Update

' Show the newly added data.
        MsgBox "New contact: " & rstcontact!name & " has been successfully added"


'close connections
rstcontact.Close
    cnn1.Close


Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub 

The first part of the code declares some variables that are needed to insert data:

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstcontact As ADODB.Recordset
Dim strCnn As String

Then we do some error checking. The code checks each of the five fields to verify that it contains something. Here it checks the name text box:

'Check that all fields are filled in
txtname.SetFocus
If txtname.Text = "" Then
err = err + 1
MsgBox "Please fill in the name text box!" & err
End If

Also included in the checks is a variable that increments every time a text field turns out empty. This variable is then used to determine if a new record should be inserted or not. If all fields are filled in, the err variable will be zero:

If err < 1 Then

After the above check a connection to the database is made, then the contact table is opened up and then the user input is collected from the form fields:

'get the new record data
rstcontact.AddNew
        rstcontact!name = txtname
        rstcontact!email = txtemail
        rstcontact!age = txtage
        rstcontact!occupation = txtoccupation
        rstcontact!address = lbaddress
        rstcontact.Update

The “rstcontact.update” method then finally creates the new record that is added to the table. An appropriate confirmation message is then displayed to the user:

' Show the newly added data.
        MsgBox "New contact: " & rstcontact!name & " has been successfully added"

If there was an error detected, the message below will be shown:

Else
MsgBox "An Error has occurred, please check and try again"
End If

The other button on the form is responsible for clearing the text box fields. It uses the following code:

txtname.SetFocus
txtname = ""
txtage.SetFocus
txtage = ""
txtoccupation.SetFocus
txtoccupation.Text = ""
txtemail.SetFocus
txtemail.Text = ""
lbaddress.SetFocus
lbaddress.Text = ""

Conclusion

The code does not go far enough when doing error checking for example it does not check to see if the email address is entered in the right format, neither does it check to see if the age that is entered is a number or a letter.

In the next article we will look at how to delete and edit a record using unbound forms.

Download the database used in this article