Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: Stored Prcedure - Parameter Valu from from

    Hi,
    I can't figure out how to pass a value from a from in an Access Project to a stored procedure:

    CREATE PROCEDURE [dbo].[SP_CompPerson]
    @PID int=[Form]![frmPersonContact]![PersonID]
    As
    SELECT * from tblCompany_Person
    where PersonID=@PID
    GO

    Does any body know how to do this? It worked with Access, but it seems that the stored procedure won't get the
    [Form]![frmPersonContact]![PersonID]

    value from the form.

  2. #2
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69
    Hi,
    there are two options:
    a) bound form/report
    create procedure sp_foo (@param int) as
    select * from table tbl_bar where field = @param
    go

    and then set the Input Parameter Property of the Form/Report to
    @param int = forms!yourform!yourfield (or reports!yourreport!yourfield)

    where yourform is not necessarily the bound form itself

    b) for list fields etc.
    create procedure sp_foo (@param int) as
    select * from table tbl_bar where field = @param
    go

    where you have a field on the *same* form named param (that is the name of the parameter without @)

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    How can I thank you?!!!
    How did you figure this out!!!!


    Originally posted by chrisp_999
    Hi,
    there are two options:
    a) bound form/report
    create procedure sp_foo (@param int) as
    select * from table tbl_bar where field = @param
    go

    and then set the Input Parameter Property of the Form/Report to
    @param int = forms!yourform!yourfield (or reports!yourreport!yourfield)

    where yourform is not necessarily the bound form itself

    b) for list fields etc.
    create procedure sp_foo (@param int) as
    select * from table tbl_bar where field = @param
    go

    where you have a field on the *same* form named param (that is the name of the parameter without @)

  4. #4
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69
    I used "Microsoft Access Projekte mit SQL Server 7.0/2000" (Microsoft Press Germany, ISBN 3-86063-614-6) as a starting point and found it *very* helpful. Don't know if it's available in english, though.

    BTW, there's one very annoying bug regarding the input parameter property (acess 2002): when set in the properties window it will get lost every time you access the stored procedure source code via the properties window (the [...]-button). You should modify stored procedures only from the database window to avoid that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •