Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Use stored procedure as record source on subform

Posted on 2006-06-01
Medium Priority
Last Modified: 2008-12-05
I must be an idiot today. I'm trying to create a subform in access. The wizard asks me, "What data would you like to use for your subform or subreport?" It gives me the options of 'Existing Table and Views' or 'Existing Forms'. Stored procedures is not an option. If I select  'Table and View' stored procedures are not listed. If I cancel the wizard and type the name of my stored procedure into the subform properties Record Source it says, "The form name 'myProcedure' is misspelled or refers to a form that doesn't exist".

I've only ever created one subform in Access about six months ago and it specifies a stored procedure as the record source. How did I get the s.p. in that subform? I have amnesia.
Question by:jmarkfoley
  • 3
  • 2
LVL 65

Expert Comment

ID: 16808330
I assume this is for ADP's?
I dont think u can do this for MDB's unless Im mistaken

have a look at this http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart00/html/sa00f10.asp

and check out the paragraph "Using stored procedures with forms"

Author Comment

ID: 16808509
I use stored procedures with forms all the time. This is a subform. This is for ADP. I did it on my other subform, so I know it can be done. I just don't know how I did it.
LVL 65

Expert Comment

ID: 16808906
so what if u created a view that behaved the same as the stored procedure with the exception of any parameters that it may take,
and then linked in that view?

on your form code, u can apply the relevant filters
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 16809165
A view won't work very well. There are 10s of thousands of records to aggregate and I just need a tiny selection.

Accepted Solution

jmarkfoley earned 0 total points
ID: 16809214
Never mind - I figured out a solution myself. Maybe this is the only one. I didn't use the subform wizard to create the subform. Instead I created is as a distinct regular form. This does allow you to designate a stored procedure as the record source.

I then edit the parent form and added a subform. On the wizard I selected 'Existing Forms' and specified the form I just created. That's it.

In the parent form form_current() function I added:

    RequerySub "_frmPaActiveContribSubform", Me.SSNO

where is the textBox SSNO control containing the social security number of the person I'm querying contributions for.

In the RequerySub() subroutine I coded:

        With Me(sFrmName).Form
            .RecordSource = "_frmPaActiveContrib_sp"
            .InputParameters = "@SSNO='" & sSSNO & "'"
        End With

@SSNO is the stored procedure parameter in the where clause. Works like a charm!

Expert Comment

ID: 23110706

Its a known issue in Access 2000 that the stored procedures dont show up in the create new form wizards. If you just create a blank form you can go into the form properties and then select your stored procedures. But hey if your up and running now anyway....

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

571 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