By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Explosion

P: n/a
I have a problem similar as getting a straight list from multi-level Bills
of Material.

Instead I have parent jobs and subjobs.

Parent / Subjob
A / X
A / Y
A / Z
Y/ B
Y/ C
Z / D
Z / E
F / G

So I would like to get a list, base on reference parent 'A'
X
Y
Z
B
C
D
E

I have spent a few hours without success. I have seen BOM.ZIP on
mvps.org/access, but it is too different.

Anybodt has done something similar?

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
The following VBA function returns an array such as you describe, from a
table named Table1, with the fields Parent and Subjob:

'---------------<< Begin Code >>---------------
Public Function Explosion(Parent As String) As Variant

Dim exPos As Long, exBuffer As String
Dim rs As Recordset
ReDim exTemp(0) As String

exBuffer = "'" & Parent & "'"
Do
Set rs = CurrentDb.OpenRecordset("SELECT Subjob FROM Table1 WHERE
Parent In (" & exBuffer & ") ORDER BY Subjob;")
If rs.RecordCount = 0 Then rs.Close: Exit Do
exBuffer = ""
Do While Not rs.EOF
exBuffer = exBuffer & IIf(exBuffer > "", ",'", "'") & rs!Subjob
& "'"
rs.MoveNext
Loop
rs.Close
exPos = 0
Do
If InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) Then
exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) - exPos - 3)
Else
exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
Len(exBuffer) - exPos - 2)
End If
exPos = InStr(exPos + 1, exBuffer, ",", vbBinaryCompare)
ReDim Preserve exTemp(UBound(exTemp) + 1)
Loop Until exPos = 0
Loop
If UBound(exTemp) Then ReDim Preserve exTemp(UBound(exTemp) - 1)

Explosion = exTemp

End Function
'----------------<< End Code >>----------------

For example, if you want to populate an array named MyList with an explosion
of "A", as you described, you would use:
MyList=Explosion("A")
....and it would populate MyList thus:
MyList(0)="X"
MyList(1)="Y"
MyList(2)="Z"
MyList(3)="B"
MyList(4)="C"
MyList(5)="D"
MyList(6)="E"

See if this works for you.
"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:or**********************@wagner.videotron.net ...
I have a problem similar as getting a straight list from multi-level Bills of Material.

Instead I have parent jobs and subjobs.

Parent / Subjob
A / X
A / Y
A / Z
Y/ B
Y/ C
Z / D
Z / E
F / G

So I would like to get a list, base on reference parent 'A'
X
Y
Z
B
C
D
E

I have spent a few hours without success. I have seen BOM.ZIP on
mvps.org/access, but it is too different.

Anybodt has done something similar?

Nov 13 '05 #2

P: n/a
Thanks for replying. However I get an error (3075). I suspect that
the array is limited by the maximum number of characters allowed for a
string (255) variable exBuffer in this case. Any solution to this?

"ByteMyzer" <sb*@nospam.news.chi.sbcglobal.net> wrote in message news:<%e*****************@newssvr15.news.prodigy.c om>...
The following VBA function returns an array such as you describe, from a
table named Table1, with the fields Parent and Subjob:

'---------------<< Begin Code >>---------------
Public Function Explosion(Parent As String) As Variant

Dim exPos As Long, exBuffer As String
Dim rs As Recordset
ReDim exTemp(0) As String

exBuffer = "'" & Parent & "'"
Do
Set rs = CurrentDb.OpenRecordset("SELECT Subjob FROM Table1 WHERE
Parent In (" & exBuffer & ") ORDER BY Subjob;")
If rs.RecordCount = 0 Then rs.Close: Exit Do
exBuffer = ""
Do While Not rs.EOF
exBuffer = exBuffer & IIf(exBuffer > "", ",'", "'") & rs!Subjob
& "'"
rs.MoveNext
Loop
rs.Close
exPos = 0
Do
If InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) Then
exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) - exPos - 3)
Else
exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
Len(exBuffer) - exPos - 2)
End If
exPos = InStr(exPos + 1, exBuffer, ",", vbBinaryCompare)
ReDim Preserve exTemp(UBound(exTemp) + 1)
Loop Until exPos = 0
Loop
If UBound(exTemp) Then ReDim Preserve exTemp(UBound(exTemp) - 1)

