DAO.Recordset

[dbay]

Looking at this code. Is there a better way of doing this?

Function SET_Current_User(ByVal STR_User As String) As String
Dim STR_SQL As String

Dim DB As DAO.Database
Dim RS As DAO.Recordset
STR_SQL = "SELECT TBL_Employee_Info.EMP_First_Name, TBL_Employee_Info.EMP_Last_Name, TBL_Employee_Info.EMP_ID_Number, TBL_Employee_Info.EMP_User_Name, " & _
"TBL_Employee_Info.EMP_User_Level FROM TBL_Employee_Info WHERE TBL_Employee_Info.EMP_User_Name = '" & STR_User & "';"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(STR_SQL, dbOpenSnapshot, dbReadOnly)

If Not RS.BOF Then
If RS(0) <> "" Then Current_User_First_Name = RS(0)
If RS(1) <> "" Then Current_User_Last_Name = RS(1)
If RS(2) <> "" Then Current_User_Number = RS(2)
If RS(3) <> "" Then Current_User_Name = RS(3)
If RS(4) <> "" Then Current_User_Level = RS(4)
End If

RS.Close
Set RS = Nothing
DB.Close
Set DB = Nothing

End Function

 

[MarkK]

I wouldn't declare a database variable, I would just use CurrentDb.
I would not put "Info" in the name of a table. I presume all tables to contain "Info".
You don't need to prefix each fieldname in the query with the name of the table in a query where there is only one table.
Doing this test seems pointless...

If RS(0) <> "" Then FirstName = RS(0)

If FirstName is declared as a string somewhere it is initialized with a value of "". If when RS(0) = "" you don't make the assignment to FirstName, this makes no difference at all.
This routine is a function that returns a string, but you never assign it a value, so it might as well be a sub.
Mark

 

[dbay]

I wouldn't declare a database variable, I would just use CurrentDb.
I would not put "Info" in the name of a table. I presume all tables to contain "Info".
You don't need to prefix each fieldname in the query with the name of the table in a query where there is only one table.
Doing this test seems pointless...

If RS(0) <> "" Then FirstName = RS(0)

If FirstName is declared as a string somewhere it is initialized with a value of "". If when RS(0) = "" you don't make the assignment to FirstName, this makes no difference at all.
This routine is a function that returns a string, but you never assign it a value, so it might as well be a sub.
Mark

Thanks for the input lagbolt.
I put this line

If RS(0) <> "" Then FirstName = RS(0)

in because if the Table does not contain info in that Field, it will through an error. You are right about the Table name containing "Info". This will change later on in the database design. Right now it is in its rough format. Just trying to get as many opinions on the best way to deal with Recordsets. As far as the SQL statements, I usualy just create a Query to do what I want and copy it.