Master Maintenance Forms

1
Master Maintenance Forms
2
Master Maintenance Forms

• Allow the user to browse through the records in a
  record set
• Allow the user to modify the data
• Change a record
• Add a new record
• Delete a record

3
Protecting the Data

• A professional interface will protect the user
  from themselves by not permitting them to make a
  wrong move
• Without this protection a user may accidentally
  change a record without intending to

4
Two Distinct Form States

• Browse State
• The user may move from record to record
• Changes may not be made to the data
• Modify State
• The user may change the data shown on the form
• User is fixed on the current record and no
  movement to another record may occur
• User may not return to browse state until they
  exit from modify state by clicking either SAVE or
  CANCEL

5
Enforcing the States

• The states are enforced programmatically by
• enabling and disabling buttons so the user is
  presented with only valid options in each state
• Locking the data controls to restrict users from
  altering the data or unlocking the controls to
  allow them to alter data according to the state
  they are in

6
Sample User Interface - browse state
Buttons to control activity - Save / Cancel
disabled Focus is off the data All data controls
are locked the user may not alter any data in
this state Buttons to control movement through
the recordset enabled
First Previous Next Last
7
Sample User Interface - modify state
Buttons to control activity - only Save / Cancel
enabled All data controls except autonumber key
are unlocked the user may alter the data in this
stateFocus set to first unlocked
control Buttons to control movement through the
recordset are disabled
8
The user changes states by

• clicking Add or Edit to leave browse state and
  enter modify state
• clicking Save or Cancel to leave modify state and
  return to browse state

9
Selecting the data for the form

• Data that appears on a form is from the forms
  underlying Recordset
• A recordset is a group of records retrieved from
  1 or more tables in a database
• A recordset may be from a table or a query
• Queries are popular recordset sources as they can
  provide records in sorted order, or provide only
  certain records based on criteria

10
Selecting the data for the form

• For master file maintenance the recordset for the
  form should consist of records from the master
  table or query based on the master table only
• Combo or list boxes on the form can be used to
  display data from other tables or queries (as the
  select vendor control does), but the fields from
  the vendor table are not part of the recordset
  for the form

11
Linking the Data to the form

• Data may be linked to the form in several
  manners, each of which have associated pros and
  cons

12
Method 1 Linking using bound controls
Forms Record Source Property

• Wizard for form building does this
• Can drag fields to form using field list icon
• Each control is bound to a field in the record
  source, you see the field name in the control in
  design view
• The Control Source property contains the data
  fields name
• PROS fast easy to create forms
• - record navigation bar automatic
• CONS hard to enforce control
• of states

13
Method 2 Linking using unbound controls
writing code to manipulate recordset

• No direct association in the forms properties to
  the recordset
• No list of available fields
• controls are unbound - No control source
• PROS
• can take total control of the form
• logic makes sense to programmers and is
  transportable to other applications
• CONS
• must write code to open a recordset on form load
• must write code to populate (fill) controls with
  data from recordset, and save (write) data from
  controls to recordset
• must build buttons and code for recordset
  navigation

14
Code for the Method 2 Unbound controls/form
15
Connecting to your Data

• From a form you can connect to data in a data
  base using the DAO or ADO object models

16
DAO Data Access Objects

• Access uses the Jet engine and DAO
• Can use it to do just about anything with the
  database
• Good for a small desktop application or one that
  runs on a local server
• Bundled with Visual Basic 6.0

17
ADO ActiveX Data Objects

• ADO is the successor to DAO
• Flatter object model (contains fewer objects and
  more properties, methods and events)
• Permits access to many more data sources other
  than Access (i.e. Oracle, SQL server, etc.)
• Better for larger client/server apps

18
The following code examples use ADO
19
Writing code in VBA (Visual Basic for
Applications)

• Open a new form in design view
• Click the code icon to open a code window
• Code window view
• To exit code window click close icon- this just
  closes code window and not Access

20
Writing code in VBA

• follow established coding standards
• document your code with comments and dividing
  lines between subroutines
• Include white space for easier reading
• Name objects with standard prefixes
• Include OPTION EXPLICIT to force variable
  declaration
• add form wide scope variables at top outside of
  subroutines

21
Connecting to the database, 1-way 3-ways in total

• Requires 2 object variables with form wide scope
• One for the database object
• One for the recordset object
• DIM db1 as ADODB.Connection
• DIM rstCust as NEW ADODB.Recordset

22
Open the connection to the database and recordset

• DONE once only at the start
• what event?
• Make sure globals
• are set from prior slide
• Form_Load() event

23
Adding event subroutines

• In the code window select object and event
• or
• In the form design window click on the form,
  properties, event choose event and click the
  to transfer to code window
• or
• In the code window type the subroutine header ?
• Private Sub Form_Load()

24
Open the Recordset

• Private Sub Form_Load()
• Establish a connection to the database
• Set db1 CurrentProject.Connection
• Open a recordset using an SQL statement to
  specify the records
• see Access help for parameter definition
• rstCust.Open "Select from qryCustAlpha", db1,
  adOpenKeyset, adLockOptimistic, adCmdText

25
Getting data to show on form

• In form design view add unbound controls (i.e.
  text boxes, combo boxes, radio groups, etc) to
  the form
