changing parameters on a stored procedure

  • Thread starter Thread starter bp17
  • Start date Start date
B

bp17

I have a stored procedure that does not return records but needs two
parameters to run
@ SAP_code & @Source
@SAP_code = interger @source = varchar
I can run the SP by inputting the parameters in the SQL view
going into queries desgin view and clicking on "pass-through".
I would like create a form with text feilds where this data can be updated
from using the "enter" event.
I have tried using the following code.

Private Sub Text9_Enter()
Set qryd = dbs.QueryDefs("fm.uspDelForcast")
qryd.Parameters("@sap_code") = Me!Text7
qryd.Parameters("@Source") = Me!Text9
qryd.Execute


End Sub

but I get an "object required error" on the Set qryd line.
My VBA coding is weak at best, am I even on the right track?
I have two more SP's with the same problem but they both return records and
have 4 seperate required params, I am hoping the code would be similar as
long as I set the connection string to return records = true

Thanks
BP17
 
bp17 said:
I have a stored procedure that does not return records but needs two
parameters to run
@ SAP_code & @Source
@SAP_code = interger @source = varchar
I can run the SP by inputting the parameters in the SQL view
going into queries desgin view and clicking on "pass-through".
I would like create a form with text feilds where this data can be
updated from using the "enter" event.
I have tried using the following code.

Private Sub Text9_Enter()
Set qryd = dbs.QueryDefs("fm.uspDelForcast")
qryd.Parameters("@sap_code") = Me!Text7
qryd.Parameters("@Source") = Me!Text9
qryd.Execute


End Sub

but I get an "object required error" on the Set qryd line.
Click to expand...

That's because you did not define and instantiate the dbs variable:

dim dbs as database
set dbs=currentdb

A passthrough query cannot accept/pass parameters. It can only take the
statement that is entered in SQL View and pass it to the remote database
to be executed.. What your code needs to do is alter that sql statement
using the textbox values. You do that using the querydef's SQL property:

Set qryd = dbs.QueryDefs("fm.uspDelForcast")
qryd.SQL = "exec procname @sap_code=" & Me!Text7 & _
", @Source='" & Replace(Me!Text9,"'","''") & "'"
qryd.execute

I used the Replace function to escape any apostrophes entered into Text9
by the user. "Escaping" is how one causes characters with special
meaning (such as quotes) to be used as literals within a string. In both
VBA and T-SQL, characters are escaped by doubling them. So, in the event
that Text9 contains an apostrophe, I use Replace to escape that
apostrophe so it will be included with the string passed to the
procedure's argument rather than mistakenly ending the string's
definition.
 
Bob Barrows said:
That's because you did not define and instantiate the dbs variable:

dim dbs as database
set dbs=currentdb

A passthrough query cannot accept/pass parameters. It can only take the
statement that is entered in SQL View and pass it to the remote database
to be executed.. What your code needs to do is alter that sql statement
using the textbox values. You do that using the querydef's SQL property:

Set qryd = dbs.QueryDefs("fm.uspDelForcast")
qryd.SQL = "exec procname @sap_code=" & Me!Text7 & _
", @Source='" & Replace(Me!Text9,"'","''") & "'"
qryd.execute

I used the Replace function to escape any apostrophes entered into Text9
by the user. "Escaping" is how one causes characters with special
meaning (such as quotes) to be used as literals within a string. In both
VBA and T-SQL, characters are escaped by doubling them. So, in the event
that Text9 contains an apostrophe, I use Replace to escape that
apostrophe so it will be included with the string passed to the
procedure's argument rather than mistakenly ending the string's
definition.

--
HTH,
Bob Barrows


.
I'm trying to achieve a similar goal (although I'm passing parameters). What is the qryd variable type?
Click to expand...
 
DAO.Querydef

But as I said earlier: you cannot pass parameters to a passthrough query.
 
So I found out the variable type. I had to reference MS DAO first then
declare the variables as database, querydef, etc.

The problem is I'm still getting an "Object variable or With block not set"
error.
 
Bob said:
So I found out the variable type. I had to reference MS DAO first then
declare the variables as database, querydef, etc.

The problem is I'm still getting an "Object variable or With block
not set" error.
Click to expand...

Well, you certainly have my sympathies. Do you want help with it?

Hint: I'm not standing behind you looking at your code over your shoulder
:-)

Did you forget to use the Set keyword to assign an object to a variable?

PS. I'll be happy to help you here, but coding issues really belong in one
of the VBA groups in the Access hierarchy.
 
Back
Top