Stored Procedure from Access Project

[Krystoff]

Hi all,

I am new to using Access projects and I am trying to write a stored procedure that will act as the recordsource for a form.

The procedure is called GetRecordSource.

Here is the code:

Create Procedure GetRecordsource
(
@DECustID int
)

As
SELECT *
FROM Data_Cust
WHERE CustID = @DECustID

The recordsource on my form is GetRecordSource.

The InputParameters section is this
@DECustID = Forms!getcustid

GetCustID is a field that is populated by a search form. When the user selects a record, the OnCurrent event populates the main form with the CustID of the current record and then rqueries the main form using forms!data_entry!requery.

Is there something I am doing wrong? Why isn't this working? Any help is appreciated.

Chris

 

[MrFurious]

What version of access are you using? How is access getting the data from SQL server, through a pass through query or some other means?

You should be able to just set up a linked table to the Data_Cust table in SQL server. After that you can use the linked table like any other access table.

-Mike

 

[Krystoff]

Hello Mike!

I am using Access 2000 and using the new feature they brought out called Access Projects. It hooks directly to a SQL server or an MSDE Database and in this case I am hooked directly to a SQL Server 7.0 database.

Access Projects are a little different that Access Databases. They have quite a bit of extra functionality in some areas and not as much in others. This seems to be what I need to get my multi user db up and running.

Chris

 

[SQLSister]

This is just a guess as I never used projects, but I think your problem is you are not executing the stored procedure anywhere. I doubt that requery executes a stored procedure but only an Access query. We did this kind of thing (before projects) by creating a passthrough query with the execute statement and changing the execute statement at run-time to update the variable. Hopefully Projects has a less awkward way to do it. Look in help under executing stored procedures and see what you find.