I am trying to dynamicallys set a form's recordsource to a stored 
procedure, but I can't get it to work.

I open the form with no bound record source, then I have a button that 
sets it to a stored procedure.

I am using:  

Forms!F_Elements.RecordSource = spElements 
 
    The form opens but no records

Forms!F_Elements.RecordSource = "spElements" 
    Brings up an error

Any ideas?

Hi Michael,

1. Create the stored procedure  in SQL Server  - since you wish to be 
have a
dynamic recordsource, I'm going to assume that it has parameters.
2. Create an ODBC connection to the SQL database.
3. In Access, create an SQL pass-through query.
a. Name: qrypt_spRun
b. EXEC spElements
c. ODBC Connect String:  per Step 2
4. The form's recordsource should be the qrypt_spRun query.

For more details see:

Microsoft Access Developer's Guide to SQL Server
Mary Chipman and Andy Baron
Chapter 10 - p444 to 495

This is the only reference I've found that covers the subject in any 
detail.
It has VBA code for dynamically modifying the pass through query that 
calls
the stored procedure.  The code could be modified to suit your needs.

I'm new to stored procedures - less than one month's experience - but 
this
book has helped me learn the subject.


Rand E. Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston, IL  60201
(xxx) xxx-xxxx

-----Original Message-----
From: Michael Mitchell [mailto:michael@m...]
Sent: Thursday, February 27, 2003 22:07
To: Access
Subject: [access] Dynamically set a forms recordsource to a stored 
procedure

I am trying to dynamicallys set a form's recordsource to a stored
procedure, but I can't get it to work.

I open the form with no bound record source, then I have a button that
sets it to a stored procedure.

I am using:

Forms!F_Elements.RecordSource =3D spElements

    The form opens but no records

Forms!F_Elements.RecordSource =3D "spElements"
    Brings up an error

Any ideas?

Good news bad news:

OK, I finally got records to show up on my sub form, after 3 hours of 
working on it, it was becasue my stored procedure did not have "Like" in 
it, that is why the use of the "%" did not work but the exact value did.  
I checked that first, but my changes must not have been saved, and after 
that I never went back to check on it because I knew I made the change.

The bad news is, now the sub form shows all the records that match and not 
just the ones that the main form is one, so my master and child links are 
not working.

Any suggestions on that?

> I am trying to dynamicallys set a form's recordsource to a stored 
p> rocedure, but I can't get it to work.

> I open the form with no bound record source, then I have a button that 
s> ets it to a stored procedure.

> I am using:  

> Forms!F_Elements.RecordSource = spElements 
 > 
 >    The form opens but no records

> Forms!F_Elements.RecordSource = "spElements" 
 >    Brings up an error

> Any ideas?