using sql server function

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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.
 
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 -
Click to expand...

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
 
Back
Top