Set the code in the Parent Form to execute the Stored Procedure and set the Subform Recordset. Do not set the Link Child Fields and the Link Master Fields for the subform. In the Subform, add textboxes with the name and controlsource of the columns in the stored procedure. It includes an ADODB Connection Object and an ADODB Command Object as well as a ADODB Recordset Object to assign to the form. The following code is added to the formLoad event. (SELECT TerritoryGroup, Max(SalesYTD) mYTD FROM Sales.vSalesPerson GROUP BY TerritoryGroup) aĪ blank MS Access form is created with TextBox controls named for the columns returned by the temp table in the SQL Stored Procedure.
For example, if your database contains two similar tables. In Microsoft Access, you can concatenate multiple strings together into a single string with the & operator.
The demo database attached to that article contains a few examples of concatenation, in particular the complex multi-line concatenation building. Microsoft Access 2013 databases consist of tables that you can change in a variety of useful ways. This MSAccess tutorial explains how to use the Access & operator to conatentate strings together with syntax and examples. (SELECT SalesPersonID FROM Sales.vSalesPerson v inner join Concatenation is especially important for label reports, when there is little room and no place to grow the sections in this article where originally part of the article Printing labels with Access. ISNULL(SalesQuota,0)ISNULL(SalesLastYear,0)) (SELECT SalesPersonID FROM Sales.vSalesPerson WHERE TerritoryGroup, SalesQuota, SalesLastYear,ĬONVERT(varchar(50),null) as Leader, CONVERT(varchar(150),null) as Quota There are two concatenation operators available in Access: + and &. It uses the view Sales.vSalesPerson in a temp table and the modifies the temp table with updates based on meeting a sales quota and then getting the Max SalesYTD - derived query - to get the Sales Leaders for each territory.įirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName as FName, Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, Python, PHP, Bootstrap, Java, XML and more. In SQL Server, the Stored Procedure is created in the AdventureWorks Database.
Using SQL Stored Procedure in an MS Access Continuous Form This article shows 2 other methods of setting form data to a stored procedure. The error message below that pops up (when the parent form is run) is not accurate (as it refers to a crosstab query when the query used is a SQL Data Definition Query), but it does convey that MS Access does not like what you are attempting to do in linking a subform tied to a SQL Stored Procedure.
While this code will work and update the query, it will not work when this updated SQL query is used as a Recordset for a Subform. Qry.SQL = "EXEC dbo.SalesRatings " & Forms!frmSalesPeople.SalesPersonID
Set qry = CurrentDb.QueryDefs("SQL_QueryData")