Pass-Through Query With Parameters

[TimboA]

I've created a module (called Parameter_Pass_Through) and added a function for a pass-through query to my Oracle DB as follows :-

Public Function ParamSPT(MyProcName As String, MyParam As String)

Dim MyDb As Database
Dim MyQ As QueryDef

Set MyDb = CurrentDb()
Set MyQ = MyDb.CreateQueryDef("&quot

MyQ.Connect = "ODBC;DSN=OracleDB;UID=scott;PWD=tiger;"
MyQ.ReturnsRecords = False
MyQ.SQL = "BEGIN SCHEMA." & MyProcName & "('" & MyParam & "'); END;"

MyQ.Execute
MyQ.Close
MyDb.Close

End Function

The idea is that I can reuse the code, passing the stored Oracle Stored Procedure name in as MyProcName and any parameters in as MyParam. I've tested this via the Immediate window using the following syntax :-

?paramspt("PT_PR_DD_CHECK","31-DEC-2001&quot

This works a treat !!

However, I now want to be able to run this exact same query from a command button on a form. I've set the form up with Text1 accepting the parameter info (i.e. 31-DEC-2001). The code behind the button looks like this :-

Private Sub Command0_Click()

Dim Response As VbMsgBoxResult

Parameter_Pass_Through.ParamSPT("PT_PR_DD_CHECK",Me.Text1)

Response = Msgbox("Report Created.", vbOKOnly)

End Sub

However, Access doesn't seem to like the Parameter_Pass_Through.ParamSPT("PT_PR_DD_CHECK",Me.Text1) line, giving the following error message :-

Compile Error. Expected: =

I've tried a few ways of getting round this but to no avail...any ideas ???

Thanks in Advance.

TimboA

 

[Taff07]

Initial look, I think you don`t require the 'Parameter_Pass_Through.' in the following line of code on your command button. Whether this is the only thing I couldn`t tell you

Parameter_Pass_Through.ParamSPT("PT_PR_DD_CHECK",Me.Text1)

Hope this helped

Ian