Function with SQL

[CaptainBob007]

Hi -

This probably simple, but I can't figure it out for the life of me.

I'm attempting to add some user-level security to a database I'm writing, and I have a table

tblUsers

to hold this information. One of the fields in that table is

UserLevel

, which is the access level of the user. The name of the current user is stored as the public variable

CurrUser

.

Anyhow, I am trying to write a function

GetUserLevel()

to take the user name from

CurrUser

and return

UserLevel

. I can only assume this would require an SQL query, and I know how to word it, but I cant figure out how to get the result of the query (which must be unique as

CurrUser

is the primary key of

tblUsers

.

The query I have come up with is as follows:

DoCmd.RunSQL "select UserLevel from tblUsers where UserID = " & CurrUser & ";"

There's gotta be something I'm missing here. Any help would be appreciated!

~Bob

 

[SQLBI]

Bob,

Here's how i would deal with this using ADO, assuming that your GetUserLevel function returns a Long datatype.

Public Function GetUserLevel() As Long

Dim strSQL As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
dim rstArray As Variant

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

strSQL = "SELECT UserLevel FROM tblUsers WHERE UserID = " & CurrUser & ";"

rst.Open strSQL, cnn, adOpenKeyset

If rst.RecordCount > 0 Then

rstArray = rst.GetRows

GetUserLevel = rstArray(0, 0)

End If

Set cnn = Nothing
Set rst = Nothing

End Function

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk