Solved

Calling Parameters for a pass through query from a form IN MS Access 2003

Posted on 2011-02-18
9
960 Views
Last Modified: 2012-05-11
Hi Experts

I am executing a stored proc as a pass through query in sql but am having difficulty passing the parameters to the sp at run time from an Ms Access form.  I have used various examples that I have found on this and other forums but am just not winning.  The sp is Called upAmbledown and there are Param1 and Param2 that will get pulled from controls on the Access form.  I would like to do an ADO connection on the form and then set up the connection to the query after it has executed using the params as the WHERE and then open a form called frmBusiness and set the source of the form to the connection

Your help will be appreciated.
0
Comment
Question by:caandal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 85
ID: 34924818
You can't add an ADO connection "to a form". An ADO connection is just an object that you build using VBA. That connection can be used to connect to a database.

You CAN set the Recordset of an Access form to a Recordset, if that's what you mean.

To run your Stored Proc, you can do this:

Dim con as ADODB.Connection
Set con = New ADODB.Connection

con.Open "Your ConnectionString"

con.Execute "EXEC upAmbleDown @Param1=" & Me.YourFirstControl & ", @Param2=" & Me.YourSecondControl

This should fire off your stored procedure, assuming you have the necessary permissions on the server to do this. Note that this assumes the parameters are numeric; if they Text or Date values, do this:

con.Execute "EXEC upAmbleDown @Param1='" & Me.YourFirstControl & "', @Param2='" & Me.YourSecondControl & "'"

Basically, I'ved enclosed the value of the parametrs in single quotes ( ' )

After doing this, you can then open a Recordset based on that connection:

Dim rst AS ADODB.Recordset
Set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient

rst.Open "Your SElect Statement here", con

Now you can open your form, and set the Recordset property:

DoCmd.OpenForm "YourFormName"

Set Forms("YourFormName").Recordset = rst


0
 

Author Comment

by:caandal
ID: 34927617
Hi LSM - Long time no chat hope that life is treating u well.  Your code makes absolute sense and is easy to read and understand. - Thanks for that
I am getting an error on rst.Open and suspect that it may have something to do with the connection string statement itself please have a look at the screenshots and see if you can pick anything up.  Screen shot - error  Screen shot error occurring in this line
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34928796
You need to provide a valid SQL statement. Your "strConnection" string is a connection string that's used to initialize your ADODB.Connection object.

A valid SQL string would be something like:

SELECT * FROM MyTable.

Does your Stored Proc return a Recordset? If so, then you can "open" it with your recordset:

Dim con as ADODB.Connection
Dim rst As ADODB.Connection

Set con = New ADODB.Connection

con.Open "Your ConnectionString"

Set rst = New ADODB.Connection
rst.Open "EXEC upAmbleDown @Param1=" & Me.YourFirstControl & ", @Param2=" & Me.YourSecondControl, con



0
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 

Author Comment

by:caandal
ID: 34931386
upUmbledown returns a query with the two parameters.  The statement that you have made above does not make sense to me.  Why are you opening a table for example and then returning another recordset with the stored proc?
The query that is being run is a complicated one.
0
 

Author Comment

by:caandal
ID: 34931395
maybe I should just run a view with the 2 parameters instead of a stored procedure?
0
 

Author Comment

