Use stored procedure as record source on subform

Posted on 2006-06-01
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
    LVL 65

    Expert Comment

    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

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

    Author Comment

    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

    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
    LVL 1

    Author Comment

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

    Accepted Solution

    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!
    LVL 3

    Expert Comment


    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now