Explosion = exTemp

End Function
'----------------<< End Code >>----------------

For example, if you want to populate an array named MyList with an explosion
of "A", as you described, you would use:
MyList=Explosion("A")
...and it would populate MyList thus:
MyList(0)="X"
MyList(1)="Y"
MyList(2)="Z"
MyList(3)="B"
MyList(4)="C"
MyList(5)="D"
MyList(6)="E"

See if this works for you.
"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:or**********************@wagner.videotron.net ...
I have a problem similar as getting a straight list from multi-level

Bills
of Material.

Instead I have parent jobs and subjobs.

Parent / Subjob
A / X
A / Y
A / Z
Y/ B
Y/ C
Z / D
Z / E
F / G

So I would like to get a list, base on reference parent 'A'
X
Y
Z
B
C
D
E

I have spent a few hours without success. I have seen BOM.ZIP on
mvps.org/access, but it is too different.

Anybodt has done something similar?

Nov 13 '05 #3

P: n/a
The string variable is not limited to 255 characters. That has nothing to
do with it, in any event. If you check the help file in reference to Error:
3075, you will find the following:

<Message> in query expression <expression>. (Error 3075)
The expression you typed isn't valid for the reason indicated in the
message. Make sure you have typed field names and punctuation correctly, and
then try the operation again.
Do any of the values in the records have single or double quotes, or even
commas, in them?
"Saintor" <sa******@hotmail.com> wrote in message
news:2f**************************@posting.google.c om...
Thanks for replying. However I get an error (3075). I suspect that
the array is limited by the maximum number of characters allowed for a
string (255) variable exBuffer in this case. Any solution to this?

"ByteMyzer" <sb*@nospam.news.chi.sbcglobal.net> wrote in message

news:<%e*****************@newssvr15.news.prodigy.c om>...
The following VBA function returns an array such as you describe, from a
table named Table1, with the fields Parent and Subjob:

'---------------<< Begin Code >>---------------
Public Function Explosion(Parent As String) As Variant

Dim exPos As Long, exBuffer As String
Dim rs As Recordset
ReDim exTemp(0) As String

exBuffer = "'" & Parent & "'"
Do
Set rs = CurrentDb.OpenRecordset("SELECT Subjob FROM Table1 WHERE Parent In (" & exBuffer & ") ORDER BY Subjob;")
If rs.RecordCount = 0 Then rs.Close: Exit Do
exBuffer = ""
Do While Not rs.EOF
exBuffer = exBuffer & IIf(exBuffer > "", ",'", "'") & rs!Subjob & "'"
rs.MoveNext
Loop
rs.Close
exPos = 0
Do
If InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) Then
exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) - exPos - 3)
Else
exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
Len(exBuffer) - exPos - 2)
End If
exPos = InStr(exPos + 1, exBuffer, ",", vbBinaryCompare)
ReDim Preserve exTemp(UBound(exTemp) + 1)
Loop Until exPos = 0
Loop
If UBound(exTemp) Then ReDim Preserve exTemp(UBound(exTemp) - 1)

Explosion = exTemp

End Function
'----------------<< End Code >>----------------

For example, if you want to populate an array named MyList with an explosion of "A", as you described, you would use:
MyList=Explosion("A")
...and it would populate MyList thus:
MyList(0)="X"
MyList(1)="Y"
MyList(2)="Z"
MyList(3)="B"
MyList(4)="C"
MyList(5)="D"
MyList(6)="E"

See if this works for you.
"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:or**********************@wagner.videotron.net ...
I have a problem similar as getting a straight list from multi-level

Bills
of Material.

Instead I have parent jobs and subjobs.

Parent / Subjob
A / X
A / Y
A / Z
Y/ B
Y/ C
Z / D
Z / E
F / G

So I would like to get a list, base on reference parent 'A'
X
Y
Z
B
C
D
E

I have spent a few hours without success. I have seen BOM.ZIP on
mvps.org/access, but it is too different.

Anybodt has done something similar?

Nov 13 '05 #4

