VBA Access getting RowSource to find lookup values

Question

VBA noob here (as of this mourning),

In MS Access I wrote a test function to find the value of a record base on some criteria you pass in.

The function seems to work fine except in cases where there is a lookup in the column that I am searching.

Basically it might return "19" and 19 corresponds to some other table value.

It seems that the RowSource of the column is what Im after so I can do a second query to find the true value.

Can someone point me in the right direction on finding the RowSource assuming I know the column name and then utilizing it to find the value Im after?

Edit: It seems that Im not explaining myself clearly, Here is a picture of what I trying to get programatically enter image description here

enter image description here

Not quite sure I understand your question correctly, but I tried your code & it works. I wonder if your problem isn't that your SELECT is actually bringing back more than one row, and giving you a different value than you'd expect. Your `recordSet` is only going to give you the first record. To move to the next one, you'd use `recordSet.MoveNext`. Try running your SELECT in a query, and see if it brings back only a single row. If so, try to explain the problem in a little more detail. — transistor1, Aug 22 '13 at 03:01
As a side note- you are going to have trouble if there aren't any results - so you will also want to do a check like `If Not recordSet.EOF Then result = recordSet(column)` — transistor1, Aug 22 '13 at 03:17
The "RowSource" you are pointing to is just a query to populate the combo box that shows up when you open up that table. I don't see how the RowSource is going to help you get the correct values back from your function. In your `lookUpColumnValue` function, after the line `sql = "SELECT...`, please try adding another line: `MsgBox sql`, and let us know what SQL statement shows up when you run your code. Also, try creating a new query and running that SQL statement, and show us the results. I think that should help. — transistor1, Aug 23 '13 at 02:03
SELECT [FI - ROOM].[Main Space Category] FROM [FI - ROOM] WHERE [FI - ROOM].[Room No] = [parm1] ----- I would think that I would need to know what the row source is so that after I run this query and get the numeric value, I can then run the row source query to get the string value that it looks up. — MichaelTaylor3D, Aug 23 '13 at 13:25
I have a similar question: https://stackoverflow.com/questions/50979048/query-fetches-via-lookup-but-recordsource-with-same-sql-does-not — bgmCoder, Jun 22 '18 at 00:20

ron tornambe · Answer 1 · 2013-08-22T21:51:24.123

2

If I understand your question correctly, I think using a parameter query will solve your problem. Using parameters is good practice since they will perform implicit data type casts and also prevent injection attacks.

Notice in the following function, I changed the lookupValue to a Variant type, which allows you to pass any type of value to the function.

Public Function lookUpColumnValue( _
    database As DAO.database, _
    column As String, _
    table As String, _
    lookUpColumn As String, _
    lookUpValue As Variant) As String

    Dim sql As String
    Dim recordSet As DAO.recordSet
    Dim result As String
    Dim qd As QueryDef
    Set qd = database.CreateQueryDef("")
    sql = "SELECT [" + table + "].[" + column + "] FROM [" + table + "] " & _
          "WHERE [" + table + "].[" + lookUpColumn + "] = [parm1];"
    qd.sql = sql
    qd.Parameters![parm1] = lookUpValue
    Set recordSet = qd.OpenRecordset()

    result = recordSet(column)

EDIT

    lookUpColumnValue = DLookup("Space Use Description", "Space Use Codes", result)


End Function

edited Aug 22 '13 at 21:51

answered Aug 21 '13 at 21:23

ron tornambe

10,452
7
33
60

While this is really amazing advice, and it answers a few unrelated questions I had, Im afraid it wasnt what I was asking. Im sorry if i didnt explain it clearly. Imagine a database column that has various numbers that correspond to a category name. Except that the column is linked to a table that identifies which names coorespond to the the numbers. The access database then displays the name in the column instead of the number. I believe its called a lookup. Im trying to get the name value but my function only returns the number. – MichaelTaylor3D Aug 22 '13 at 01:35
Oh okay, I think what you are looking for is the Dlookup() function: http://www.techonthenet.com/access/functions/domain/dlookup.php – ron tornambe Aug 22 '13 at 01:58
I added a picture so I can explain myself more clearly, the Dlookup function still gave me the same result that I was receiving before. Thanks! – MichaelTaylor3D Aug 22 '13 at 20:45
Thanks for the clarification. I edited my answer to return the "Space Use Description" based on the result code. I haven't been able to test it, but I think this this what you are looking for. – ron tornambe Aug 22 '13 at 21:55
Well what Im trying to do is generisize my function so that no matter what I query, it will get the correct value. The Space use description example that you gave me assumes that I know ahead of time what the lookup query is, and technically I do, but I need to pretend that I dont, that way when I move on to another column that has a different lookup query it will still be able to handle it. – MichaelTaylor3D Aug 23 '13 at 13:13
Although I applaud @transitor1 for an interesting approach, I think it would be simpler and safer to just include and pass the lookup table and column name to the function. – ron tornambe Aug 23 '13 at 17:50
I agree, @ron, it's better to know the details of your Primary Key table ahead of time and make 2 direct calls to a DLookup-like function (DLookup won't work in an external database -- as requested by OP-- without spawning a new Access.Application & using app.DLookup); but I was answering the direct question. There's no "easy" way to **dynamically** find the primary key description on an arbitrary field. – transistor1 Aug 23 '13 at 19:33

