Get specific record in table (not first location)...

[AT76]

Hi all, I'm trying to return the Initials of a SalesPerson Name chosen from a ComboBox (SalesAdminID) in Access. I've been able to successfully output the Initials but it ALWAYS returns the 1st location of tbl_SalesAdmin no matter what is chosen from the SalesPerson Name ComboBox. Here's the code I have so far. Could I get any recommendations as to how I can get the correct Initials depending from what Name is chosen from the ComboBox? Thank you in advance for your help.

Here's the code I have so far... -VBARocks

Function FindSalesAdmin()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rec As DAO.Recordset
Dim rsVal As String
Dim LSQL As String
Dim LAdmin As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_Quote", dbOpenDynaset)

rs.MoveFirst
LSQL = "select Initials from tbl_SalesAdmin"

Set Lrs = db.OpenRecordset(LSQL)

'Retrieve value if data is found

If Lrs.EOF = False Then
LAdmin = Lrs("Initials")

Else
LAdmin = "Not found"
End If

FindSalesAdmin = (LAdmin)

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function

 

[drctx]

you could use the dlookup function and replace all your code with this:
FindSalesAdmin = DLookup("Initials", "tbl_SalesAdmin", _
"SalesNameFIELD = SalesAdminID")

or
change your select statement to have WHERE.

LSQL = "select Initials from tbl_SalesAdmin WHEERE SalesNameFIELD = SalesAdminID;

 

[AT76]

Thanks drctx!! It worked. AWESOME!

You saved my night!