Access 2002 Data Projects for Developers

Using functions

SQL Server 2000 implements a new type of rowset-returning object, the function. A function is similar to a stored procedure in that it can return a value that depends on an input parameter. Unlike a stored procedure, though, the result of a function can be used in the FROM clause of a SELECT statement. From the Transact SQL point of view, you can think of a function as a sort of inline procedure; from the point of view of Access ADPs, functions are good ways to handle parameterized queries.

Figure 4 showed you a function in the designer. This particular function takes the storeid that represents a store in the pubs database as a parameter, and returns a rowset containing all books sold in that store together with the quantity sold. Figure 7 shows a form based on this function. When the user selects a store from the combo box, the Change event runs to reset the form’s RecordSource property:

Private Sub cboStore_Change()

Me.RecordSource = _

“SELECT * FROM SalesByStore(” & cboStore & “)”

End Sub

Functions provide a handy trick for the construction of parameterized recordsets. Functions can contain quite complex conditional logic and multiple statements, though once they get past a certain level of complexity they can’t be represented in the graphical designer.

via Access 2002 Data Projects for Developers.