Cannot reference parameter using form

[Rachel]

Hi,
I have the following stored procedure in sql server 2000 and I am
trying to use text boses on a form (adp) to pass parameters to this
stored procedure. I then want to use the temp table created for a
report. I have named the text boxes on the form: s_date and e_date and
set the control source to =myproc2 however it doesnt work and i get
this error: #name?), in the text boxes in form view.

ALTER procedure myproc2
@s_date datetime, @e_date datetime
as
begin
delete from temp

insert into temp SELECT Client_ID,[Quit Date], Quit, Lastname
FROM tbl_Client
where [quit date] between @s_date and @e_date
end

Any help is greatly appreciated

Rachel

 

[Sylvain Lafontaine]

You might have problem to access a temp table created by a form from a
report. This is because Access is using three connections and sometime a
fourth but temp tables are strongly associated with a connection. (Instead
of a local temp table #temp, you could use a global temp table ##temp but of
course, this will probably gives you other kinds of problem.).

It would be better if this temp table was created directly from the SP used
as the record source of the report (instead of a form calling the report).

For your parameters passing problem, the best way is to either build a full
sql string calling the SP using an EXEC statement or use the InputParameters
property. Both of these two solutions have been explicited many times in
this newsgroup. Take a look with Google for Groups:

http://groups.google.com/groups?q=inputparameters&start=0&scoring=d&ie=UTF-8&oe=UTF-8&

 

[Graham R Seach]

Rachel,

myproc2 is an input procedure and has no outputs, so setting it as a control
source is pointless. Also, I can't see where you're passing the textbox
values to the procedure.

1. Make sure ADO is referenced.
a). Open any code module.
b). From the Tools menu, click References.
c). Make sure Microsoft ActiveX Data Objects 2.x Library is ticked.
d). Click OK.

2. Create the following form procedure:
Private Sub GetDates()
Dim cmd As ADODB.Command

If IsDate(Me!s_date) And IsDate(Me!e_date) Then
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = strmyconnection
.CommandText = "myproc2"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@s_date", adDate,
adParamInput, , Me!s_date)
.Parameters.Append .CreateParameter("@e_date", adDate,
adParamInput, , Me!e_date)
.Execute
End With

Set cmd = Nothing
End If
End Sub

3. Add the following code to each textbox's AfterUpdate event:
GetDates

4. Add the following code to the form's Current event:
GetDates

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia