473,237 Members | 1,223 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

Customize Prompt Message for Stored Procedure from Access Form

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
3 5504
CaptainD
135 100+
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
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
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

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

Similar topics

1
by: newman | last post by:
Can anyone help me with code to do the following I have a stored procedure that requires a date. What I want to do is to write an application that runs that stored procedure and prompts the user...
6
by: ndn_24_7 | last post by:
Hello all, I have a stored procedure that prompts the user for beginning date and ending date to run a monthly report. The prompt says Enter_Beginning_Date and Enter_Ending_Date. I want the prompt...
4
by: Robin Tucker | last post by:
Hi, I'm trying to determine with my program whether or not a given database supports a given feature set. To do this I'm querying for certain stored procedures in the sysobjects table and if...
3
by: Ryan.Chowdhury | last post by:
This is a general question regarding the use of view and stored procedures. I'm fairly new to databases and SQL. I've created a SQL database using an Access Data Project ("ADP") and I'm...
1
by: Lauren Quantrell | last post by:
I have read the newsgroups and see this is a common issue but I saw no resolution for it: I have an Access2K frotn end and SQL Server 2K backend. In access, I create a temp table using code in a...
6
by: fumanchu | last post by:
I've got to let end users (really just one person) load billing batch files into a third party app table. They need to specify the billing cycle name, the batch name, and the input file name and...
10
by: Eric E | last post by:
Hi all, I am using an Access client linked to a PG 7.4 server via ODBC. I have a stored proc on the server that inserts rows into a table.particular table, accomplished via an INSERT within the...
3
by: bala | last post by:
Hi Gurus The scenario A MS Access frontend application with Oracle Backend (Linked Tables). The Database UserID and password is not stored and each user has a unique UserID and password. There...
3
by: Darkside12 | last post by:
Hi, I'm trying to build a dynamic query by form. The idea is that a user can select a table in the database via a combo box and this will then change all of the text box labels on the form to...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.