Prev: Join a table, queryfails with "Query input must contain at least one table or query
Next: XIRR Calculation
From: bp17 on 22 Oct 2009 11:41
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

From: Bob Barrows on 22 Oct 2009 12:02
bp17 wrote:
> 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.

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


From: Bob Frank Bob on 4 Nov 2009 21:32


"Bob Barrows" wrote:

> bp17 wrote:
> > 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.
>
> 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?
From: Bob Barrows on 4 Nov 2009 22:18
Bob Frank wrote:
>> I'm trying to achieve a similar goal (although I'm passing
>> parameters). What is the qryd variable type?

DAO.Querydef

But as I said earlier: you cannot pass parameters to a passthrough query.

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


From: Bob Frank on 5 Nov 2009 01:40
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.
 |  Next  |  Last
Pages: 1 2
Prev: Join a table, queryfails with "Query input must contain at least one table or query
Next: XIRR Calculation