P: n/a
Since I did not find on internet (surprisingly), here is my solution ( for
the next seeker) to have an exploded list, based on a multi-level structure.
Relatively simple. Table1 is the one with jobs and subjobs and table2 is
the target, with a list of all related subjobs and level found.

Public Sub Explode(newparent As String)
Dim db As DAO.database, rs As DAO.Recordset, iLevel As Integer

DoCmd.SetWarnings False

iLevel = 0

Set db = CurrentDb
Set rs = db.openrecordset("Select subjob from table1 where job='" &
newparent & "'")

If rs.RecordCount = 0 Then GoTo GetOut

iLevel = iLevel + 1
DoCmd.RunSQL "INSERT INTO table2 ( Subjob ) SELECT Subjob FROM Table1 WHERE
Job='" & newparent & "'"
DoCmd.RunSQL "UPDATE table2 SET table2.[Level] = " & iLevel & " WHERE
(((table2.Level)=0));"

rs.Close

R1:

Set rs = db.openrecordset("Select subjob from table2 where Level=" & iLevel)
iLevel = iLevel + 1

If rs.RecordCount = 0 Then GoTo GetOut

rs.MoveFirst
Do Until rs.EOF
newparent = rs!subjob
DoCmd.RunSQL "INSERT INTO table2 ( Subjob ) SELECT Subjob FROM Table1 WHERE
Job='" & newparent & "'"
DoCmd.RunSQL "UPDATE table2 SET table2.[Level] = " & iLevel & " WHERE
(((table2.Level)=0));"
rs.MoveNext
Loop

rs.Close
GoTo R1

GetOut:
DoCmd.SetWarnings True
Set rs = Nothing
Set db = Nothing

End Sub
"Saintor" <sa******@hotmail.com> wrote in message
news:2f**************************@posting.google.c om...
Thanks for replying. However I get an error (3075). I suspect that
the array is limited by the maximum number of characters allowed for a
string (255) variable exBuffer in this case. Any solution to this?

"ByteMyzer" <sb*@nospam.news.chi.sbcglobal.net> wrote in message

news:<%e*****************@newssvr15.news.prodigy.c om>...
The following VBA function returns an array such as you describe, from a
table named Table1, with the fields Parent and Subjob:

'---------------<< Begin Code >>---------------
Public Function Explosion(Parent As String) As Variant

Dim exPos As Long, exBuffer As String
Dim rs As Recordset
ReDim exTemp(0) As String

exBuffer = "'" & Parent & "'"
Do
Set rs = CurrentDb.OpenRecordset("SELECT Subjob FROM Table1 WHERE Parent In (" & exBuffer & ") ORDER BY Subjob;")
If rs.RecordCount = 0 Then rs.Close: Exit Do
exBuffer = ""
Do While Not rs.EOF
exBuffer = exBuffer & IIf(exBuffer > "", ",'", "'") & rs!Subjob & "'"
rs.MoveNext
Loop
rs.Close
exPos = 0
Do
If InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) Then
exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) - exPos - 3)
Else
exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
Len(exBuffer) - exPos - 2)
End If
exPos = InStr(exPos + 1, exBuffer, ",", vbBinaryCompare)
ReDim Preserve exTemp(UBound(exTemp) + 1)
Loop Until exPos = 0
Loop
If UBound(exTemp) Then ReDim Preserve exTemp(UBound(exTemp) - 1)

Explosion = exTemp

End Function
'----------------<< End Code >>----------------

For example, if you want to populate an array named MyList with an explosion of "A", as you described, you would use:
MyList=Explosion("A")
...and it would populate MyList thus:
MyList(0)="X"
MyList(1)="Y"
MyList(2)="Z"
MyList(3)="B"
MyList(4)="C"
MyList(5)="D"
MyList(6)="E"

See if this works for you.
"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:or**********************@wagner.videotron.net ...
I have a problem similar as getting a straight list from multi-level

Bills
of Material.

Instead I have parent jobs and subjobs.

Parent / Subjob
A / X
A / Y
A / Z
Y/ B
Y/ C
Z / D
Z / E
F / G

So I would like to get a list, base on reference parent 'A'
X
Y
Z
B
C
D
E

I have spent a few hours without success. I have seen BOM.ZIP on
mvps.org/access, but it is too different.