• At form load and whenever the current record
  pointer is moved assign the data in the current
  record to the forms controls as follows
• me.txtLastName rstCust.Fields(lastName)
• current control recordset
  fields name of fieldform name
  name method (must be
  in rst)

26
Getting data to show on form

• Write a routine to fill each control with the
  data in the current record
• (could name the routine readData)
• Call that routine whenever the current record
  changes (i.e. at the start, when navigate to
  another record, when add a new record )

27
DAO Recordset Navigation

• Navigation
• Think of a recordset a collection of records in
  the order specified by the query or table
• BOF First Last EOF
• BOF Beginning of File marker current
  record pointer
• EOF End of File marker

28
ADO Navigation Methods - moving the current
record pointer

• .MoveFirst
• .MoveLast e.g. rstCust.moveFirst()
• .MoveNext
• .MovePrevious
• Code the events for the click of the navigation
  buttons
• Use the navigation methods above
• Check for BOF and EOF to ensure that you dont
  stay on these markers as opposed to valid records
  (see next slide)

29
ADO Navigation Methods - moving the current
record pointer

• If the user presses while on the first
  record and your code does a .MovePrevious the
  current record pointer will move to BOF. Your
  code should then position them to the first
  record, do not stay on BOF
• Use the BOF property to test to see if you are at
  BOF
• rstCust.MovePrevious
• If rstCUST.BOF True then
• rstCust.MoveFirst
• similar processing is required to handle pressing
  while on the last record

30
ADO methods for controlling record updating

• ADO records are by default in edit mode i.e.
  their contents may be altered
• Adds a temporary blank record to the recordset,
  if there is an autonumber field a new number is
  created and assigned
• Writes the contents of the current record to the
  underlying table
• Undoes any changes to the current record and
  removes the new record if were adding

• .AddNew
• .Update..
• .CancelUpdate

31
ADO methods for controlling record updating

• Requeries the recordset (like a refresh)
• Finds the first record that matches the criteria
  string provided and sets the current record
  pointer to that record. If a record is not found
  the current record pointer will be left at EOF.

• .Requery
• .Find.

Dim SavedCustNo rstCust.Fields(CustNo")
rstCust.Requery rstCust.Find CustNo"
SavedCustNO
32
Find

• Example
• rstCust.Find CustNo savedCustno
• Criteria string

• The criteria string is built by concatenating
  a field name literal, a relational
  operator and a value (literal or variable)
• Ensure that the type of the value matches the
  type of the field

33
Tying it all together

• Control the state programmatically by writing
  routines to setBrowseState and setModifyState
• These routines will
• enable / disable buttons i.e.
• cmdAdd.Enabled True (or False)
• Lock / unlock controls i.e.
• me.txtLastName.locked True (or False)

34
Tying it all together

• Use the navigation methods to control the
  movement of the current record pointer
  (.MoveFirst, .MoveNext, etc.) ensuring that the
  record pointer is always on a valid record (not
  .BOF or .EOF)
• At the start and every time the record pointer
  changes call the routine (readData) to assign the
  values in the fields of the current record to the
  controls on the form i.e.
• me.txtLastName rstCust.Fields(lastName)

35
When the user clicks Add

• Change to modify state
• add a new record (.AddNew)
• Set default values if desired
• rstCust.Fields(category) 1
• Call routine (readData) to assign the value of
  the new records fields to the controls
  (effectively blanks out controls)

36
When the user clicks Cancel

• If in the process of Adding a record then invoke
  the .CancelUpdate method to remove the
  temporarily added blank record
• To determine if you were in the process of adding
  a record check the recordsets EditMode property
  e.g.
• If rstCust.EditMode adEditAdd
• - adEditAdd is an access database constant
• - it indicates that the AddNew method has been
  invoked, and the current record in the copy
  buffer is a new record that hasn't been saved in
  the database.

37
When the user clicks Cancel

• If you have not changed the current record or
  added a new record, calling the CancelUpdate
  method generates an error so you must check that
  EditMode indicates adEditAdd before executing the
  .CancelUpdate method
• If you are adding a new record when you call the
  CancelUpdate method, the record that was current
  prior to the AddNew call becomes the current
  record again. ?

38
When the user clicks Cancel

• note no changes will have yet been made to the
  current record if Editing so a cancelUpdate need
  not be performed (changes are on the screen only
  and not written to the fields in the record yet)

39
When the user clicks Cancel

• Always (whether were adding or editing)
• call the routine (readData) to assign the values
  of the current fields to the controls on the form
  (to refresh to prior data)
• Change back to browse state.

40
When the user clicks Save

• If valid Data (use a function to check)
• Save the value of the current key field into a
  variable
• Call a routine (writeData) to assign the values
  in the controls on the form to the current
  records fields
• rstCust.Fields(lastName) me.txtLastName
• ---- continued next page ----

41
When the user clicks Save (contd)

• Write the current record back to the recordset
  (.Update)
• Requery the recordset ( to refresh records may
  have changed positions in the recordset if the
  value of their order-by field changes)
• Do a .Find to reposition to the record just saved
  (use the variable that the key field was saved
  in)
• Change back to browse state.

42
When the user clicks Save (contd)

• Set hot keys
• Cannot set focus on a disabled control