Retreiving Data from a recordset using Access VBA

[Garfield]

Hello,

Please can you help with my very simple problem.

I want to create a general function GetName() that can be used throughout my
Access application.
The function will return the First and Last name as one string into a
textbox
I'm passing in the UserID
I'm trying to use an SQL statement to get to the record I want but I keep on
getting 'type mis-match' error

Here is the code


Public Function GetName(userid as integer) As String
Dim rstUser as Recordset
Dim db as Database
Dim strSql as String
Dim UserName as String

Set strSql = "SELECT Title, FirstName,LastName From Users WHERE UserID
=" & userid
Set db = CurrentDb
Set rstUser = db.OpenRecordset(strSql)

Set UserName = rstUser(0) & " " & rstUser(1) & " " & rstUser(2)
GetName = UserName
rstUser.Close

End Function

 

[CSmith]

Hi,

This is really a DAO question, not ADO.

Change "Set strSql =" and "Set UserName =" to "strSql =" and "UserName =" in
your code. In fact, you may as well clean up your code and rename UserName
to strUserName, while you're at it.

Also, if UserID is a text column in your Users table, then that will cause
the type-mismatch since you're not surrounding your userid variable with
single quotes in your SQL string. If UserID is a numeric column in your
Users table, then your code is okay.