Anybodt has done something similar?

Nov 13 '05 #5

P: n/a
Hey, this isn't an indication if ingratitude, now is it? You never
responded to the question I asked. Geez, try to help and run into
ungrateful users.

One more try. Here is a sub that does what you need, with Table1 defining
the Parent and Subjob relationships and Table2 with the following two
fields: Subjob (text field) and LLC (Number field, Long Integer). (LLC - Low
Level Code)

'---------------------<< begin code>>---------------------
Public Sub Explosion(Parent As String)

Dim LLC As Long, qd As QueryDef

Set qd = CurrentDb.CreateQueryDef("")

qd.SQL = "INSERT INTO Table2 (Subjob, LLC) " _
& "SELECT Table1.Subjob, 1 FROM Table1 " _
& "WHERE Table1.Parent='" & Parent & "';"
qd.Execute

If qd.RecordsAffected Then
Do
LLC = LLC + 1
qd.SQL = "INSERT INTO Table2 (Subjob, LLC) " _
& "SELECT Table1.Subjob, " & LLC + 1 _
& " FROM Table1 WHERE Table1.Parent IN " _
& "(SELECT T2.Subjob FROM Table2 AS T2 " _
& "WHERE T2.LLC=" & LLC & ";);"
qd.Execute
Loop Until qd.RecordsAffected = 0
End If

qd.Close
Set qd = Nothing

End Sub
'----------------------<< end code>>----------------------

"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:Uw********************@wagner.videotron.net.. .
Since I did not find on internet (surprisingly), here is my solution ( for
the next seeker) to have an exploded list, based on a multi-level structure. Relatively simple. Table1 is the one with jobs and subjobs and table2 is
the target, with a list of all related subjobs and level found.

Public Sub Explode(newparent As String)
Dim db As DAO.database, rs As DAO.Recordset, iLevel As Integer

DoCmd.SetWarnings False

iLevel = 0

Set db = CurrentDb
Set rs = db.openrecordset("Select subjob from table1 where job='" &
newparent & "'")

If rs.RecordCount = 0 Then GoTo GetOut

iLevel = iLevel + 1
DoCmd.RunSQL "INSERT INTO table2 ( Subjob ) SELECT Subjob FROM Table1 WHERE Job='" & newparent & "'"
DoCmd.RunSQL "UPDATE table2 SET table2.[Level] = " & iLevel & " WHERE
(((table2.Level)=0));"

rs.Close

R1:

Set rs = db.openrecordset("Select subjob from table2 where Level=" & iLevel) iLevel = iLevel + 1

If rs.RecordCount = 0 Then GoTo GetOut

rs.MoveFirst
Do Until rs.EOF
newparent = rs!subjob
DoCmd.RunSQL "INSERT INTO table2 ( Subjob ) SELECT Subjob FROM Table1 WHERE Job='" & newparent & "'"
DoCmd.RunSQL "UPDATE table2 SET table2.[Level] = " & iLevel & " WHERE
(((table2.Level)=0));"
rs.MoveNext
Loop

rs.Close
GoTo R1

GetOut:
DoCmd.SetWarnings True
Set rs = Nothing
Set db = Nothing

End Sub
"Saintor" <sa******@hotmail.com> wrote in message
news:2f**************************@posting.google.c om...
Thanks for replying. However I get an error (3075). I suspect that
the array is limited by the maximum number of characters allowed for a
string (255) variable exBuffer in this case. Any solution to this?

"ByteMyzer" <sb*@nospam.news.chi.sbcglobal.net> wrote in message

news:<%e*****************@newssvr15.news.prodigy.c om>...
The following VBA function returns an array such as you describe, from a table named Table1, with the fields Parent and Subjob:

'---------------<< Begin Code >>---------------
Public Function Explosion(Parent As String) As Variant

Dim exPos As Long, exBuffer As String
Dim rs As Recordset
ReDim exTemp(0) As String

