I keep getting errors when I have the Preserve keyword, when I take it out, I don't get errors...
I need to Preserve the data in my arrays so, how do I preserve and make my array large at the same time.
The notes in Help say you can only change the last dimension when using Preserve, but I do not understand what that means...
My arrays are set as follows
Public roughPart() as string
Public roughPartQty() as integer
Public topoutPart() as string
Public topoutPartQty() as integer
Here is the section of code relevant to the above problem...
Else:
Set blkPart = TempSet(X)
If blkPart.Name = "Assembly" Then
cntAssembly = cntAssembly + 1
For Each attPart In blkPart.GetAttributes
Select Case attPart.TagString
Case "ASSEMBLY":
If attPart.TextString = "" Then
Exit For
Else:
Assembly = attPart.TextString
End If
End Select
Next attPart
'SQLstring = "Select PartID, QTY, AssemblyType From tblAssembly Where AssemblyName ='" & Assembly & "';"
SQLstring = "Select tblAssembly.PartID, tblAssembly.Quantity, tblAssembly.AssemblyType, tblParts.PartName From tblAssembly Inner Join tblParts ON tblAssembly.PartID = tblParts.PartID Where tblAssembly.AssemblyName ='" & Assembly & "';"
Set PlumbingREC = PlumbingDB.OpenRecordset(SQLstring, dbOpenDynaset)
If PlumbingREC.RecordCount > 0 Then
If PlumbingREC.EOF And PlumbingREC.BOF Then
Exit Sub
Else:
PlumbingREC.MoveFirst
Y = 0
Do While Not PlumbingREC.EOF
ReDim roughPart(Y, cntAssembly) As String
ReDim roughPartQty(Y, cntAssembly) As Integer
If PlumbingREC.Fields("AssemblyType").value = "Rough" Then
roughPart(Y, cntAssembly) = PlumbingREC.Fields("PartName").value
roughPartQty(Y, cntAssembly) = PlumbingREC.Fields("Quantity").value
Y = Y + 1
End If
PlumbingREC.MoveNext
Loop
PlumbingREC.MoveFirst
Y = 0
Do While Not PlumbingREC.EOF
ReDim topoutPart(Y, cntAssembly) As String
ReDim topoutPartQty(Y, cntAssembly) As Integer
If PlumbingREC.Fields("AssemblyType").value = "TopOut" Then
topoutPart(Y, cntAssembly) = PlumbingREC.Fields("PartName").value
topoutPartQty(Y, cntAssembly) = PlumbingREC.Fields("Quantity").value
Y = Y + 1
End If
PlumbingREC.MoveNext
Loop
End If
End If
PlumbingREC.Close
Set PlumbingREC = Nothing
Summary of what I am trying to do:
I have 1 block, "Assembly" with 1 attribute "ASSEMBLY". The value of that Attribute is an AssemblyName stored in an *.mdb file. So my parts are stored in a DB instead of in multiple attributes in a block.
One Assembly block may have an AssemblyName which references only one or two parts, however another may have 20 to 30 parts. So When I create (redim) my array ...roughPart(X,Y) There may be only 2 rows of data in the first column, and then when the next Assembly block is Queried, and it has 30 parts there will be 30 rows in that column. Is that a problem??
Thanks in Advance for your help...I hope this is clear enough, I'll post more detail if you need it...
Thanks