How to pass parameter from text box to stored procedure

[Twinprabu]

Hi,
I have created a command button called Reports in Access 2007 using wizard for generating a report between two dates.The stored procedure behind the button is spr_Reports(SQL server 2008):

ALTER PROCEDURE [dbo].[spr_Reports] 
    @start_date date,@end_date date
AS
BEGIN
    select convert(varchar(20),date,103)as Date,d.Planned,d.Comment 
    from tbl_Calendar e join tbl_Calendar_Details d 
    on e.Calendar_ID=d.Calendar_ID
    where Date between @start_date and @end_date order by Date asc
END

According to this procedure,If I click on Reports button it will prompt for start and end date.Instead of prompting I want to give input to the stored procedure from two text boxes called tboStartDate and tboEndDate.

The code behind the Reports button is:

Private Sub CmdReports_Click()
On Error GoTo Err_CmdReports_Click
 
    Dim stDocName As String
 
    stDocName = "dbo.spr_Reports"
    DoCmd.OpenStoredProcedure stDocName, acViewNormal, acEdit
 
Exit_CmdReports_Click:
    Exit Sub
 
Err_CmdReports_Click:
    MsgBox Err.Description
    Resume Exit_CmdReports_Click
 
End Sub

Can anyone please help me out of this?

[zmbd]

Stored procedures have no way of "seeing" the form's values.

The basic is that you will make a connection to the backend, and then use a pass thru to execute the stored procedure

execute Stored_procedure_Name 'Value1','Value2'

Read thru these two first so that you have a foundation to work from:
ACC: How to Simulate Parameters in an SQL Pass-Through Query

ACC: How to Return Values from SQL Stored Procedures

once you read those, then you should follow this thread...
how to pass values to a calling stored procedure
(really read the MS stuff first, then the Bytes thread, for some reason, once all three are read, it all makes sense - like a puzzle is suddenly assembled)

You might find this helpfull too:

• Microsoft Access / VBA Insights Sitemap/
• 106 Methods of Connecting to SQL Server

[edit{ok, one more article that I just found:
How to write VBA to SQL Server sub routine that calls a Stored procedure

[Twinprabu]

Hi,
Actually am new to VBA. I know nothing about VBA. In my project all the applications(.adp) are using Access 2007 as front end and SQL server 2008 as back end.In one of the applications it seems stored procedure directly fetched the input from a text box.The code is

strRowSource = "EXEC dbo.spr_Find_Project_by_Number '" & Me.tboProjectNo & "'"
    Me.lboOutputDatabaseProjects.RowSource = strRowSource
    Me.lboOutputDatabaseProjects.Requery

Can you please make me clear on this?

[zmbd]

Twinprabu
Post #3 is a different question than what you orginally asked. Please start a new thread. WHen you do, you'll need to provide a tad more information.

(helpful hint: When formating text using the [CODE/] button, select/highlight the section of text first. Then click on the button. The text should then be between one set of the [code] [/code] tags.)