Selecting a Specific Record to View or Edit*

The “FQRF” Process If you came to this presentation via a web browser, right click and choose “Full Screen” before proceeding.

Click mouse or press space bar to continue.

• This presentation was prepared by Professor Steve Ross, with the advice of other MIS Faculty, for use in MIS Classes at Western Washington University. Please contact Dr. Ross for permission to use in other settings..

The Challenge

• Create a set of forms, queries, and reports that allow you to select a specific building, and then takes you to reports or forms for that specific building tblManuBuilding

PK,FK1 BuildingID

OfficeArea ManuArea StorageArea RailDock TruckDock PK,I1 tblBuilding BuildingID FK1,U1,U2,I2 Address City State Zip Description Status ManagerID BuildingType tblRetailBuilding

PK,FK1 BuildingID

TotalArea RetailArea CommonArea FoodCourt ParkingArea

Form, Query, Report, Form Process (FQRF)

• A process by which you will create three or more objects – A F orm on which you select a specific record – A Q uery that returns only that record – A R eport (or a different form) that contains information from only that record – Revise the first F orm to include command buttons that link to the report

Creating the

F

orm, Step 1

• Specify the source of data (one or more tables) from which the selection will be made –

tblBuilding

• Determine which field or fields are necessary to make the selection – including both ID and descriptive fields – BuildingID : primary key, identifies selected building to the system – Address, City, and State : will be combined to identify the building to the user for selection

Creating the

F

orm, Step 2

• Create a form that has only one input control – a combo box – I usually create a text box then change to combo box – Give the form a very descriptive name, e.g., frmSelect

Creating the

F

orm, Step 3

• Open the form in design view – Name the combo box, e.g.,

cboBuildingID

• On the property sheet of the combo box, the Control Source should be an empty box. The combo box will show “Unbound” when form is in design view – which means that a change in the combo box will not change the data in any table. • For the Row Source, create a view and save it with the name

vueCboBuildingID

(might already exist) • Set the Bound Column to be the column of BuildingID numbers.

• Adjust other properties of the combo box as necessary so that address data are displayed during and after selection

Creating the

F

orm, Step 3

(cont’d) – On the properties of the form • Erase the Record Source, if any (Data tab) • Set the following to “no” (Format tab) – Scroll Bars – Record Selectors – Navigation Buttons – Dividing Lines • You have now completed the first F of FQRF • Leave the form open and select one of the buildings

Creating the

Q

uery

• The “Query” is an Inline Table-valued Function – Source data for the view might come from a table or another view – whatever is necessary for the ultimate report or form • If you already have a view with the proper fields, e.g., vueFrmBuilding, then copy the Select Statement from that view – Create a new Inline Table-valued Function • Code sample on following screen – Give the function a very descriptive name, e.g., tfnSelectedBuilding

Creating the

Q

uery

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Steve Ross -- Create date: 24-Feb-10 -- Description: Selected Building Info -- ============================================= CREATE FUNCTION tfnSelectedBuilding ( @BuildingID int ) RETURNS TABLE AS RETURN ( SELECT BuildingID, Address, City, State, Zip, Description, Status, ManagerID, BuildingType FROM dbo.tblBuilding

WHERE BuildingID=@BuildingID ) GO

Creating the

R

eport

• “Report” might be a report or another form – Often, existing report or form is modified • Ensure that the selection form is still open, with a building selected • Do one of the following 1. Copy an existing form/report, paste it with a new descriptive name, e.g., frmSelectedBuilding 2. Modify an existing form/report 3. Create a new form/report using wizard, design view, or master Approach #1 is recommended

Creating the

R

eport

(cont’d) • Open the form/report in design view – Set Record Source property to the function, e.g., tfnSelectedBuilding – (On Data Tab) Set Input Parameters property to the combo box on the “Select” form: @BuildingID=[Forms]![frmSelect]![cboBuildingID] – If present, remove record selectors and navigation buttons from forms – these no longer apply since only one record will be available • You have now completed the R of FQRF – If necessary, create other reports or forms relevant to the “selected” building

Creating the

R

eport

(cont’d)

Revising the

F

orm, Step 1

• List all the forms and reports that depend on the selection – frmSelectedBuilding • Create command buttons on the form that open the dependent forms and reports – Note this choice on form open:

Designing for Usability: Prevention of Null Reports

• The query and “report” depend on a record being selected on the form – If no record is selected, interesting or catastrophic results may occur • Use VBA Code and the value of the combo box to disable the button when the value of the combo box is null – Subroutine that sets enabled property to True or False based on value of combo box, called by the following events – For the form, an On Activate event that will execute the subroutine each time to form becomes the active object – For the combo box, an After Update event that will execute the subroutine each time the value of the combo box changes

Revising the

F

orm, Step 2

Private Sub Form_Activate() subCheck4BuildingID End Sub Private Sub cboBuildingID_Change() subCheck4BuildingID End Sub Public Sub subCheck4BuildingID() If cboBuildingID > 0 Then cmdEditBuilding.Enabled = True Else cmdEditBuilding.Enabled = False End If End Sub

Executes each time form becomes the active object Executes after the value of the combo box is changed Subroutine called by the two event handlers

One Gotcha …

• When you click the “Close Form” button on the “Select” form, you will get this message … • The

Dirty

property indicates whether the current record has been modified since it was last saved. The “Select” form has no record source, and therefore nothing to get dirty. Remove the line of code that refers to this property.

If Me.Dirty Then Me.Dirty = False

Quality Assurance

• Are the buttons that lead away from the “Select” form disabled until a selection is made?

• Can the user make a selection – and then change it?

• Once a selection is made, do the buttons link to the appropriate form or report – with the selected instance?

• When the selected form or report is closed, can another selection be made?

• Have all Design Standards been met?

One Last Thought

• Several selection boxes and associated buttons can appear on the same form …