Access VBA across rows

A simple question I hope. I am trying to use findfirst to locate 451 in the PID column based on 451 in the CID column and then use Description on the PID 451 row.

Each connotation  I have tried gives me PID = 1.

I need PID = 451 Description Transfer Box Casing when starting with CID 451.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
what do you want to do with this value.

Best way is to use DLOOKUP("Description", "yourTableName", "PID = 451")
Dale FyeOwner, Developing Solutions LLCCommented:
Not certain why you are using findfirst, but the syntax for that would be something like:

    Dim rs as dao.recordset
    set rs = currentdb.openrecordset(tablename)
    with rs
        .findfirst "PID = 451"
        if .nomatch then 
             msgbox "value not found"
             msgbox rs!Description
       end if
   end with

Open in new window

StephenAuthor Commented:
Sorry, I should have mentioned it needs to be recursive for a treeview.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do you mean you're looking for this on a Treeview?
StephenAuthor Commented:
Yes this union query forms basis of the treeview.  As a newbe I've spent a lot of time  getting the union queries the way I wanted it to get the various values together.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I think you need to restate your question.

Do you want to find a row in a Treeview?

Or do you want to find a row in a Table/Form/Query?
StephenAuthor Commented:
I need to find the row in a query, using the CID value for a different row.
StephenAuthor Commented:
I need to find the row in a query, using the CID value of a different row.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Where is the CID value coming from? Is the user selecting something (like a Treeview node, or in a combo) or entering something (like in a textbox)?
ste5anSenior DeveloperCommented:
What kind of tree view?
StephenAuthor Commented:
The CID value is coming from:

strFind = DLookup("[StockCode]", "mod4", "[CID] = Forms!frmbomselect!txtListBomID")
    strFind2 = DLookup("[CID]", "mod4", "[CID] = Forms!frmbomselect!txtListBomID")

Open in new window

mod4 is the union query.

txtListBomID is reading a column of a list box.
StephenAuthor Commented:
Apologies, mod4 is a def query
StephenAuthor Commented:
Treeview is ActiveX. I need to get this working before thinking about transferring it to the all VBA version.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Apologies, mod4 is a def query
What is a "def" query?
I need to find the row in a query, using the CID value of a different row.
Are you doing this in the same query? That is, in a single query row, you want to find the value in another row in the same query?

Does your code above that pulls "strFind2" not work? If so, do you get an error?
ste5anSenior DeveloperCommented:
Do you want to load the entire dataset at once?

I'm using two queries in such a case: The first to query only the top level nodes. And the second containing the entire relevant data set.
Then you would loop over the first query and append the top-level node. And after appending it, you add a recursive call to add the children.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StephenAuthor Commented:
Here is the code I have which works. However probably not in recommended fashion. It is fairly instantaneous, however the root and branches repeat to each leaf.

Option Compare Database
Option Explicit
Private CurrentNode As Node

 Sub load()

Dim db As DAO.Database
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    Dim tv As MSComctlLib.TreeView
    Dim First As MSComctlLib.Node
    Dim Second As MSComctlLib.Node
    Dim Third As MSComctlLib.Node
    Dim Forth As MSComctlLib.Node
    Dim Fifth As MSComctlLib.Node
    Dim Sixth As MSComctlLib.Node
    Dim Seventh As MSComctlLib.Node
    Dim Keysecond As String
    Dim Keysecond2 As String
    Dim keythird As String
    Dim keyforth As String
    Dim keyfifth As String
    Dim keysixth As String
    Dim keyseventh As String
    Set tv = Forms!frmBomSelect.TreeShow.Object
    Dim strSQL As String
    Dim strSQL2 As String
     strSQL = "SELECT dbo_BomComponents.HeaderID, dbo_BomHeaders.ID, dbo_BomHeaders.BomReference, dbo_BomComponents.StockCode AS StockCodeB, dbo_BomHeaders_1.ID AS HID1, dbo_BomComponents_1.ID AS ID1, dbo_BomComponents_1.StockCode AS StockCodeC, dbo_BomComponents_2.ID AS ID2, dbo_BomComponents_2.StockCode AS StockcodeD, dbo_BomComponents_3.ID AS ID3, dbo_BomComponents_3.StockCode AS StockCodeE, dbo_BomHeaders_4.ID AS ID4, dbo_BomComponents_4.StockCode AS StockCodeF, dbo_BomComponents_5.ID AS ID5, dbo_BomComponents_5.StockCode AS StockCodeG, dbo_BomComponents_6.ID AS ID6, dbo_BomComponents_6.StockCode AS StockCodeH " _
