Access VBA - Assignment #4
Due Date: March 20, 2003
For this assignment, you will create complex forms and apply data entry control techniques. In
addition to the existing PurData.mdb found in K:\AccessVBA\Assign2\Datafiles, you will
embed images to your command buttons. The images can be found in
K:\AccessVBA\Assign4\Images. For this assignment, you must add error handling to your VBA
Menu bars and startup options.
Multi-record data entry control using command buttons.
o Programmatically manipulating records: Form properties such as
AllowAdditions, AllowDeletions, NavigationButtons
o Control properties such as Enabled, Locked, ForeColor, BackColor, etc.
Creating subforms and embedding subforms into forms
o Using special object variables such as Parent
o Special methods such as Requery, Recalc
Programmatically setting default values and calculating fields
Submission Requirements: You must modify and/or create all your forms, reports and modules
in your own client-side Access file (i.e., PurClientXX.mdb). In addition to keeping a copy of your
client-side Access file, you must print a hard copy of Form_fsubPurchaseOrderLines,
Form_frmPurchaseOrder and other VBA codes you added for this assignment.
A. Calling functions and documentation standards (5 points)
Modify the basUtilities.HandleOpenForm and convert the subprocedure to a function.
[Hint: Change the Sub keyword to Function.] (1 point)
Modify frmSwitchboard to call the HandleOpenForm function in the OnClick event of
all the command buttons (e.g., cmdPurchaseOrders). [Hint: You need to prefix the
function call with an “=” sign.] (1 point)
Make sure all your functions and subprocedures are documented as per Assignment
standards. In addition, you must handle all Access errors appropriately. (3 points)
B. Main Menu Bar and Startup Options (5 points)
Hint: Use Lecture slides 9d for more details.
Add a new frmAbout form (see Figure 1). (2 points)
Add a MainMenu command bar with three options: (1) File | Link Purchasing Tables
(opens frmLinkTables), (2) File | Exit (executes built-in Access command), (3) Help |
About (opens frmAbout). (2 points)
Modify the start-up options to only display the MainMenu command bar. (1 point)
Figure 1. frmAbout
C. Creating a base form with picture command buttons (5 points)
Form Name: frmBase
Figure 2. frmBase shown with all the picture command buttons
Requirements: Create frmBase and set the following form properties: Scroll Bars = Neither;
Record Selector = No; Navigation Buttons = No; Dividing Lines = No. Add the command buttons
as shown in Figure 2. For each command button (e.g., cmdClose), add an embedded image (e.g.,
close.bmp) and set the appropriate ControlTip Text (e.g., Close Form). Use Table 1 to name each
command button (e.g., cmdClose). (5 points)
Name Image ControlTip Text
cmdClose close.bmp Close Form
cmdSave save.bmp Save Record
cmdUndo undo.bmp Undo Record
cmdAdd add.bmp Add Record
cmdDelete delete.bmp Delete Record
cmdFind find.bmp Find Next Record
cmdEdit edit.bmp Edit Record
cmdPreview preview.bmp Preview Document
cmdCalc calc.bmp Calculate Totals
Table 1. Command buttons
D. Creating a Subform (10 points)
Form Name: fsubPurchaseOrderLines
Figure 3. fsubPurchaseOrderLines shown in run-time view
1. GUI Forms Design (5 points)
Create fsubPurchaseOrderLines (see Figure 3) and set default view to Continuous Forms.
Set the Record Source to a query that comes from tblPurchaseOrderLines and
tblProducts. Your query must include: PurchaseOrderId [Ascending]. (2 points)
Add the following Detail fields: (2 points)
o txtLineNumber: LineNumber [Ascending]
o cboProductID: ProductID
o txtDescription: tblProduct.Description
o txtQty: Qty
o txtBalance: Balance [Calculated Field]: Qty – NZ(QtyReceived)
o cboUOMCode: UOMCode
o txtCost: Cost
o txtTaxAmount: TaxAmount
o txtExtCost: ExtCost
Add the following Form Footer fields: (1 point)
o Sum of TaxAmount
o Sum of ExtCost
2. Data Entry Control (5 points)
Control Properties (1 point)
o cboProductID: List of ProductIDs and Product Descriptions
o cboUOMCode: List of UOM codes
o txtLineNumber, txtDescription, txtTaxAmount, txtBalance, txtExtCost: Locked =
In the txtProductID’s AfterUpdate event, validate non-null ProductID and set the default
values (2 points)
o Verify that cboProductID is not null
o Set the cboProductID to upper case (Hint: UCase$)
o Set the cboUOMCode default value (from tblProducts.QuantityPerUnit)
o Set the txtCost default value (from tblProducts.Cost)
Add a CalcExtCost function that calculates the following: (2 points)
o ExtCost = Qty * Cost
o TaxAmount = ExtCost * .10
o To immediately see the totals updated, add: Me.Recalc
o Note: For simplicity, we assume a discount of zero. We also assume a tax rate of
o txtQty AfterUpdate: Call the CalcExtCost function [Hint: set to
o Change the quantity of the existing purchase order lines. Result: The Tax Amount
and Extended Cost are recalculated.
o For the ProductIDs you will use for testing, set the UOM and Cost in tblProducts.
o Attempt to add a new record by entering a valid ProductID. The Cost and UOM
are defaulted from tblProducts. Press ESC to undo changes.
o Note: At this point, you cannot add a new purchase order line record because a
PurchaseOrderID is required.
E. Creating a Data Entry Form with Command Buttons (15 points)
Form Name: frmPurchaseOrder
Figure 4. frmPurchaseOrder in Edit mode
1. GUI Forms Design (5 points)
Create frmPurchaseOrder (see Figure 4). Set the Record Source to a query that comes
from tblPurchaseOrder and tblVendors.
Add the following Detail fields:
o txtPurchaseOrderId: PurchaseOrderId
o txtOrderDate: OrderDate
o txtPOStatus: POStatus
o txtVendorInstruction: VendorInstruction
o cboVendorID: VendorID (Show VendorID, Company)
o txtVendorAddress: =[Address] & Chr(13) & Chr(10) & [City] & ", " & [Region]
& " " & [PostalCode] & Chr(13) & Chr(10) & [Country]
o txtContact: Contact
o txtShipCompany: ShipCompany
o txtShipAddress: ShipAddress
o txtShipCity: ShipCity
o txtShipRegion: ShipRegion
o txtShipPostalCode: ShipPostalCode
o txtShipName: ShipName
Add the subform fsubPurchaseOrderLines
o Subform Name: fsubPurchaseOrderLines
o Source Object: fsubPurchaseOrderLines
o Master/Child fields: PurchaseOrderID
cboSelectPurchaseOrder (Unbound Control)
o Show PurchaseOrderID, OrderDate, tblVendors.Company
o AfterUpdate event: Move the record to the PurchaseOrderID selected.
cboVendorID (Bound Control)
o Show VendorID and Company
o AfterUpdate event:
Verify that cboVendorID is not null
Set the cboVendorID to upper case (Hint: UCase$)
Set the txtVendorInstruction default value (from
Set the txtContact default value (from tblVendors.ContactName)
Figure 5. frmPurchaseOrder in Browse mode
2. Data Entry Control (5 points)
Add LockDataControl procedure to basUtilities. [Hint: Copy existing procedure from
Add SetEditMode subprocedure
o Lock Goto Document Control (i.e., cboSelectPurchaseOrder)
o Unlock edit fields: All fields except primary key and non-edit fields
o Lock primary key: txtPurchaseOrderID
o Lock non-edit fields: txtPOStatus, cboVendorID, txtVendorAddress
Add SetBrowseMode subprocedure
o Unlock cboSelectPurchaseOrder and lock all other fields
cmdSave: (Record Validation):
o PurchaseOrderID must not be null.
Note: Checking for unique keys is handled by Access.
o OrderDate cannot be greater than today.
o Reminder: Remember to unlock primary key (txtPurchaseOrderID) and unlock
txtVendorID after calling SetEditMode
o Set POStatus to “Open”. POStatus is set programmatically all the time.
cmdEdit, cmdDelete, cmdUndo: Follow standard data entry control techniques (see
Figures 4 and 5).
cmdCalc: recalculates the total cost and total tax amount.
o Hint: Use Me![fsubPurchaseOrderLines].Requery
Add cmdOpenFrmVendor command button (beside cboVendorID) and embed
openSmall.bmp image. OnClick: If VendorID is null, open frmVendors, else open
frmVendors with a where condition (i.e., find the specific VendorID before opening the
form). [Hint: Use DoCmd.OpenForm]
3. Insert, Update and Delete Purchase Order Line Records (5 points)
Start by testing your navigation buttons of frmPurchaseOrder. Your Purchase Order
should only show Purchase Order Lines that belong to the same PO.
Adding a new Purchase Order Line
o In fsubPurchaseOrderLines Detail section, add txtPurchaseOrderId (Visible =
No, Default Value = [Forms]![frmPurchaseOrder]![txtPurchaseOrderID])
o Modify txtLineNumber [Default Value = 0]
o In the Form Header section, add txtNextLine and set Control Source
“=Max([LineNumber])+1”. txtNextLine will automatically carry the next
available line number for the current Purchase Order.
o In fsubPurchaseOrderLines class module, create a new AssignNewLine
subprocedure. This procedure will first requery the txtNextLine. If txtNextLine is
null then set txtLineNumber to 1. Otherwise, txtNextLine is assigned to
o Form_BeforeUpdate: Call AssignNewLine (before the record is updated/added
to the table).
Deleting a new Purchase Order Line
o Set fsubPurchaseOrderLines RecordSelectors = Yes
o Testing Example: To delete an order line, right-click and select Cut
o Form_BeforeDelConfirm: If the parent form’s cmdSave is not enabled (i.e., Not
Parent!cmdSave.Enabled), then show message “Cannot delete order line when in
Browse mode” and cancel the delete.
Reminder: You must keep a copy of your client-side Access file in your
G:\AccessVBA\Assign4 directory. However, you need to email PurClientXX.mdb to your
instructor and submit a hard copy of your VBA codes.