Form not tied directly to any fields in the database Must use SQL to bind the fields 1 Form View 2 Combo Box in Form View 3 Create a blank Form 4 Form Design Combo Box 5 Name the Combo Box ID: 429571
Download Presentation The PPT/PDF document "Unbound Form" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Unbound Form
Form not tied directly to any fields in the databaseMust use SQL to “bind” the fields
1Slide2
Form
View
2Slide3
Combo Box in Form
View
3Slide4
Create a blank Form
4
Form DesignSlide5
Combo Box
5Slide6
Name the Combo Box
6Slide7
7Slide8
8Slide9
Private Sub ClientComboBox_Click
()End Sub
9Slide10
Form in Design View
10Slide11
11Slide12
Add Button
12Slide13
13Slide14
14Slide15
15Slide16
Option Compare Database
Option Explicit
Option Compare Database
Declares that string comparisons are not case sensitive
Option ExplicitUsed to require that all variables be declared before they are usedBest practice
16Slide17
Private Sub LoadClient()
End Sub
17Slide18
Dim db As
DAO.DatabaseAn object library used to work with Access databases
Dim
rs As
DAO.RecordsetDeclaring a variable named rs that stores the record set in memoryThe 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
18Slide19
On Error GoTo
LoadErrorLoadError:
MsgBox
(Err.Number) MsgBox (Err.Description)End Sub19Slide20
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").Value
rs.Close
db.Close
Exit Sub
20Slide21
Other Procedures
Add recordsChange recordsDelete recordsClear 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
21Slide22
Private Sub
ChangeCommand_Click() ChangeClientEnd Sub
Private Sub
ClearCommand_Click
() ClearClientEnd SubPrivate Sub ClientComboBox_Click()
LoadClient
End Sub
Private Sub
DeleteCommand_Click
()
DeleteClient
End
Sub
Private Sub
InsertRecord_Click
()
AddClient
End Sub
22
Best PracticeSlide23
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
23Slide24
On Error
GoTo AddError
AddError
:
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
24Slide25
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
25Slide26
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
& "';")
26Slide27
SQL: Deleting
Records
strDelete
= "DELETE * FROM Client WHERE ClientID = '" _
& Me.ClientID.Value & "';"27Slide28
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
28Slide29
Test Plan! GIGO
29