Access data from SQL Server

[Guest]

Hi,

I am using Access as a front end GUI to retrieve data from a database on an
SQL server.

I have created stored procedures that work fine, but now I want to be able
to parameterise them from a form.

I am having great difficulty in getting information on how to reference a
control on a form into a stored procedure.

I have a combo box that will contain the value I wish to pass to the stored
procedure.

The query works fine if I run it by it's self, but cannot seem to get it
going from a form.

Stored Procedure:
ALTER PROCEDURE dbo.StoredProcedure1
(@Assignee varchar(25))
AS SELECT Call, Status, Assignee, LoggedBy, DateRaised, DateCompleted,
Request, Action, SysDateChanged
FROM dbo.SKYSQL02_Envisage_dbo_HD_Call
WHERE (Assignee = @Assignee) AND (NOT (Status IN ('completed',
'Decline/Cancel')))
ORDER BY DateRaised DESC


Also how do I refresh the sub form that I have used to display the data with?

Thanks in advance.

 

[Ron Weiner]

I assume you are working on an mdb and that you want to use the SPROC as the
RowSource for the combo.

What you will need to do is to create a PassThru query in your Access Front
End Database. The Sql for that PassThru would be something like:

Execute StoredProcedure1 Forms!YourFormName.txtAsignee.Value

Then add a textbox whose name is txtAsignee (can be invisible) to your form.
You are responsible to populate the text box with the Assignee name in the
forms load event, then set the RowSource of the combo the PassThru query and
you should be good to go.

Another method for access 2003 (perhaps 2002 too) would to write a bunch of
code to open and iterate a recordset based on the SPROC and an use
cboxxx.AddItem to populate the combo. Lots less work to bind the cbo to the
PassThru query though. This will NOT work for access version 2000 and
earlier as combos do not have the AddItem method.