call stored procedure from ms access

[kevin]

I have written a stored procedure (sp) that calculates the number of
seats remaining for an event. I need to pass the event id to the sp.
I have a combo box that lists all the events. When I choose the event
from the combo box the event executes the sp and the 'Enter Parameter
Value' box appears. How can I execute this sp and pass the value to
it via vba and ms access and not have the input box appear?
Also, how do return the value from this sp to a form?
Any help is appreciated.
Thanks.

[Tony]

You said "the enter value parameter box appears" suggesting somewhere in
what you are doing there is a parameter query. If i understand your
question correctly you could replace the predicate in the parameter query
with a reference to the combo box that you mention.

eg. in you query make the predicate forms!formname!controlname where
"formname" is the name of the form you are working on and "controlname" is
the name of the combo box.

"kevin" <di*****@comcast.net> wrote in message
news:c4**************************@posting.google.c om...

I have written a stored procedure (sp) that calculates the number of
seats remaining for an event. I need to pass the event id to the sp.
I have a combo box that lists all the events. When I choose the event
from the combo box the event executes the sp and the 'Enter Parameter
Value' box appears. How can I execute this sp and pass the value to
it via vba and ms access and not have the input box appear?
Also, how do return the value from this sp to a form?
Any help is appreciated.
Thanks.

[Kevin Dickie]

Tony,Thanks for the reply.
Here is my existing sp code...
--------------------------------------------------------
ALTER PROCEDURE sp_SpotsRemaining2
(
@EventID int
)
AS
/* -- get maxsignup number
-- get total number of people signed up for specific event*/

declare @MaxSignup int
declare @EventCountStaff int
declare @EventCountStudent int
declare @EventCountNonStudent int
declare @SpotsRemaining int
declare @EventCount int
declare @EventCountTotal int

Set @EventCountStaff = 0
Set @EventCountStudent = 0
Set @EventCountNonStudent = 0

--get max number available for event
Set @MaxSignup = (SELECT dbo.Spess_tblCreateNewEvent.MaxSignup FROM
dbo.Spess_tblCreateNewEvent where
dbo.Spess_tblCreateNewEvent.EventID = @EventID)
--get number of spots remaining for all three categories
(Staff,Students,Non-Students)
Set @EventCountStaff = (Select count(*) FROM
dbo.Spess_tblEventSignup_Staff where
dbo.Spess_tblEventSignup_Staff.stfEventID = @EventID)

Set @EventCountStudent = (Select count(*) FROM
dbo.Spess_tblEventSignUp_Student where
dbo.Spess_tblEventSignUp_Student.EventID = @EventID)

Set @EventCountNonStudent = (Select count(*) FROM
dbo.Spess_tblEventSignup_NonStudent where
dbo.Spess_tblEventSignup_NonStudent.nonEventID = @EventID)
--calculate number of total spots filled
Set @EventCountTotal = @EventCountStaff + @EventCountStudent +
@EventCountNonStudent
--calculate number of spots remaining
Set @SpotsRemaining = @MaxSignup - @EventCountTotal
SELECT @SpotsRemaining

RETURN

------------------------------------------
If I understand correctly do I replace @EventID with
forms!formname!controlname?
Your example: forms!formname!controlname
becomes: forms!frmEventSignup!cmbstfEventID

Do I need to include a @ with my new predicate, can you insert the
correct code in the sp for me to show me how it should look. Also do you
know how to return the value from the sp into a form.
Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The RowSource of the ComboBox should be like this:

EXEC sp_SpotsRemaining2 Forms!frmEventSignup!cmbstfEventID

The control "cmbstfEventID" should not be the name of the ComboBox that
is calling the SP.

If the ComboBox is on the form "frmEventSignup" then you can shorten the
call to this:

EXEC sp_SpotsRemaining2 Form!cmbstfEventID

Here, "Form" means the current form.

Another version of the same call is:

EXEC sp_SpotsRemaining2 @EventID=Form!cmbstfEventID

You can get rid of the RETURN statement in the SP. Since it is the last
statement in the SP it isn't actually needed.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVG9/oechKqOuFEgEQIvlACgwwe4Ds9cbW1D8CFl80ZoSWx439UAoMw u
OGxpKpae6cg0SIsyU03SlC6o
=y1Ci
-----END PGP SIGNATURE-----
Kevin Dickie wrote:

Tony,Thanks for the reply.
Here is my existing sp code...
--------------------------------------------------------
ALTER PROCEDURE sp_SpotsRemaining2
(
@EventID int
)
AS
/* -- get maxsignup number
-- get total number of people signed up for specific event*/

declare @MaxSignup int
declare @EventCountStaff int
declare @EventCountStudent int
declare @EventCountNonStudent int
declare @SpotsRemaining int
declare @EventCount int
declare @EventCountTotal int

Set @EventCountStaff = 0
Set @EventCountStudent = 0
Set @EventCountNonStudent = 0

--get max number available for event
Set @MaxSignup = (SELECT dbo.Spess_tblCreateNewEvent.MaxSignup FROM
dbo.Spess_tblCreateNewEvent where
dbo.Spess_tblCreateNewEvent.EventID = @EventID)
--get number of spots remaining for all three categories
(Staff,Students,Non-Students)
Set @EventCountStaff = (Select count(*) FROM
dbo.Spess_tblEventSignup_Staff where
dbo.Spess_tblEventSignup_Staff.stfEventID = @EventID)

Set @EventCountStudent = (Select count(*) FROM
dbo.Spess_tblEventSignUp_Student where
dbo.Spess_tblEventSignUp_Student.EventID = @EventID)

Set @EventCountNonStudent = (Select count(*) FROM
dbo.Spess_tblEventSignup_NonStudent where
dbo.Spess_tblEventSignup_NonStudent.nonEventID = @EventID)
--calculate number of total spots filled
Set @EventCountTotal = @EventCountStaff + @EventCountStudent +
@EventCountNonStudent
--calculate number of spots remaining
Set @SpotsRemaining = @MaxSignup - @EventCountTotal
SELECT @SpotsRemaining

RETURN

------------------------------------------
If I understand correctly do I replace @EventID with
forms!formname!controlname?
Your example: forms!formname!controlname
becomes: forms!frmEventSignup!cmbstfEventID

Do I need to include a @ with my new predicate, can you insert the
correct code in the sp for me to show me how it should look. Also do you
know how to return the value from the sp into a form.
Thanks.