Forms

Forms based on single tables have upsized with no problems. However, it is better to replace the table with a stored procedure. In this way, using SQL Server security, we can grant our users access to the stored procedure rather than the table itself, thus adding an additional level of security.

Important 

When working with the record source of a form it is better, if you intend to upsize, to use a fixed query rather than SQL. When your database is upsized stored procedures or functions will be created. This is very useful if you are using the same SQL in several objects. Instead of multiple stored procedures (one for each SQL string) only one is created. This makes managing these objects much simpler.

frmIceCreamPopup

This pop-up form is called from frmSales and provides additional detail on the price. The form is populated using the following SQL statement in the original database:

   SELECT *     FROM tblIceCreamIngredient     WHERE (((tblIceCreamIngredient.fkIceCreamID)=[forms]![frmSales]![fkIceCreamID]));   

Note the use of the form value as the criteria in the SQL statement. When the database is upsized this SQL statement changes and must be corrected to get the form to perform as intended. The changed SQL statement is as follows :

   SELECT tblIceCreamIngredient.*     FROM tblIceCreamIngredient   

You can see that the WHERE clause has been removed. We are going to replace the WHERE clause and use the forms Input Parameters property to pass the ID value from our sales form, fkIcecrream .

Try It Out-Creating a Stored Procedure

In order to do this we are going to create a new stored procedure to act as the form's record source. Close the form, saving any changes. Then in the Database window:

1. Click Queries.

2. Click New.

3. Select Design Stored Procedure from the dialog.

4. Create the stored procedure shown below.

5. Save the procedure as usp_popup.

   Changing the form's record source:

6. Open frmIceCreamPopup in Design view.

7. Click the Data tab.

8. Select the stored procedure usp_popup in the recordsource drop-down list.

   

   Note the use of @ID as the T-SQL parameter. We are going to use the input parameter of the form to pass the ID value from frmsales to this procedure.

9. Close the query builder and save the changes. Remember this form is called from frmsales and is passed a reference to the fkIceCreamID to filter the records returned. With frmIceCreamPopup in Design view:

10. Open the P roperty Sheet.

11. Click the Data tab to view the Input Parameter property.

12. Enter the following into the Input Parameter property:

     @ID Int,=[Forms]![ frmSales].fkIceCreamID 

13. Open frmSales.

14. Double-click the Ingredients combo box.

    Note that the value in the combo box is passed to the form using the above syntax and the results are now filtered.