using sql server function

[John]

I am trying to use a sql server 2005 scalar-valued function to return
a decimal that will be used to determine if a person is over or under
their authority limit. Below is the what I have so far but keep
getting the error 'Undefined function dbo.POLimitGetFromEmployeeID in
expression.' Debugging stops on the 'sql =' line. The gUserID (int)
is what is used to determine who is signed into the Access2007 app.

Private Sub TabCtl30_Change()
Form!zPurchaseOrderItems.Form.Requery

Dim rst As ADODB.Recordset
Dim sql As String
Set rst = New ADODB.Recordset
sql = "SELECT dbo.POLimitGetFromEmployeeID(gUserID)"
rst.Open sql, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
rst.Close
Set rst = Nothing

End Sub

The above lines are in a the tab change event. There will eventually
be other code written here to complete the process. Thought about
references but there are no references with MISSING on them. Is there
a particular reference that should be checked? Is there something in
the code above that I should have but missed?

Thanks....John

 

[Douglas J Steele]

Access and VBA know nothing about SQL Server functions. The only way you'll
be able to use a SQL Server function is to run the query on the server. That
means you need the appropriate connection, not CurrentProject.Connection.

 

[John]

Access and VBA know nothing about SQL Server functions. The only way you'll
be able to use a SQL Server function is to run the query on the server. That
means you need the appropriate connection, not CurrentProject.Connection.











- Show quoted text -

I discovered that shortly after I sent the posting. And got to fill a
textbox with the result and able to uset the rst in an IF statement.
Have been slowly adding items as I need them. The code (so far) is
below. Some is also just for testing.

Dim rst As ADODB.Recordset
Dim sql As String
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Driver={SQL Server};Server=MXSQL;Database=MTXMain;User
ID=sa;Password=h0rn3t;"
sql = "SELECT dbo.POLimitGetFromEmployeeID(" & gUserID & ")"
Set rst = New ADODB.Recordset
rst.Open sql, conn, adOpenKeyset, adLockReadOnly

If Me.Subtotal.Value > rst(0) Then
Me.lblPONotApproved.Visible = True
Else
End If

Me.Text108 = rst(0)

rst.Close
Set rst = Nothing
Set conn = Nothing

Thanks...John