Customize Prompt Message for Stored Procedure from Access Form

[rsbutterfly16]

hi all, I have a front end access database (mdb) with a sql server backend for my
tables. I have an form in which I have created a store procedure in sql
server, and i have a form created and also a sql pass thru query. I am not sure yet of which is better for the parameters. can someone tell me the steps to do this?

my form is in access, what i did was i created a
sql pass through query that exec the procedure and it works fine but i know
there has to be an easier way just to link the access form directly to the sp and prompt for the parameters I need?

this is part of my stored procedure created in sql server for my existing form.
CREATE PROC myReport (@begindate smalldatetime, @enddate smalldatetime)
WHERE (Sales_Detail.I nvoice_Date)>=@ begindate And (Sales_Detail.I nvoice_Date)
<= @enddate

so when i do the sql pass through query to test it i put dates for the input
parameters. for example:

exec myreport '05/09/2005', '05/09/2006',

and it runs myReport with those parameters; however, I would like the user to
be prompt to enter these dates(@begindat e, @enddate), how can i put that in
the sql pass through query? or maybe directly on the form. ?


I tried doing a vb script when adding a command in the form:


Private Sub cmdDateRange_Cl ick()

Dim qd As QueryDef

Set qd = db.QueryDefs("I nvoice Detail Inquiry SQL")



qd.Parameters("[Please Enter a Date]")="& Me.begindate & " And (["Please Enter End Date]") = "&Me.Enddat e & ""



but it does not work...

[CaptainD]

Using your method, just use two variables to hold the dates then use input boxes to retrieve the values

Dim dStartDate as Date

dStartDate = Inputbox("Enter a starting date", "Start Date")

then plug it in.

[rsbutterfly16]

Using your method, just use two variables to hold the dates then use input boxes to retrieve the values

Dim dStartDate as Date

dStartDate = Inputbox("Enter a starting date", "Start Date")

then plug it in.

thanks for the help, in my sp in sql server i already declared the variables, do i have to do this in my access form also?
I am a little confused... where do i put that code?

[rsbutterfly16]

thanks, i though i had already declared my variables as you can see in my stored procedure in sql server... i am a little bit confused where exactly do i put that code?