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:
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.