Graham,
Yes they are the other way round, code works fin & I get a 2nd level on some
nodes, i now get error message saying KEY IS NOT UNIQUE IN COLLECTION.
having seen your comments relating to the 3rd argument I have noticed that
MIBatch contains duplicate instances. i.e. with prefix, m3305 can be used in
multiple MECHT_ID
b42 m3305
b43 m3305
b50 m3305
b77 m3305
Do i now have to go back to the drawing board to look at the data table ? or
create a differant query?
Current code listing
Private Sub Form_Load()
Dim DB As DAO.Database, RS As DAO.Recordset
Dim StrOrderKey As String
Set DB = CurrentDb
' Open a Recordset and loop through it to fill the TreeView
' control.
' Fill Level 1 using qryPowderB.Batch_ID as the Key property
' displays first level like: o42 1200
Set RS = DB.OpenRecordset("qryPowderB", dbOpenForwardOnly)
Do Until RS.EOF
Me!axTreeView.Nodes.Add , , RS!Batch_ID, RS!Name
RS.MoveNext
Loop
RS.Close
' Fill Level 2.
Set RS = DB.OpenRecordset("qryTVPBMIX", dbOpenForwardOnly)
Do Until RS.EOF
' Link to Level 1 by referencing the Batchid (o42)key and set
' the node as a child node of Level 1. Use "o" and the
' StrConv() function in the new Key property for Level 2,
' because MIBatch is a numeric field.
' StrOrderKey = StrConv("o" & RS!MIBatch, vbLowerCase)
Me!axTreeView.Nodes.Add RS!Batchid.Value, tvwChild, _
"m" & RS!MIBatch, RS!MIBatch & " " & RS!Rawmatt
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Set DB = Nothing
End Sub
SQL for qryTVPBMIX
SELECT "b" & [NPOW-ID] AS Batchid, tblPowdermix.[MI-Batch] AS MIBatch,
IIf([Rawmat]=1,"Tungsten",IIf([Rawmat]=2,"Nickel",IIf([Rawmat]=3,"Iron",IIf([Rawmat]=4,"Copper",IIf([Rawmat]=5,"Molydbneyum","HA/HE"))))) AS Rawmatt
FROM tblPowdermix;
Output from query
Batchid MIBatch Rawmatt
b42 3305 Tungsten
b42 3505 Nickel
b42 3205 Iron
b43 014 HA/HE
b43 1031 HA/HE
b43 004 HA/HE
b43 1034 HA/HE
b43 1035 HA/HE
b44 004 HA/HE
b44 014 HA/HE
b44 1031 HA/HE
b44 1034 HA/HE
b44 1035 HA/HE
b45 1035 HA/HE
b45 014 HA/HE
b45 1031 HA/HE
b45 004 HA/HE
b45 1034 HA/HE
b46 1034 HA/HE
b46 1035 HA/HE
b46 1031 HA/HE
b46 004 HA/HE
b46 014 HA/HE
b47 016 HA/HE
b47 017 HA/HE
b47 018 HA/HE
b47 1024 HA/HE
b47 1023 HA/HE
b48 037 HA/HE
b48 039 HA/HE
b48 010 HA/HE
b48 040 HA/HE
b48 029 HA/HE
b48 050 HA/HE
b48 008 HA/HE
b48 051 HA/HE
b49 040 HA/HE
b49 042 HA/HE
b49 021 HA/HE
b49 022 HA/HE
b49 028 HA/HE
b49 029 HA/HE
b49 041 HA/HE
b49 013 HA/HE
b50 982 HA/HE
b50 041 HA/HE
b50 025 HA/HE
b50 039 HA/HE
b50 031 HA/HE
b50 013 HA/HE
b50 042 HA/HE
b50 036 HA/HE
b50 037 HA/HE
b50 053 HA/HE
b51 013 HA/HE
b51 042 HA/HE
b51 036 HA/HE
b51 053 HA/HE
b51 037 HA/HE
b51 031 HA/HE
b51 025 HA/HE
b51 039 HA/HE
b51 982 HA/HE
b52 026 HA/HE
b52 043 HA/HE
b52 008 HA/HE
b52 051 HA/HE
b52 050 HA/HE
b53 0106 Tungsten
b53 3505 Nickel
b53 3205 Iron
b53 2805 Molydbneyum
b54 021 HA/HE
b54 040 HA/HE
b54 046 HA/HE
b54 041 HA/HE
b54 022 HA/HE
b54 042 HA/HE
b55 0106 Tungsten
b55 3505 Nickel
b55 3205 Iron
b56 047 HA/HE
b56 052 HA/HE
b56 053 HA/HE
b57 032 HA/HE
b57 2305 Tungsten
b57 3505 Nickel
b58 900 HA/HE
b58 907 HA/HE
b58 898 HA/HE
b59 038 HA/HE
b59 2305 Tungsten
b59 3505 Nickel
b60 045 HA/HE
b60 021 HA/HE
b60 046 HA/HE
b60 041 HA/HE
b60 022 HA/HE
b61 1204 HA/HE
b61 1218 HA/HE
b61 056 HA/HE
b61 049 HA/HE
b62 0106 Tungsten
b62 3505 Nickel
b62 2305 Copper
b63 0106 Tungsten
b63 3505 Nickel
b63 2305 Copper
b64 056 HA/HE
b64 049 HA/HE
b64 1204 HA/HE
b64 046 HA/HE
b64 1203 HA/HE
b65 023 HA/HE
b65 048 HA/HE
b65 049 HA/HE
b66 969 HA/HE
b66 045 HA/HE
b66 041 HA/HE
b66 1209 HA/HE
b66 990 HA/HE
b66 972 HA/HE
b67 045 HA/HE
b67 990 HA/HE
b67 969 HA/HE
b67 967 HA/HE
b67 046 HA/HE
b68 1219 HA/HE
b68 969 HA/HE
b68 1215 HA/HE
b68 967 HA/HE
b69 967 HA/HE
b69 1219 HA/HE
b69 969 HA/HE
b69 1215 HA/HE
b69 990 HA/HE
b69 972 HA/HE
b69 045 HA/HE
b70 0106 Tungsten
b70 3505 Nickel
b70 3205 Iron
b71 0106 Tungsten
b71 3505 Nickel
b71 3205 Iron
b72 1205 HA/HE
b72 876 HA/HE
b72 026 HA/HE
b72 017 HA/HE
b73 0306 Tungsten
b73 3505 Nickel
b73 2305 Copper
b74 0306 Tungsten
b74 3505 Nickel
b74 2305 Copper
b75 1221 HA/HE
b75 1222 HA/HE
b76 0306 Tungsten
b76 3505 Nickel
b76 3205 Iron
b77 0306 Tungsten
b77 3505 Nickel
b77 3205 Iron
b78 1216 HA/HE
b78 1218 HA/HE
b78 1201 HA/HE
b78 1202 HA/HE
b78 1204 HA/HE
b79 1225 HA/HE
b79 0306 Tungsten
b81 0306 Tungsten
b81 3505 Nickel
b81 2305 Copper
b82 2405 Tungsten
b82 881 HA/HE
b82 878 HA/HE
b82 877 HA/HE
b82 872 HA/HE
b83 2405 Tungsten
b83 872 HA/HE
b83 842 HA/HE
b83 878 HA/HE
b83 881 HA/HE
b84 898 HA/HE
b84 900 HA/HE
b84 858 HA/HE
b84 597 HA/HE
b85 1200 HA/HE
b85 1214 HA/HE
b85 1229 HA/HE
b85 1236 HA/HE
b85 026 HA/HE
b85 008 HA/HE
b86 1207 HA/HE
b86 1206 HA/HE
b86 969 HA/HE
b86 1219 HA/HE
b86 1217 HA/HE
b87 1019 HA/HE
b87 055 HA/HE
b87 1205 HA/HE
b88 1033 HA/HE
b88 054 HA/HE
b88 965 HA/HE
b88 0306 Tungsten
b88 3505 Nickel
b89 1232 HA/HE
b89 1233 HA/HE
b89 054 HA/HE
b89 0306 Tungsten
b89 3505 Nickel
b90 876 HA/HE
b90 032 HA/HE
b91 1229 HA/HE
b91 1230 HA/HE
b92 1202 HA/HE
b92 002 HA/HE
b92 1203 HA/HE
b93 1211 HA/HE
b93 0306 Tungsten
b93 3505 Nickel
b94 1211 HA/HE
b94 0306 Tungsten
b94 3505 Nickel
b95 1211 HA/HE
b95 0306 Tungsten
b95 3505 Nickel
b96 1226 HA/HE
b96 0306 Tungsten
b97 1234 HA/HE
b97 1231 HA/HE
b97 1242 HA/HE
b98 1210 HA/HE
b98 047 HA/HE
b98 053 HA/HE
b98 052 HA/HE
b98 1248 HA/HE
b99 1236 HA/HE
b99 1214 HA/HE
b99 1235 HA/HE
b99 1230 HA/HE
b99 1229 HA/HE
b100 1206 HA/HE
b100 1207 HA/HE
b100 1225 HA/HE
b100 1226 HA/HE
b101 0306 Tungsten
b101 3505 Nickel
b101 3205 Iron
b102 0306 Tungsten
b102 3505 Nickel
b102 3205 Iron
b103 0306 Tungsten
b103 3505 Nickel
b103 3205 Iron
b104 1231 HA/HE
b104 1234 HA/HE
b105 1215 HA/HE
b106 897 HA/HE
b106 920 HA/HE
b106 809 HA/HE
b106 1222 HA/HE
b106 1025 HA/HE
b107 1209 HA/HE
b107 1213 HA/HE
b107 1208 HA/HE
b107 1227 HA/HE
b108 1242 HA/HE
b108 055 HA/HE
b108 1243 HA/HE
b109 926 HA/HE
b109 1015 HA/HE
b109 1017 HA/HE
b109 1212 HA/HE
b110 1248 HA/HE
b110 1249 HA/HE
b110 1250 HA/HE
b111 1251 HA/HE
b111 1238 HA/HE
b111 1217 HA/HE
b112 0306 Tungsten
b112 3505 Nickel
b112 3205 Iron
b113 0306 Tungsten
b113 3505 Nickel
b113 3205 Iron
b114 0000 HA/HE
b115 0306 Tungsten
b115 3505 Nickel
b115 3205 Iron
b116 0306 Tungsten
b116 3505 Nickel
b116 3205 Iron
b123 05/08/844 HA/HE
b123 0306 Tungsten
b123 3505 Nickel
b123 2305 Copper
b124 05/10/931 HA/HE
b124 0306 Tungsten
b124 3505 Nickel
b124 2305 Copper
SQL for qryPowderB
SELECT "b" & [MECHT_ID] AS Batch_ID, tblPowderbatches.[BATCH#] AS Name
FROM tblPowderbatches;
Output
Batch_ID Name
b42 1200
b43 1201
b44 1202
b45 1203
b46 1204
b47 1205
b48 1206
b49 1207
b50 1208
b51 1209
b52 1210
b53 1212
b54 1213
b55 1214
b56 1215
b57 1216
b58 1217
b59 1218
b60 1219
b61 1220
b62 1221
b63 1222
b64 1223
b65 1224
b66 1225
b67 1226
b68 1227
b69 1228
b70 1229
b71 1230
b72 1231
b73 1232
b74 1233
b75 1234
b76 1235
b77 1236
b78 1237
b79 1238
b81 1239
b85 1240
b86 1241
b87 1242
b88 1243
b89 1244
b90 1245
b91 1246
b92 1247
b93 1248
b94 1249
b95 1250
b96 1251
b97 1252
b98 1253
b99 1254
b100 1255
b101 1256
b102 1257
b103 1258
b104 1259
b105 1260
b106 1261
b107 1262
b108 1263
b109 1264
b110 1265
b111 1266
b112 1267
b113 1268
b114 1269
b115 1271
b116 1272
b123 1273
b124 1274
b82 898
b83 900
b84 907
Graham Mandeno said:
Ahhhh... I think I understand now.
I was assuming that NPOW_ID is the primary key of tblPowderMix and MIBatch
is the foreign key (the field containing a matching MECHT_ID value that
links the records in the two tables).
In fact it is the other way around. Am I correct?
When you add a node to a TreeView, there are two important considerations:
1. The key value (third argument) must be unique.
2. The relative value, if specified (first argument) must exist.
If the key is not unique you will get the error "Key is not unique in
collection"
If the relative does not exist you will get "Element not found"
OK, so since MECHT_ID and NPOW_ID are related fields, you must generate
matching keys for them, otherwise you will get "Element not found". So,
change you queries so they both have the same prefix - say "b" for "batch":
SELECT "b" & [MECHT_ID] AS Batch_ID, ...
and
SELECT "b" & [NPOW-ID] AS Batchid, ...
Now, to ensure the keys are unique when you add the mix (child) nodes, you
should generate keys that start with a different letter - say "m" for "mix":
Me!axTreeView.Nodes.Add RS!Batchid.Value, tvwChild, _
"m" & RS!MIBatch, RS!MIBatch & " " & RS!Rawmatt
Hope that works for you.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Jedit said:
Graham
yes correct, the two id fields do share the same value, MECHT_ID is the
primary key from the main table. NPOW_ID is linked field that gets its
value
from MECH_ID when a element is added to the powdermix table, i.e. 5 or 6
elements in NPOW_ID to one MECHT_ID. Not sure why I created the querys in
the
first place but there u go??? :-(
Re did the code as to your suggestion but now get element not found code
35601
Changed it round slightly to read.
' Fill Level 2.
Set RS = DB.OpenRecordset("qryTVPBMIX", dbOpenForwardOnly)
Do Until RS.EOF
' Link to Level 1 by referencing the Batchid (o42)key and set
' the node as a child node of Level 1. Use "o" and the
' StrConv() function in the new Key property for Level 2,
' because MIBatch is a numeric field.
StrOrderKey = StrConv("o" & RS!MIBatch, vbLowerCase)
Me!axTreeView.Nodes.Add RS!Batchid.Value, tvwChild, StrOrderKey, _
RS!MIBatch & " " & RS!Rawmatt
RS.MoveNext
Loop
& get the same ans, when I place the cursor over the yellow portion of the
same hoghlit code, tvwChild, has a value of 4 i thought this should have
been
o42?
Graham Mandeno said:
Is it possible that a record in tblPowderMix has a value for NPOW_ID
which
is the same as a MECHT_ID value in tblPowderBatches?
If this happens, you will be trying to create two nodes with the same key
value.
I suggest you use a different prefix for the ID values in the two tables:
SELECT "b" & [MECHT_ID] AS Batch_ID, ...
and
SELECT "m" & [NPOW-ID] AS Batchid, ...
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Need help with tree View
I have a treeview control on a form & I can populate the first level
But seem to be going round in circles when it comes to the second leg.
The first level is using a query "qryPowderB" that shows the Batch_ID &
Name, the name of the batch.
The second level is also using a query "qryTVPBMIX"
Displays
Batchid this is equal to Batch_ID from the other query
MIBatch this is the Batch number given as a name
Rawmatt this is the raw material type
What I want to do is display a batch with name 1200 then display all
the
sub
batches associated with it.
Not sure why but I keep getting Key is not unique in collection, in the
VB
editor Me!axTreeView.Nodes.Add RS!Batchid.Value, tvwChild, StrOrderKey,
_
RS!MIBatch & " " & RS!Rawmatt
Is highlit yellow but I cant debug it, don't know how?
The bellow is the sql code for the querys & the vb code for my tree
view
HELP HELP HELP HELP pleeeeas??
qryPowderB
SELECT "o" & [MECHT_ID] AS Batch_ID, tblPowderbatches.[BATCH#] AS Name
FROM tblPowderbatches;
qryTVPBMIX
SELECT "o" & [NPOW-ID] AS Batchid, tblPowdermix.[MI-Batch] AS MIBatch,
IIf([Rawmat]=1,"Tungsten",IIf([Rawmat]=2,"Nickel",IIf([Rawmat]=3,"Iron",IIf([Rawmat]=4,"Copper",IIf([Rawmat]=5,"Molydbneyum","HA/HE")))))
AS Rawmatt
FROM tblPowdermix;
VB Code
Private Sub Form_Load()
Dim DB As DAO.Database, RS As DAO.Recordset
Dim StrOrderKey As String
Set DB = CurrentDb
' Open a Recordset and loop through it to fill the TreeView
' control.
' The Key value, RS!Batch_ID, is naturally alphabetical
Set RS = DB.OpenRecordset("qryPowderB", dbOpenDynaset, abReadOnly)
Do Until RS.EOF
Me!axTreeView.Nodes.Add , , RS!Batch_ID, RS!Name
RS.MoveNext
Loop
RS.Close
' Fill Level 2.
Set RS = DB.OpenRecordset("qryTVPBMIX", dbOpenForwardOnly)
Do Until RS.EOF
' Link to Level 1 by referencing the Batchid key and set
' the node as a child node of Level 1. Use "o" and the
' StrConv() function in the new Key property for Level 2,
' because MIBatch is a numeric field.
StrOrderKey = StrConv("o" & RS!MIBatch, vbLowerCase)
Me!axTreeView.Nodes.Add RS!Batchid.Value, tvwChild, StrOrderKey,
_
RS!MIBatch & " " & RS!Rawmatt
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Set DB = Nothing
End Sub
