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. |
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 .
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:
Click
Queries.
Click
New.
Select
Design Stored Procedure from the dialog.
Create the stored procedure shown below.
Save the procedure as
usp_popup.
Changing the form's record source:
Open
frmIceCreamPopup in Design view.
Click the
Data tab.
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.
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:
Open the P
roperty
Sheet.
Click the
Data tab to view the
Input Parameter property.
Enter the following into the
Input Parameter
@ID Int,=[Forms]![ frmSales].fkIceCreamID
Open
frmSales.
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.
