Beginning Access 1002002 Vba [Electronic resources] نسخه متنی

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.

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:

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

property:


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