By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Customize Prompt Message for Stored Procedure from Access Form

P: 3
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.Invoice_Date)>=@begindate And (Sales_Detail.Invoice_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(@begindate, @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_Click()

Dim qd As QueryDef

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



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



but it does not work...
May 18 '06 #1
Share this Question
Share on Google+
3 Replies


100+
P: 135
Using your method, just use two variables to hold the dates then use input boxes to retrieve the values

Expand|Select|Wrap|Line Numbers
  1. Dim dStartDate as Date
  2.  
  3. dStartDate = Inputbox("Enter a starting date", "Start Date")
  4.  
  5.  
then plug it in.
May 18 '06 #2

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

Expand|Select|Wrap|Line Numbers
  1. Dim dStartDate as Date
  2.  
  3. dStartDate = Inputbox("Enter a starting date", "Start Date")
  4.  
  5.  
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?
May 18 '06 #3

P: 3
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?
May 18 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.