Problems setting a paramater for SQL stores procedure with code.

[TomDuCrosbie]

I have a command button I want to send the contents of a form, as a report. I've written this code to do it.

Private Sub ResultsOK_Click()
On Error GoTo Err_ResultsOK_Click

Dim qdef As QueryDef
Dim strSQL As String
Dim strParam As String

Set qdef = CurrentDb().QueryDefs("ReferralResultsMailReport&quot

strParam = Forms!frmReferralResults!txtReferralID
strSQL = "Exec ReferralResultsMailReport" & strParam
qdef.SQL = strSQL

Dim stDocName, strSubject, strSendTo, strCC, strBody As String

stDocName = "rptReferralResultsforMail"
stSubject = "Results of your Referral"
stSendTo = Me!ReferredBy
strCC = "Tom Petersen"
stBody = Me!txtFirstName & " " & Me!txtLastName

DoCmd.SendObject acReport, stDocName, acFormatRTF, stSendTo, , stCC, stSubject, stBody

The problem is, I get a runtime error "91" Object variable or with block variable not set. I get the error on this line
Set qdef = CurrentDb().QueryDefs("ReferralResultsMailReport&quot

What does this mean? THe database is a access front end with MS SQL server back end,and ReffealResultsMailReport is a stored procedure. I also have the DAO 3.6 object library attached.

 

[vbajock]

My Dao is a little rusty, but try adding

Dim db as DAO.database

set db=CurrentDb

change:
Set qdef = CurrentDb().QueryDefs("ReferralResultsMailReport&quot

to:
Set qdef = db.QueryDefs("ReferralResultsMailReport&quot

 

[TomDuCrosbie]

I'm still geting the same error on the same line. It sounds like one of the variables I've declared isn't set. any idea which one I have to set to something?

 

[GComyn]

when you said that the "ReferralResultsMailReport" is a storied procedure, did you mean it was a stored query?

I use DAO all the time, and the SQL statement that you used above is not a correct sql statement for access (that I've ever seen).

More information about the 'procedure' would be helpful.

GComyn

 

[TomDuCrosbie]

I have upsized the DB to MS SQL. In the process, it changed all my queries in access to either View's or Stored Procedures.

 

[GComyn]

Oh... Ok... well.. I can't help you... I have never used MS SQL at all... so I don't know how to access them....

Maybe someone else "listening in" will be able to help you....

GComyn

 

[cmmrfrds]

Can you explain this a little further.
"I have a command button I want to send the contents of a form, as a report"

Do you want the stored procedure as the record source for a Form or Report, or some other need?

 

[DaltonVB]

is ReferralResultsMailReport setup as a passthrough query to MySQL?

 

[TomDuCrosbie]

The stored procedure ReferralResultsMailReport is a passthrough query to MS SQL Server 2000 and it is the record source for the report. I'm trying to have the input ReferralID to pass to the querry so that it only pulls the info that is showing on the form

 

[cmmrfrds]

What you have should work. I assume the passthrough query name is the record source for the report.

Set qdef = CurrentDb().QueryDefs("ReferralResultsMailReport&quot
strParam = Forms!frmReferralResults!txtReferralID
strSQL = "Exec ReferralResultsMailReport " & strParam
qdef.SQL = strSQL

Probably put a space so that the parm does not get to be part of the Sproc name.

Set a reference to the DAO library.

 

[TomDuCrosbie]

I did double check and the querry is the record source for the report. And I also have MS DAO 3.6 object library already connecrted as a referance.

 

[cmmrfrds]

Usually the problem you described is due to a missing reference.

Put this function in a standard module.

Function FixUpRefs()
Dim loRef As Access.Reference
Dim intCount As Integer
Dim intX As Integer
Dim blnBroke As Boolean
Dim strPath As String

On Error Resume Next

'Count the number of references in the database
intCount = Access.References.Count

'Loop through each reference in the database
'and determine if the reference is broken.
'If it is broken, remove the Reference and add it back.
Debug.Print "----------------- References found -----------------------"

For intX = intCount To 1 Step -1
Set loRef = Access.References(intX)
With loRef
''-Debug.Print " reference = "; .FullPath
blnBroke = .IsBroken
Debug.Print "broken = "; blnBroke; " "; "path = "; .FullPath
If blnBroke = True Or Err <> 0 Then
strPath = .FullPath
Debug.Print &quot; ***** the previous reference was broken ******&quot;
With Access.References
.Remove loRef
.AddFromFile strPath
End With
End If
End With
Next

Set loRef = Nothing

' Call a hidden SysCmd to automatically compile/save all modules.
Call SysCmd(504, 16483)
End Function

If you have an AutoExec Macro then add a line to run the above function. In the Macro runcode and FixUpRefs()

Do a &quot;Control G&quot; to bring up the debug window. The references will be printed in the window. Copy and paste here so we can see what references are set.

 

[vbajock]

Gcomyn:
I'm not much of a DAO guy but are you saying that

Set qdef = CurrentDb().QueryDef(&quot;ReferralResultsMailReport&quot

should instead be Set qdef = CurrentDb().QueryDef(&quot;Select * FROM sometbl&quot
?

 

[TomDuCrosbie]

Here's what it came up with


----------------- References found -----------------------
broken = False path = C:\Program Files\Common Files\System\ADO\msado21.tlb
broken = False path = C:\WINNT\system32\stdole2.tlb
broken = False path = C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
broken = False path = C:\Program Files\Microsoft Office\Office\MSACC9.OLB
broken = False path = C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL

 

[GTLoco99]

strParam = Forms!frmReferralResults!txtReferralID


This is not declared in your code anywhere, that is your problem

You need this at at the start of your code

Dim strParam As String

Thanks
GTLoco

 

[oharab]

GT, sorry to rain on your parade, but strParam is defined at the beginning.

Again, I've never used SQL Server before, but here is what I think:
You are trying to use a stored procedure as a record source for a report.
To call a stored procedure you use the syntax

Exec ReferralResultsMailReport 59

in a pass thru query where 59 is the parameter for the stored procedure.

Your problem is here:
strSQL = &quot;Exec ReferralResultsMailReport&quot; & strParam

if you pass the parameter 59 to this, you get
strSQL = &quot;Exec ReferralResultsMailReport59&quot;
which means the query is trying to run a stored proc that doesn't work. Put the space in & you should be away.

strSQL = &quot;Exec ReferralResultsMailReport &quot; & strParam

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[TomDuCrosbie]

Unfortunalty the space leaves me with the same error.

 

[oharab]

Try this:
Dim qdef As DAO.QueryDef
Dim strSQL As String
Dim strParam As String

Set qdef = CurrentDb.QueryDefs(&quot;ReferralResultsMailReport&quot
and see if that works,

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[GTLoco99]

Bah, i missed it, and i read over the code like three times too

GTLoco

 

[TomDuCrosbie]

sory o'hara, same thing object variable or with block variable not set