1

Unbound Form

• Form not tied directly to any fields in the database

• Must use SQL to “bind” the fields

2

Form View

3

Combo Box in Form View

4

Create a blank Form

Form Design

5

Combo Box

6

Name the Combo Box

7

8

9

Private Sub ClientComboBox_Click()

End Sub

10

Form in Design View

11

12

Add Button

13

14

15

16

Option Compare DatabaseOption Explicit

• Option Compare Database– Declares that string comparisons are not case

sensitive• Option Explicit– Used to require that all variables be declared

before they are used

Best practice

17

Private Sub LoadClient()

End Sub

18

Dim db As DAO.Database• An object library used to work with Access databases

Dim rs As DAO.Recordset• Declaring a variable named rs that stores the record set

in memory• The record set contains the records you extracted by

running a query or using a Access table

Dim strQuery As String• Creates an area in memory to store information

– This variable will be used to store the query text you create

Set db = CurrentDb• Specifies what database to use in the queries

– In this case it is the current database

19

On Error GoTo LoadError

LoadError: MsgBox (Err.Number) MsgBox (Err.Description)End Sub

20

strQuery = "SELECT * FROM Client WHERE ClientID = '" & Me.ClientComboBox.Value & "';"Set rs = db.OpenRecordset(strQuery, dbOpenSnapshot) Me.ClientID = rs.Fields("ClientID").Value Me.FirstName = rs.Fields("FirstName").Value Me.LastName = rs.Fields("LastName").Value Me.Address = rs.Fields("Address").Value Me.City = rs.Fields("City").Value Me.State = rs.Fields("State").Value Me.ZipCode = rs.Fields("Zipcode").Value Me.HomePhone = rs.Fields("HomePhone").Valuers.Closedb.CloseExit Sub

21

Other Procedures

• Add records• Change records• Delete records• Clear all the controls on the form so you can

add a record

• These will all be attached to the appropriate command button and will have an [Event Procedure] in the On click property

22

Private Sub ChangeCommand_Click() ChangeClientEnd Sub

Private Sub ClearCommand_Click() ClearClientEnd Sub

Private Sub ClientComboBox_Click() LoadClientEnd Sub

Private Sub DeleteCommand_Click() DeleteClientEnd Sub

Private Sub InsertRecord_Click() AddClientEnd Sub

Best Practice

23

All Procedures accessing the Database will have the following Code

Dim db As DAO.Database Set db = CurrentDb Dim rs As DAO.Recordset Dim strQuery As StringSet db = CurrentDb

SQL statement and any other program code rs.Close db.Close

24

On Error GoTo AddErrorAddError: Select Case Err.Number Case 3464 MsgBox ("Client ID Cannot be Blank") Case 3315 MsgBox ("Client ID Cannot be Blank") Case 3022 MsgBox ("Client ID Must be Unique") Case Else MsgBox (Err.Number) MsgBox (Err.Description) End Select

25

Private Sub AddClient() Dim db As DAO.Database Set db = CurrentDb Dim strInsert As String On Error GoTo AddError strInsert = "INSERT INTO Client(ClientID, FirstName, LastName,Address, State, City, ZipCode, HomePhone) " & _ " VALUES('" & Me.ClientID & "','" & Me.FirstName & "','" & Me.LastName & _ "','" & Me.Address & "','" & Me.City & "','" & Me.State & _ "','" & Me.ZipCode & "','" & Me.HomePhone & "');"

CurrentDb.Execute strInsert, dbFailOnError Me.ClientComboBox.Value = Me.ClientID.Value Me.ClientComboBox.Requery

26

SQL: Changing (Updating) Records

strUpdate = ("UPDATE Client SET " & _ "ClientID = '" & Me.ClientID & _ "', FirstName ='" & Me.FirstName & _ "', LastName ='" & Me.LastName & _ "', Address ='" & Me.Address & _ "', City ='" & Me.City & _ "', State ='" & Me.State & _ "', ZipCode ='" & Me.ZipCode & _ "', HomePhone ='" & Me.HomePhone & _ "' WHERE ClientID = '" & Me.ClientComboBox.Value & "';")

27

SQL: Deleting Records

strDelete = "DELETE * FROM Client WHERE ClientID = '" _ & Me.ClientID.Value & "';"

28

Execute this Procedure before Adding a Record

Private Sub ClearClient() Me.ClientComboBox = "" Me.ClientID = "" Me.FirstName = "" Me.LastName = "" Me.Address = "" Me.City = "" Me.State = "" Me.ZipCode = "" Me.HomePhone = ""End Sub

29

Test Plan! GIGO