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?
ste5anConnect With a Mentor Senior 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.
Dale FyeCommented:
what do you want to do with this value.

Best way is to use DLOOKUP("Description", "yourTableName", "PID = 451")
Dale FyeCommented:
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

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

StephenAuthor Commented:
Sorry, I should have mentioned it needs to be recursive for a treeview.
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?
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.