Transcript Systems Development Group Project

Systems Development
Group Project
Programming Access Forms
Lab Exercise from Appendix 9
Use this to populate
the list box on the pet
form later.
PET
m
has
1
OWNER
Access Forms
1. Bound Forms
2. Unbound Forms
e.g. a Menu
Forms are often a mixture of
bound and unbound
Access Forms
Form Detail
Right click here to see form
properties
Some useful form
properties
Access Forms
Control Box Property to turn these on or off
Record Selectors property to turn these on or off
Min Max Property to turn
these on or off
Plus various event properties – the LOAD event is especially
important
Access Forms
 Naming the controls on your forms
Form names start with frm e.g. frmPet, frmMenu
 Text boxes should start with txt e.g. txtPetNo
 Combo boxes with cmb e.g. cmbOwner
 List boxes with lst e.g. lstPetType
 Button with cmd e.g. cmdExit
 Naming is VERY important and it’s better not to include
spaces in the names.
 Naming should always be the FIRST thing you do with a new
form or control
 Don’t confuse the caption property with the name property.
Caption for a command button (for example) is what is
written on the button e.g. OK, MainMenu, etc.

Access Forms – Tool Box
Label
Text Box
Combo Box
List Box
Command
Button
Subform
Access Forms
Form Header
Text Box
Label
Subform
Navigation
buttons
Form Footer
Access Forms
Data Entry
property is set
to ‘No’
These
labels are in
the subform header
Note multiple
associated
records
Default View
property set
to
‘Continuous
Forms’ NOT
‘Datasheet’
OwnerNo and PetNo have
Visible property set to ‘No’
Access Forms
Combo Box
Code in the On Click
event of the Main Menu
button (click on the
property then on … &
then on ‘Code Builder’ to
open up a code window.
Private Sub cmdExitPet_Click()
List Box
‘Not using Wizard
‘closes this form
DoCmd.Close
‘opens main menu form
DoCmd.OpenForm "frmVetMenu"
End Sub
Access Forms
This the code for the
other ‘Main Menu’
button, this time
generated by the
Wizard.
Private Sub cmdMainMenu_Click()
On Error GoTo Err_cmdMainMenu_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmVetMenu“
DoCmd.OpenForm stDocName, , , stLinkCriteria
Would need to insert
DoCmd.Closeform
here.
Exit_cmdMainMenu_Click:
Exit Sub
Err_cmdMainMenu_Click:
MsgBox Err.Description
Resume Exit_cmdMainMenu_Click
End Sub
Wizards can make things
easier sometimes, but can
also make code
unnecessarily complicated
List and Combo Boxes
Can build a
query for the row
source
…
Important
control
settings
Next Week
 Passwords
 Calculated Fields
 Adding new values to lists and combos
Depending on how much you know about Access and VBA
you should be working on the exercise in Appendix 9 as a
way of improving your skills.
Only start on your main project when you have planned and
designed the forms you want in your system and know how
to do the programming.
Passwords
 More than one way to do this
 We will look at



A very basic way of adding password
protection
A more complex (but also more flexible) way
Putting a calculated value into a text box on a
form
Passwords – Easy but Basic Method
 Open the database in exclusive mode
Passwords
 Then select
Tools -> Security > Select Database Password
 Type the password you want to use. Next time
you attempt to open the database file you will be
asked to enter the password as shown below:
 The password can be removed in the same way
Passwords
 It is possible to assign more complex arrangements
of users and groups of users with different permission
from within the Tools -> Security menus.
 You would need to investigate this carefully before
trying it
 Also look at Tools -> Start Up. This enables you to
switch off menus etc on starting the database.
 Make a copy of your database file before trying any
of this or you may end up not being able to get at any
of your work!
Using VBA to Set User Ids and
Passwords
 The vet database has an example of this
implemented
 Start by creating a new table:
Passwords
1. Create a new form
2. We Can add code to the controls of the form
to cause it to check the user ID and Password
entered.
3. Use the On Exit property of the UserID text
box
4. Use the on click property of the OK button
Data Access Objects (DAO)
• Go into the Code Builder for the On-exit event of the UserID text box
• Select Tools -> References from the menu
• Select Microsoft DAO Object Library
• DeSelect Microsoft ActiveX Data Objects library
Enables us to use
Record Sets
Check UserID
On Exit property of USERID text
box
Private Sub txtUserID_Exit(Cancel As Integer)
Dim dbUsers As Database
Dim rsUsers As Recordset
Sets up database and
recordset variables
Set dbUsers = CurrentDb
Set rsUsers = dbUsers.OpenRecordset("Select * from tblUser")
rsUsers.FindFirst "UserID='" & txtUserID & "'"
Stops
on-exit
event
If rsUsers.NoMatch Then
MsgBox ("Invalid Username")
Cancel = True
If there’s no
txtUserID = ""
match…
'txtUserID.SetFocus
End If
rsUsers.Close
dbUsers.Close
End Sub
Close the
variables – (in
this order)
Initialises the
variables so that
rsUsers is a copy
of the Users table
Finds the first userID in
the recordset that
matches the userID
entered
Checking Password
Private Sub cmdOK_Click()
Dim dbUsers As Database
Dim rsUsers As Recordset
On Click event
of the OK button
Set dbUsers = CurrentDb
Set rsUsers = dbUsers.OpenRecordset("Select * from tblUser")
rsUsers.FindFirst "UserID='" & txtUserID & "'"
If txtPassword = rsUsers!Password Then
DoCmd.Close
DoCmd.OpenForm "frmVetMenu"
Else
MsgBox ("Invalid Password")
txtPassword = " "
txtPassword.SetFocus
End If
rsUsers.Close
dbUsers.Close
End Sub
If the password is
correct the vet menu
is opened
If the password is incorrect
we get a message and can
try again
What else can we do with record sets?
 Various methods are available:
 FindFirst
 FindNext
 FindPrevious
 MoveNext
 MovePrevious
 There is more about this on Tugrul’s Essendal’s web
page for the CSCI2010 module:
http://www.cse.dmu.ac.uk/~the/csci2010/T&L.htm
Record sets
 Useful for manipulating data via the forms
 The Record set is a variable that holds the
result of an SQL query – the query result can
be a whole table, or part of a table etc.
 E.g. Select * from tblUser
 Using methods like FindFirst etc. causes a
pointer to point at a particular row or record in
the recordset
Record sets
 So this code:
rsUsers.FindFirst "UserID='" & txtUserID & "'“
 Causes the pointer to point at the first row in the recordset (which
is holding a copy of tblUser) that has a value for UserID that is the
same as that entered in the UserID text box.
 So if I’d typed Carter as my UserID, find first would point at the
2nd record

Record sets
 When using record sets you always have to:
 Declare a database variable
 Declare recordset variable
Dim dbUsers As Database
Dim rsUsers As Recordset
 Then you have to initialise them:
Set dbUsers = CurrentDb
Set rsUsers = dbUsers.OpenRecordset( "Select * from tblUser“ )
 And you have to close them
rsUsers.Close
dbUsers.Close
Different menus for different user types
If txtPassword = rsUsers!Password Then
If rsUsers!UserType = 1 Then
DoCmd.Close
DoCmd.OpenForm "frmVetMenu“
Else
DoCmd.Close
DoCmd.OpenForm “frmOtherMenu”
End If
Else
MsgBox ("Invalid Password")
Enhancement of code
etc. etc
from the OK button on
frmUser
Calculation example
 A treatment table has been added to the
database, such that::


a pet has many treatments, and
a treatment is for 1 pet
PET
1
m
has
TREATMENT
 tblTreatment(TreatmentID, PetNo, Description, Cost)
Calculating total cost per pet
 A text box called txtCost has been added to the Pet
form in which we want to see the total cost of all
treatments for that pet
 How?

Create a query that selects the list of cost values for
the PetNo currently shown on the form:
SELECT tblTreatment.Cost
FROM tblTreatment
WHERE
(((tblTreatment.PetNo)=[forms]![frmPet]![txtPetNo]));
The SQL view of the query should look like this
Calculating cost so far per pet
 In the on-current event property of the Pet form,
enter the following code:
txtCost = DSum("[Cost]", "qryTotalCostRows")
This adds up (sums) the list of costs in our query and puts it in the
txtCost textbox.
And to improve the format of the output within the text box:
txtCost = FormatCurrency(DSum("[Cost]", "qryTotalCostRows"))
Aggregate functions
 Dsum is an example of an aggregate function
 You can also use DAvg, DCount, DMin, DMax
 You can include calculations within these as
well:
e.g. txtOrderTotal = DSum(“[UnitPrice]*[Quantity]”, “Order_Line”)
Finally
 Download the example database from the web page – the





password to get into it initially is jenny
Look in the users table to see the usernames and passwords
required to enter via the frmUser Log In form
Look at the other code on the various forms and make use of it
where necessary in your own applications
Download copies of these slides from the web
The only way to get used to developing Access applications is to
put a lot of time into practising.
There are two other example databases you can look at on the L
drive in the directory CSCI1403 – one on frozen foods, the other
on camping equipment – we have been kindly offered the use of
these by another member of staff