exBuffer = "'" & Parent & "'"
Do
Set rs = CurrentDb.OpenRecordset("SELECT Subjob FROM Table1 WHERE Parent In (" & exBuffer & ") ORDER BY Subjob;")
If rs.RecordCount = 0 Then rs.Close: Exit Do
exBuffer = ""
Do While Not rs.EOF
exBuffer = exBuffer & IIf(exBuffer > "", ",'", "'") & rs!Subjob & "'"
rs.MoveNext
Loop
rs.Close
exPos = 0
Do
If InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) Then
exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) - exPos - 3)
Else
exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
Len(exBuffer) - exPos - 2)
End If
exPos = InStr(exPos + 1, exBuffer, ",", vbBinaryCompare)
ReDim Preserve exTemp(UBound(exTemp) + 1)
Loop Until exPos = 0
Loop
If UBound(exTemp) Then ReDim Preserve exTemp(UBound(exTemp) - 1)

Explosion = exTemp

End Function
'----------------<< End Code >>----------------

For example, if you want to populate an array named MyList with an explosion of "A", as you described, you would use:
MyList=Explosion("A")
...and it would populate MyList thus:
MyList(0)="X"
MyList(1)="Y"
MyList(2)="Z"
MyList(3)="B"
MyList(4)="C"
MyList(5)="D"
MyList(6)="E"

See if this works for you.
"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:or**********************@wagner.videotron.net ...
> I have a problem similar as getting a straight list from multi-level Bills
> of Material.
>
> Instead I have parent jobs and subjobs.
>
> Parent / Subjob
> A / X
> A / Y
> A / Z
> Y/ B
> Y/ C
> Z / D
> Z / E
> F / G
>
> So I would like to get a list, base on reference parent 'A'
> X
> Y
> Z
> B
> C
> D
> E
>
> I have spent a few hours without success. I have seen BOM.ZIP on
> mvps.org/access, but it is too different.
>
> Anybodt has done something similar?
>
>
>


Nov 13 '05 #6

P: n/a
I had a look at google group. Indeed your message from August 19th never
showed up from my newsgroup server. Sorry about that. Thanks for the
revision.

Signed:
Not an ungrateful user. ;o)

"ByteMyzer" <sb*@nospam.news.chi.sbcglobal.net> wrote in message
news:q5*****************@newssvr33.news.prodigy.co m...
Hey, this isn't an indication if ingratitude, now is it? You never
responded to the question I asked. Geez, try to help and run into
ungrateful users.

One more try. Here is a sub that does what you need, with Table1 defining
the Parent and Subjob relationships and Table2 with the following two
fields: Subjob (text field) and LLC (Number field, Long Integer). (LLC - Low Level Code)

'---------------------<< begin code>>---------------------
Public Sub Explosion(Parent As String)

Dim LLC As Long, qd As QueryDef

Set qd = CurrentDb.CreateQueryDef("")

qd.SQL = "INSERT INTO Table2 (Subjob, LLC) " _
& "SELECT Table1.Subjob, 1 FROM Table1 " _
& "WHERE Table1.Parent='" & Parent & "';"
qd.Execute

If qd.RecordsAffected Then
Do
LLC = LLC + 1
qd.SQL = "INSERT INTO Table2 (Subjob, LLC) " _
& "SELECT Table1.Subjob, " & LLC + 1 _
& " FROM Table1 WHERE Table1.Parent IN " _
& "(SELECT T2.Subjob FROM Table2 AS T2 " _
& "WHERE T2.LLC=" & LLC & ";);"
qd.Execute
Loop Until qd.RecordsAffected = 0
End If

qd.Close
Set qd = Nothing

End Sub
'----------------------<< end code>>----------------------

"Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
news:Uw********************@wagner.videotron.net.. .
Since I did not find on internet (surprisingly), here is my solution ( for
the next seeker) to have an exploded list, based on a multi-level structure.
Relatively simple. Table1 is the one with jobs and subjobs and table2 is the target, with a list of all related subjobs and level found.

Public Sub Explode(newparent As String)
Dim db As DAO.database, rs As DAO.Recordset, iLevel As Integer

DoCmd.SetWarnings False

iLevel = 0

Set db = CurrentDb
Set rs = db.openrecordset("Select subjob from table1 where job='" &
newparent & "'")

If rs.RecordCount = 0 Then GoTo GetOut

iLevel = iLevel + 1
DoCmd.RunSQL "INSERT INTO table2 ( Subjob ) SELECT Subjob FROM Table1