by:caandal
ID: 34931400
Here is the stored proc that is being executed
USE [ComplimedSQL]
GO
/****** Object:  StoredProcedure [dbo].[upAmbledown]    Script Date: 02/18/2011 18:43:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[upAmbledown] 
( 
@MyParam1 NVarchar(50),
@MyParam2 Varchar(50) 
) 
AS 
SELECT     dbo.MemberStatus.MemberStatusID, dbo.MemberStatus.MembershipStatus, dbo.Policy.PolicyID, dbo.Policy.PolicyNo, dbo.People.Initial, dbo.People.FirstName, 
                      dbo.People.SURNAME, dbo.People.IDNumber, dbo.People.DOB, dbo.Policy.DateJoined, dbo.Policy.DateResigned, dbo.Policy.CompanyID, dbo.Company.Company, 
                      dbo.Policy.Paypoint, dbo.Brokerage.BrokerageID, dbo.Brokerage.Brokerage, dbo.Broker.Broker, dbo.NewPolicyProductDetails.AmtDue, 
                      dbo.Product.PRDescription AS TARIFF, dbo.NewPolicyProductDetails.TariffAmt, Product_2.PRDescription AS COPAYMENTS, 
                      dbo.NewPolicyProductDetails.CoPaymentsAmt, Product_4.PRDescription AS INHOSPITAL, dbo.NewPolicyProductDetails.InHospitalAmt, 
                      Product_11.PRDescription AS CANCER, dbo.NewPolicyProductDetails.CancerAmt, Product_8.PRDescription AS [HOSPITAL ADMISSION], 
                      dbo.NewPolicyProductDetails.HospAdmissionAmt, Product_9.PRDescription AS [PREMIUM WAIVER], dbo.NewPolicyProductDetails.PremiumWaiverAmt, 
                      Product_1.PRDescription AS COPAYCANCER, dbo.NewPolicyProductDetails.CopayCancerAmt, Product_7.PRDescription AS CASHBACK, 
                      dbo.NewPolicyProductDetails.PerHospBenAmt, Product_12.PRDescription AS [HOSPITAL EXCESS], dbo.NewPolicyProductDetails.HospExcessAmt, 
                      Product_10.PRDescription AS KEYCARE, dbo.NewPolicyProductDetails.KeyCareAmt, Product_3.PRDescription AS FUNERAL, 
                      dbo.NewPolicyProductDetails.FuneralAMT, Product_6.PRDescription AS PACKAGES, dbo.NewPolicyProductDetails.PckgPlansAmt, dbo.Policy.TelW, dbo.Policy.Mobile, 
                      dbo.Policy.[E-mail], dbo.Policy.ReasonResigned, dbo.MedicalAids.MedicalAid, dbo.Policy.MANumber, dbo.Policy.PolYearID, dbo.vPolicyTotals.Premium, 
                      dbo.vPolicyTotals.Risk, dbo.vPolicyTotals.BrokerComm, dbo.vPolicyTotals.BrokerFee, dbo.vPolicyTotals.PolicyFee, dbo.vPolicyTotals.AdminFee, dbo.Policy.Addr1, 
                      dbo.Policy.Addr2, dbo.Title.Title, dbo.Policy.CITY, dbo.Suburb.SuburbID, dbo.Policy.PROVINCE, dbo.Policy.[POSTAL CODE], dbo.Suburb.Suburb, 
                      dbo.Brokerage.[BROKER TEL], dbo.vPolicyTotals.PolicyID AS Expr1,@MyParam2 AS 'Month'
FROM         dbo.NewRates AS Rates_7 LEFT OUTER JOIN
                      dbo.Product AS Product_7 ON Rates_7.ProductID = Product_7.ProductID RIGHT OUTER JOIN
                      dbo.NewRates AS Rates_6 LEFT OUTER JOIN
                      dbo.Product AS Product_6 ON Rates_6.ProductID = Product_6.ProductID RIGHT OUTER JOIN
                      dbo.Product RIGHT OUTER JOIN
                      dbo.NewRates ON dbo.Product.ProductID = dbo.NewRates.ProductID RIGHT OUTER JOIN
                      dbo.NewRates AS Rates_2 RIGHT OUTER JOIN
                      dbo.NewRates AS Rates_4 RIGHT OUTER JOIN
                      dbo.Product AS Product_1 RIGHT OUTER JOIN
                      dbo.NewRates AS Rates_11 RIGHT OUTER JOIN
                      dbo.Title RIGHT OUTER JOIN
                      dbo.People ON dbo.Title.TitleID = dbo.People.TitleID RIGHT OUTER JOIN
                      dbo.Company RIGHT OUTER JOIN
                      dbo.MemberStatus RIGHT OUTER JOIN
                      dbo.Suburb RIGHT OUTER JOIN
                      dbo.Policy INNER JOIN
                      dbo.vPolicyTotals ON dbo.Policy.PolicyID = dbo.vPolicyTotals.PolicyID RIGHT OUTER JOIN
                      dbo.NewPolicyProductDetails ON dbo.Policy.PolicyID = dbo.NewPolicyProductDetails.PolicyID ON dbo.Suburb.SuburbID = dbo.Policy.SuburbID LEFT OUTER JOIN
                      dbo.Brokerage ON dbo.Policy.BrokerageID = dbo.Brokerage.BrokerageID ON dbo.MemberStatus.MemberStatusID = dbo.Policy.MemberStatusID ON 
                      dbo.Company.CompanyID = dbo.Policy.CompanyID ON dbo.People.PeopleID = dbo.Policy.PrincipalID ON 
                      Rates_11.RateID = dbo.NewPolicyProductDetails.[CancerID-O] LEFT OUTER JOIN
                      dbo.NewRates AS Rates_1 ON dbo.NewPolicyProductDetails.[CopayCancerID-P] = Rates_1.RateID ON Product_1.ProductID = Rates_1.ProductID ON 
                      Rates_4.RateID = dbo.NewPolicyProductDetails.[InHospitalID-N_OR_Q] ON Rates_2.RateID = dbo.NewPolicyProductDetails.[CoPaymentsID-M] LEFT OUTER JOIN
                      dbo.MedicalAids ON dbo.Policy.MedicalAidID = dbo.MedicalAids.MedicalAidID LEFT OUTER JOIN
                      dbo.Broker ON dbo.Policy.BrokerID = dbo.Broker.BrokerID ON dbo.NewRates.RateID = dbo.NewPolicyProductDetails.TariffID LEFT OUTER JOIN
                      dbo.NewRates AS Rates_3 LEFT OUTER JOIN
                      dbo.Product AS Product_3 ON Rates_3.ProductID = Product_3.ProductID ON dbo.NewPolicyProductDetails.FuneralID = Rates_3.RateID ON 
                      Rates_6.RateID = dbo.NewPolicyProductDetails.PckgPlansID ON Rates_7.RateID = dbo.NewPolicyProductDetails.PerHospBenID LEFT OUTER JOIN
                      dbo.NewRates AS Rates_8 LEFT OUTER JOIN
                      dbo.Product AS Product_8 ON Rates_8.ProductID = Product_8.ProductID ON dbo.NewPolicyProductDetails.HospAdmissionID = Rates_8.RateID LEFT OUTER JOIN
                      dbo.NewRates AS Rates_9 ON dbo.NewPolicyProductDetails.PremiumWaiverID = Rates_9.RateID LEFT OUTER JOIN
                      dbo.NewRates AS Rates_10 LEFT OUTER JOIN
                      dbo.Product AS Product_10 ON Rates_10.ProductID = Product_10.ProductID ON dbo.NewPolicyProductDetails.KeyCareID = Rates_10.RateID LEFT OUTER JOIN
                      dbo.NewRates AS Rates_12 LEFT OUTER JOIN
                      dbo.Product AS Product_12 ON Rates_12.ProductID = Product_12.ProductID ON dbo.NewPolicyProductDetails.HospExcessID = Rates_12.RateID LEFT OUTER JOIN
                      dbo.Product AS Product_9 ON Rates_9.ProductID = Product_9.ProductID LEFT OUTER JOIN
                      dbo.Product AS Product_2 ON Rates_2.ProductID = Product_2.ProductID LEFT OUTER JOIN
                      dbo.Product AS Product_4 ON Rates_4.ProductID = Product_4.ProductID LEFT OUTER JOIN
                      dbo.Product AS Product_11 ON Rates_11.ProductID = Product_11.ProductID
WHERE     (Policy.MemberStatusID = 1)and ((policy.CompanyID)= @MyParam1)

Open in new window

0
 

Author Closing Comment

by:caandal
ID: 34931573
Hi LSM

I've played around and left out the con.Open "Your ConnectionString" and it works perfectly

Thanks a ton
Good Luck with getting to the top Barbequer
We call them Braai's here in South Africa and it is a hugely popular pastime in this country.
0
 

Author Comment

by:caandal
ID: 34931580
LSM

BTW the link to your website is non-functional at the moment
Regards
Alan
0
 
LVL 85
ID: 34932707
<I've played around and left out the con.Open "Your ConnectionString" and it works perfectly >

Sorry, guess I should have been more explicit, but I thought it was obvious. You'd have to replace "Your Connection STring" with your own connection string. In other words, you would require an open, valid connection to the database before you open your recordset.

0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question