Is it possible with one recordset?

[default105]

I want to create a treeview for my work orders but I would like it to only
retrieve information within that particular recordset.

I know I can create one based on creating a link between the field
EquipmentID in both the Assets and WorkOrders table, but that lists the
entire EquipmentID's. I would like to only list the EquipmentID's that have
Work Orders. That is why I ask the question, is it possible with one
recordset to create a treeview that only lists the EquipmentID once (as
Master) and all the WorkOrderID's and Problem's (as Child)? Or am I stuck
with having to join two tables and listing all the EquipmentID's.

Thanks,

On an off note, I was wondering, I have replied to questions before here to
other problems but it does not reflect on my profile. I guess that point is
I just don't like to be seen as a someone who only takes and doesn't give. I
do try to respond to questions I can answer, but I am still in the beginning
stages of learning (as if you ever would learn everything.lol)

Thanks again

 

[Pieter Wijnen]

No & yes ;-)

Code snippet:

Dim Db AS DAO.Database
Dim RsE As DAO.Recordset, RsW As DAO.Recordset
Dim QDE AS DAO.QueryDef
Dim SQL AS String

Set Db = Access.CurrentDb()

SQL = "PARAMETERS pEQUIPMENTID TEXT;" & VBA.vbCrlf & _
"SELECT WORKORDER FROM WORKORDER W WHERE EQUIPMENTID =
pEQUIPMENTID"
Set Qdef = Db.CreateQueryDef(VBA.vbNullString,SQL)

SQL = "SELECT EQUIPMENTID FROM EQUIPMENT E " & _
"WHERE EXISTS (SELECT 'X' FROM WORKORDER W WHERE W.EQUIPMENTID =
E.EQUIPMENTID)"
Set RsE = Db.OpenRecordset(SQL,DAO.dbOpenSnapshot)
While Not RsE.EOF
' Add code to add eq to treeview here
QDE.Parameters(0).Value = RsE.Fields(0).Value
Set RsW = QDE.OpenRecordset(DAO.DbOpenSnapshot)
While Not RsW.EOF
' Add code to add WO to treeview here
RsW.MoveNext
WEnd
RsW.Close : Set RsW = Nothing
RsE.MoveNext
Wend
RsE.Close : Set RsE = Nothing
Set QDE = Nothing
Set Db = nothing


HTH

Pieter

 

[default105]

Thanks for the help but I am having a little trouble deciphering where I need
to edit the code. Will work on it.