WHERE
Job='" & newparent & "'"
DoCmd.RunSQL "UPDATE table2 SET table2.[Level] = " & iLevel & " WHERE
(((table2.Level)=0));"

rs.Close

R1:

Set rs = db.openrecordset("Select subjob from table2 where Level=" &

iLevel)
iLevel = iLevel + 1

If rs.RecordCount = 0 Then GoTo GetOut

rs.MoveFirst
Do Until rs.EOF
newparent = rs!subjob
DoCmd.RunSQL "INSERT INTO table2 ( Subjob ) SELECT Subjob FROM Table1

WHERE
Job='" & newparent & "'"
DoCmd.RunSQL "UPDATE table2 SET table2.[Level] = " & iLevel & " WHERE
(((table2.Level)=0));"
rs.MoveNext
Loop

rs.Close
GoTo R1

GetOut:
DoCmd.SetWarnings True
Set rs = Nothing
Set db = Nothing

End Sub
"Saintor" <sa******@hotmail.com> wrote in message
news:2f**************************@posting.google.c om...
Thanks for replying. However I get an error (3075). I suspect that
the array is limited by the maximum number of characters allowed for a
string (255) variable exBuffer in this case. Any solution to this?

"ByteMyzer" <sb*@nospam.news.chi.sbcglobal.net> wrote in message

news:<%e*****************@newssvr15.news.prodigy.c om>...
> The following VBA function returns an array such as you describe,

from a > table named Table1, with the fields Parent and Subjob:
>
> '---------------<< Begin Code >>---------------
> Public Function Explosion(Parent As String) As Variant
>
> Dim exPos As Long, exBuffer As String
> Dim rs As Recordset
> ReDim exTemp(0) As String
>
> exBuffer = "'" & Parent & "'"
> Do
> Set rs = CurrentDb.OpenRecordset("SELECT Subjob FROM Table1

WHERE
> Parent In (" & exBuffer & ") ORDER BY Subjob;")
> If rs.RecordCount = 0 Then rs.Close: Exit Do
> exBuffer = ""
> Do While Not rs.EOF
> exBuffer = exBuffer & IIf(exBuffer > "", ",'", "'") &

rs!Subjob
> & "'"
> rs.MoveNext
> Loop
> rs.Close
> exPos = 0
> Do
> If InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) Then
> exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
> InStr(exPos + 1, exBuffer, ",", vbBinaryCompare) - exPos - 3)
> Else
> exTemp(UBound(exTemp)) = Mid(exBuffer, exPos + 2,
> Len(exBuffer) - exPos - 2)
> End If
> exPos = InStr(exPos + 1, exBuffer, ",", vbBinaryCompare)
> ReDim Preserve exTemp(UBound(exTemp) + 1)
> Loop Until exPos = 0
> Loop
> If UBound(exTemp) Then ReDim Preserve exTemp(UBound(exTemp) - 1)
>
> Explosion = exTemp
>
> End Function
> '----------------<< End Code >>----------------
>
> For example, if you want to populate an array named MyList with an

explosion
> of "A", as you described, you would use:
> MyList=Explosion("A")
> ...and it would populate MyList thus:
> MyList(0)="X"
> MyList(1)="Y"
> MyList(2)="Z"
> MyList(3)="B"
> MyList(4)="C"
> MyList(5)="D"
> MyList(6)="E"
>
> See if this works for you.
>
>
> "Saintor" <sa******@REMOVETHIShotmail.com> wrote in message
> news:or**********************@wagner.videotron.net ...
> > I have a problem similar as getting a straight list from multi-level > Bills
> > of Material.
> >
> > Instead I have parent jobs and subjobs.
> >
> > Parent / Subjob
> > A / X
> > A / Y
> > A / Z
> > Y/ B
> > Y/ C
> > Z / D
> > Z / E
> > F / G
> >
> > So I would like to get a list, base on reference parent 'A'
> > X
> > Y
> > Z
> > B
> > C
> > D
> > E
> >
> > I have spent a few hours without success. I have seen BOM.ZIP on
> > mvps.org/access, but it is too different.
> >
> > Anybodt has done something similar?
> >
> >
> >



Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.