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.

 

[oharab]

Does the query ReferralResultsMailReport actually exist in the access database?

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

http://www.RobotParade.co.uk

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

 

[TomDuCrosbie]

I've been banging my head against this for about a week so I'm going to ramble for a minute to clear my thoughts. Maybee it'll help point someone in the correct direction.

I used the upsizing wizard to convert the backend to MS SQL server 2000. The wizard converted the queries I had in access to the new access front end in the form of stored procedures. I guess i'm assuming it's a pass through query as the tables don't acutally exist on the access front end. The DAO programming worked prior to the conversion. (prior to the conversion the tables were in a seporate access back end) This is the first time I've attempted to upsize to SQL. So I'm learning as I go forward. I havent' been able to find anything that has said that everything is on the server and I'm just looking at immages in the access front end so again I'm assuming that the queries are on the access front end as that's where I'm doing all the editing.

 

[oharab]

On the queries tab of access, to you have a query called ReferralResultsMailReport?

Your code is trying to open that query in the current database and make some changes to it. Try this:

Private Sub ResultsOK_Click()
On Error GoTo Err_ResultsOK_Click

Dim qdef As DAO.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

..OTHER STUFF...

Err_ResultsExit:
set qdef=nothing
exit sub

Err_ResultsOK_Click:
select case err
case 91
set qdef=currentdb.createQuerydef("ReferralResultsMailReport&quot
resume next
case else
msgbox "An Error Occurred"
resume Err_ResultsExit


end select

end sub


The modification in the error traps the error 91, then creates a query with the right name so the report can use it.



Give is a go.

B

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

http://www.RobotParade.co.uk

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

 

[TomDuCrosbie]

Ok, I have a question. In stead of assuming that everyting is on the access front end, how would I change it so that instead of using CurrentDb I'm calling the SQL DB?

 

[oharab]

I suppose that you could set the rowsource of the report to a passthrough query.

(make backups before you do this, you could lose your report completely)

Open your report in design view, delete all the record source, then click the button with the 3 dots on it to open a blank query.

Choose Query->SQL Specific->Pass-Through from the menu & you will be left with a clear SQL entry screen. Type
Exec ReferralResultsMailReport param
but change param to a parameter you know works.

Try the report now.

Hopefully it should run properly.

Now clear the record source option again and go to the onOpen event of the report.
In the event put:
Me.RecordSource="Exec ReferralResultsMailReport " & Forms!frmReferralResults!txtReferralID


Hopefully this should run.

hth

Ben

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

http://www.RobotParade.co.uk

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

 

[cmmrfrds]

I am glad you gave the explanation so it is a little clearer where you are coming from. I assume from what you have indicated that in the upsizing you selected an Access MDB and not an ADP or Project.

An Access Project is the best way to go with sql server if possible. You can connect directly to the database and work with the database objects - no need for ODBC. Also, the Access Forms use ADO recordset objects etc.. On an Access MDB the Form recordset objects are DAO this prevents setting ADO recordsets directly to the Form.

In an MDB you can use the code originally discussed just make sure as orahab points out that you have made a pass-through query that executes the stored procedure.

This needs to be the name of a pass-through query which can be the same name as the SP if you want.

Set qdef = CurrentDb.QueryDefs("ReferralResultsMailReport&quot

The wizard probably would not have made the name for you as a pass-though, but it should have made a stored procedure on sql server with the name you indicated.

Because you are using an MDB you need to execute the stored procedure through a pass-through query in order to be the record source on a Form.