lookup returning index number instead of value

[werafa]

Hi all,

I am trying to read a value using vba from a external linked table - where the value is the result of a combobox.
The field displays a three letter code, (eg. "MCG"), but is based on a lookup with row source = "SELECT Organisations.[ID], Organisations.[OrgCode] FROM Organisations; "

my function below returns the index number of the record rather than the code. How do I return the value displayed in the table?

Thanks
Werafa



Function GetOrg(myPerson As String) As String
'return the correct org code for myperson or return N/A
Dim rs As DAO.Recordset
Dim myOrg As String


Set rs = CurrentDb.OpenRecordset("Select * FROM Staff")


If Not (rs.EOF And rs.BOF) Then 'if recordset contains rows
rs.MoveFirst 'force start at first row
Do Until rs.EOF = True
If rs("cName") = myPerson Then
myOrg = rs("lOrg")
End If
If myOrg <> "" Then Exit Do
rs.MoveNext
Loop
If myOrg = "" Then myOrg = "N/A"
End If

GetOrg = myOrg

End Function

[PhilS]

I am trying to read a value using vba from a external linked table - where the value is the result of a combobox.
The field displays a three letter code, (eg. "MCG"), but is based on a lookup with row source = "SELECT Organisations.[ID], Organisations.[OrgCode] FROM Organisations; "

my function below returns the index number of the record rather than the code. How do I return the value displayed in the table?
You information lacks some essential details, so there is some guesswork involved here...

I assume you need to join both tables to get the OrgCode for any one person.


Dim sql As String
sql = _
"SELECT o.OrgCode
FROM Staff s
INNER JOIN Organisations o
ON s.lOrg = o.Id
WHERE s.cName = '" & myPerson & "'"
This code is simplified for readability and not valid VBA. You either need to put the SQL statement all on one line or make each line a string constant and add line continuation code. (If you want to know more about this, you could watch my video tutorial on (SQL) string building in VBA (https://www.youtube.com/watch?v=c2_fEdFBj_Q).)

This will return only the single record applying to that person or none at all. That is much faster than looping through all records to find the one you are looking for. You should use this SQL for opening your recordset.

[werafa]

Thanks.

Yes, there is a related table that provides the lookup values - and they are set via a combobox during record entry.
I've put everything on the one line - and get a compile error. Do you know why? (I'm a complete novice at sql)

do I read correctly that:
- o and s are used like variables
- Staff and Organisations tables are temporarily joined via orgcode - independently of any access relationships
- and 'select orgcode where cName = myperson ' is the value lookup?

Werafa

[werafa]

Ahh,
Found an extra " that had somehow got onto the line

[PhilS]

Yes, there is a related table that provides the lookup values - and they are set via a combobox during record entry.
I've put everything on the one line - and get a compile error. Do you know why? (I'm a complete novice at sql)
I assume according to your most recent post you fixed that error. - If you find the time, it might be worth your while to watch the video I linked in the previous article. It's rather about how to put SQL into VBA-Code than primarily about SQL, but it might be helpful anyway.


do I read correctly that:
- o and s are used like variables
Yes, almost, they are Aliases for the table names. Those improve the readability and maintainability of complex SQL-Statements.

- Staff and Organisations tables are temporarily joined via orgcode - independently of any access relationships
They are linked via the ID/lOrg of the Organisation. I assumed OrgCode is the value you want to retrieve from the database.

- and 'select orgcode where cName = myperson ' is the value lookup?
Yes, correct.

[werafa]

your assumptions are correct, and I am 3 parts through the vid. (who is the good looking chap?)

I'm just having a crack at implementing it via "Set rs2 = CurrentDb.OpenRecordset(sSQL)" and "myOrg = rs2("OrgCode")"
I've got a cranky If statement to fix before I know if it will work

[werafa]

Hmm,

methinks either you or I should be called a genius, and methinks it should not be I.

the working code is:


Function GetOrg(myPerson As String) As String
'return the correct org code for myperson or return N/A
Dim rs As DAO.Recordset, rs2 As DAO.Recordset
Dim myOrg As String
Dim myObject As Object
Dim sSQL As String


sSQL = "SELECT o.OrgCode FROM Staff s " & _
"INNER JOIN Organisations o ON s.lOrg = o.Id " & _
"WHERE s.cName = '" & myPerson & "'"

Set rs = CurrentDb.OpenRecordset("Select * FROM Staff")
Set rs2 = CurrentDb.OpenRecordset(sSQL)


If Not (rs.EOF And rs.BOF) Then 'if recordset contains rows
rs.MoveFirst 'force start at first row
Do Until rs.EOF = True
If rs("cName") = myPerson Then
myOrg = rs2("OrgCode")
End If
If myOrg <> "" Then Exit Do
rs.MoveNext
Loop
If myOrg = "" Then myOrg = "N/A"
End If

GetOrg = myOrg

End Function