transistor1 · Accepted Answer · 2013-08-23T19:18:27.807

Try this -- I think I finally understand why you are looking for the RowSource -- sorry I didn't "get" it at first. The field you're trying to pull is a foreign key into a description table.

This function should work as a general solution for all such fields (assuming the RowSource always has the primary key first, and the description second). If there is no RowSource, it will just pull the value of the field.

It's based on your original code, rather than the changes proposed by @ron, but it should set you in the right direction. You should fix it to make it parameterized, and allow for variant data types, as ron suggests (+1 ron)

As an aside, use the ampersand (&) to join strings together in VBA to avoid things like this: abc = "1" + 1, where abc is now equal to 2 instead of "11" as you would expect if both items were intended to be strings.

Public Function lookUpColumnValue(Database As Database, column As String, table As String, lookUpColumn As String, lookUpValue As String) As String

    Dim sql As String
    Dim recordSet As DAO.recordSet
    Dim result As String

    lookUpColumnValue = "" 'Return a blank string if no result

    On Error Resume Next

    sql = "SELECT [" & table & "].[" & column & "] FROM [" & table & "] WHERE [" & table & "].[" & lookUpColumn & "] = '" & lookUpValue & "'"
    Set recordSet = Database.OpenRecordset(sql)

    If Not recordSet.EOF Then
        Dim td As DAO.TableDef

        'this gives your number - say, 19
        result = recordSet(column)

        Set td = Database.TableDefs(table)

        'Get the rowsource
        Dim p As DAO.Property
        For Each p In td.Fields(column).Properties
            If p.Name = "RowSource" Then
                RowSource = Replace(td.Fields(column).Properties("RowSource"), ";", "")
                Exit For
            End If
        Next

        If Not RowSource = "" Then
            Dim rs2 As DAO.recordSet
            Dim qd As DAO.QueryDef

            Set qd = Database.CreateQueryDef("", RowSource)
            Set rs2 = Database.OpenRecordset(RowSource)

            If rs2.EOF Then Exit Function

            PKField = rs2.Fields(0).Name

            rs2.Close
            qd.Close

            sql = "SELECT * FROM (" & RowSource & ") WHERE [" & PKField & "]=[KeyField?]"
            Set qd = Database.CreateQueryDef("", sql)
            qd.Parameters("KeyField?").Value = result

            Set rs2 = qd.OpenRecordset()

            If Not rs2.EOF Then
                'NOTE: This assumes your RowSource *always* has ID first, description 2nd.  This should usually be the case.
                lookUpColumnValue = rs2.Fields(1)
            End If
        Else
            'Return the field value if there is no RowSource
            lookUpColumnValue = recordSet.Fields(column)
        End If
    End If

End Function

This is almost exactly what Im looking for! the only problem Im getting is that Im always getting the very first description entry every time. So for example even if my foreign key is 19, it will always give me the value for zero. I looked through your code so see if I could catch why and it looks like it should be correct, so Im wondering if its the database design it self that now gives me that value. It does seem to follow you assumptions though — MichaelTaylor3D, Aug 23 '13 at 18:48
Duh - you're right. I didn't filter the RowSource based on the foreign key. Give me a min. — transistor1, Aug 23 '13 at 18:53
That should do it. As Ron says, this solution is hackish at best. You really should make 2 calls using something like DLookup - but I prefer to answer the question asked than to try to teach "best practices," when they're not asked for. Anyway, don't use this in your production code. — transistor1, Aug 23 '13 at 19:22
Awesome!! This is great! On a side note you were right with your comment to ron, Im using an external database so DLookup wont work (to my knowledge). Also I have no idea ahead of time what the database is that I will be using my code on. So I wouldn't know the primary key table ahead of time. — MichaelTaylor3D, Aug 23 '13 at 19:51
Great! Make sure you do use his parameter code for the initial lookup, though, because this code will break when `lookUpValue` is something like "Mike's Room" -- because of the tick marks (`'`). I don't want to edit my code with that change, because that's really his contribution & I don't want to "steal" it. — transistor1, Aug 23 '13 at 19:55

VBA Access getting RowSource to find lookup values

2 Answers2

Linked