& "FROM dbo_BomHeaders AS dbo_BomHeaders_7 RIGHT JOIN (dbo_BomComponents AS dbo_BomComponents_6 RIGHT JOIN (dbo_BomHeaders AS dbo_BomHeaders_6 RIGHT JOIN (dbo_BomComponents AS dbo_BomComponents_5 RIGHT JOIN (dbo_BomHeaders AS dbo_BomHeaders_5 RIGHT JOIN (dbo_BomComponents AS dbo_BomComponents_4 RIGHT JOIN (dbo_BomHeaders AS dbo_BomHeaders_4 RIGHT JOIN (dbo_BomComponents AS dbo_BomComponents_3 RIGHT JOIN (dbo_BomHeaders AS dbo_BomHeaders_3 RIGHT JOIN (dbo_BomComponents AS dbo_BomComponents_2 RIGHT JOIN (dbo_BomHeaders AS dbo_BomHeaders_2 RIGHT JOIN (dbo_BomComponents AS dbo_BomComponents_1 RIGHT JOIN ((dbo_BomComponents RIGHT JOIN dbo_BomHeaders ON dbo_BomComponents.HeaderID = dbo_BomHeaders.ID) LEFT JOIN dbo_BomHeaders AS dbo_BomHeaders_1 ON dbo_BomComponents.StockCode = dbo_BomHeaders_1.BomReference) ON dbo_BomComponents_1.HeaderID = dbo_BomHeaders_1.ID) ON dbo_BomHeaders_2.BomReference = dbo_BomComponents_1.StockCode) ON dbo_BomComponents_2.HeaderID = dbo_BomHeaders_2.ID) " _
& "ON dbo_BomHeaders_3.BomReference = dbo_BomComponents_2.StockCode) ON dbo_BomComponents_3.HeaderID = dbo_BomHeaders_3.ID) ON dbo_BomHeaders_4.BomReference = dbo_BomComponents_3.StockCode) ON dbo_BomComponents_4.HeaderID = dbo_BomHeaders_4.ID) ON dbo_BomHeaders_5.BomReference = dbo_BomComponents_4.StockCode) ON dbo_BomComponents_5.HeaderID = dbo_BomHeaders_5.ID) ON dbo_BomHeaders_6.BomReference = dbo_BomComponents_5.StockCode) ON dbo_BomComponents_6.HeaderID = dbo_BomHeaders_6.ID) ON dbo_BomHeaders_7.BomReference = dbo_BomComponents_6.StockCode " _
& "WHERE (((dbo_BomHeaders.ID)= Eval('[Forms]![frmBomSelect]![txtListBomID]'))); "

    Dim qdf As QueryDef
    Set qdf = Nothing
    On Error Resume Next
    Set qdf = db.QueryDefs("TempTable")
    If qdf Is Nothing Then
    Set qdf = db.CreateQueryDef("TempTable", strSQL)
    qdf.SQL = strSQL
    End If

    Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Set First = tv.Nodes.Add(, , , rs!BomReference)
    'First.Bold = True
    'First.ForeColor = vbBlue
 Do While Not rs.EOF
    Set Second = tv.Nodes.Add(First, tvwChild, , rs!StockcodeB)
    Keysecond = "B" & (rs!id1 + Rnd)
    If rs!id1 > 0 Then: Second.Bold = True
    If rs!id1 > 0 Then: Second.ForeColor = vbMagenta

    If rs!id1 > 0 Then: Set Third = tv.Nodes.Add(Second, tvwChild, Keysecond, rs!StockCodeC)
    keythird = "C" & (rs!ID2 + Rnd)
    If rs!ID2 > 0 Then: Third.Bold = True
    If rs!ID2 > 0 Then: Third.ForeColor = vbRed
    If rs!ID2 > 0 Then: Set Forth = tv.Nodes.Add(Third, tvwChild, keythird, rs!StockCodeD)
    keyforth = "D" & (rs!ID3 + Rnd)
    If rs!ID3 > 0 Then: Forth.Bold = True
    If rs!ID3 > 0 Then: Forth.ForeColor = vbGreen
    If rs!ID3 > 0 Then: Set Fifth = tv.Nodes.Add(Forth, tvwChild, keyforth, rs!StockCodeE)
    keyfifth = "E" & (rs!ID4 + Rnd)
    If rs!ID4 > 0 Then: Fifth.Bold = True
    If rs!ID4 > 0 Then: Fifth.ForeColor = vbYellow
    If rs!ID4 > 0 Then: Set Sixth = tv.Nodes.Add(Fifth, tvwChild, keyfifth, rs!StockCodeF)
    keysixth = "F" & (rs!ID5 + Rnd)
    If rs!ID5 > 0 Then: Sixth.Bold = True
    If rs!ID5 > 0 Then: Sixth.ForeColor = vbBlue
    If rs!ID5 > 0 Then: Set Seventh = tv.Nodes.Add(Sixth, tvwChild, keysixth, rs!StockCodeG)

 End Sub


Open in new window

ste5anSenior DeveloperCommented:
What about posting a concise and *complete* example?

Craft a simple database showing what you've got and attach it to your post.
ste5anSenior DeveloperCommented:
Working sample.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.