1 / 18

Selecting a Specific Record to View or Edit* The “FQRF” Process

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 “F u ll Screen” before proceeding. Click mouse or press space bar to continue.

lexi
Télécharger la présentation

Selecting a Specific Record to View or Edit* The “FQRF” Process

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Selecting a Specific Recordto 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..

  2. 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

  3. Form, Query, Report, Form Process (FQRF) • A process by which you will create three or more objects • A Form on which you select a specific record • A Query that returns only that record • A Report (or a different form) that contains information from only that record • Revise the first Form to include command buttons that link to the report

  4. Creating the Form, 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

  5. Creating the Form, 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

  6. Creating the Form, 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

  7. Creating the Form, 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

  8. Creating the Query • 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

  9. Creating the Query 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

  10. Creating the Report • “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 • Copy an existing form/report, paste it with a new descriptive name, e.g., frmSelectedBuilding • Modify an existing form/report • Create a new form/report using wizard, design view, or master Approach #1 is recommended

  11. Creating the Report (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

  12. Creating the Report (cont’d)

  13. Revising the Form, 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 onform open:

  14. 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

  15. Revising the Form, 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

  16. 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

  17. 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?

  18. One Last Thought • Several selection boxes and associated buttons can appear on the same form …

More Related