TheSwamp
Code Red => VB(A) => Topic started by: Kheilmann on November 16, 2006, 01:17:46 PM
-
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
-
for some reason, I'm thinking you can only redim a single dimension array.
-
The notes in Help say you can only change the last dimension when using Preserve, but I do not understand what that means...
It means that once you have have dimensioned the array to its initial size, like so:
ReDim roughPart(Y, cntAssembly) As String
in your loop, each subsequent iteration can only substitute a new value for cntAssembly. The value of Y cannot change.
One way you can accomplish your goal is to figure out the record counts up front so that the first dimension of each array can be established before you start looping.
-
At this point I'm considering calling a procedure which will take the data found and export it immediately to an Excel Workbook. Instead of quering all the data and compiling into hundreds of variables and then sending those variables to the spreadsheet. I think this would eliminate the need for my arrays...
Would you agree? Or I am missing something?
BTW..
When I read the Help again for ReDim..it made more sense..
You can only change the size of the last dimension, and you can not change the Number of dimensions at all.
So if you start with a single you can not change to a 2 dim array. However, if you have one dim you can change the size of that dim any time, and if you have a multi dim array you can only change the size of the last dim (2nd for double, 3rd for 3 tier, etc.)
Which means if I want to use this method I will have to preset my first Dim (Rows) to a large enough # to hold all the possible parts, and then I can change the # of my 2nd dim (Columns) whenever a new Assembly is found.
I think exporting the data to excel immediately will be much easier than compiling the data into an Array first. I will do that unless one of you has a reason why I should not.
Thanks again for your help...
-
Thanks Chuck;
The problem I have with presetting it is 2 fold:
1. When I use my SQLstring to query my DB the recordcount for PlumbingREC is always 1. Is that because I am doing an Inner Join?
2. The other problem is that the first block queried may need the first Dim of my array to be 5 entries and the next block may need 30. So it seems to me that I would have to do a recordcount of all my Assemblies then reloop through the Assemblies to apply the parts to the Array.
Am I right in thinking this?
-
I totally agree on exporting to Excell inmediatly as long as it not neccesary to rework your data (combined sorting and filtering stuff)
A workaround for creating jagged arrays is using the collection type in an array like this:
Sub test()
Dim Cl() As New Collection
ReDim Preserve Cl(2)
Cl(0).Add ("YourStuff0.1")
Cl(0).Add ("YourStuff0.2")
Cl(0).Add ("YourStuff0.3")
Cl(0).Add ("YourStuff0.4")
Cl(1).Add ("YourStuff1.1")
Cl(1).Add ("YourStuff1.2")
Cl(1).Add ("YourStuff1.3")
Cl(1).Add ("YourStuff1.4")
Cl(2).Add ("YourStuff2.1")
ReDim Preserve Cl(3)
Cl(3).Add ("YourStuff3.1")
Cl(3).Add ("YourStuff3.2")
Cl(3).Add ("YourStuff3.3")
Cl(3).Add ("YourStuff3.4")
End Sub
You can add in two dimensions this way. Drawback the collection object is slow in comparison with true arrays.
So if you don't need to keep the data in memory... don't do it.
-
Thanks Chuck;
The problem I have with presetting it is 2 fold:
1. When I use my SQLstring to query my DB the recordcount for PlumbingREC is always 1. Is that because I am doing an Inner Join?
Did you move to the last record (.movelast) because the count will not show the actual size until all records are really loaden into the recordset
2. The other problem is that the first block queried may need the first Dim of my array to be 5 entries and the next block may need 30. So it seems to me that I would have to do a recordcount of all my Assemblies then reloop through the Assemblies to apply the parts to the Array.
Am I right in thinking this?
Maybe I'm mistaking what you want but If you know the number of records and the number of fields (Recordset.Fields.Count)you can redim an array just right.
On the other hand if you already the data in a recordset I see no need to transfer it in an array without any special reason.
-
Kevin, try to use GetRows method:
Here is my program code snip:
Private Sub UserForm_Initialize()
On Error GoTo ErrorHandler
Dim Cnxn As ADODB.Connection
Dim rstNew As ADODB.Recordset
Dim strCnxn As String
Dim strSQL As String
Dim retArr As Variant
Dim i As Long, j As Long
' Open connection
strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\AUTOLISP\LISPS\VBA\ACCESS\data.mdb;"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn
' Open table
Set rstNew = New ADODB.Recordset
strSQL = "prot_Viabilita" ' table name
rstNew.Open strSQL, Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable
[b]retArr = rstNew.GetRows[/b]
Me.Caption = "Data from table " & Chr(34) & strSQL & Chr(34)
'
' Transpose record data and populate list box
'
ReDim dataArr(UBound(retArr, 2), UBound(retArr, 1)) As String
For i = 0 To UBound(retArr, 1)
For j = 0 To UBound(retArr, 2)
dataArr(j, i) = CStr(retArr(i, j))
Next
Next
ListBox1.List() = dataArr
' clean up
rstNew.Close
Cnxn.Close
Set rstNew = Nothing
Set Cnxn = Nothing
Exit Sub
ErrorHandler:
' clean up
If Not rstNew Is Nothing Then
If rstNew.State = adStateOpen Then rstNew.Close
End If
Set rstNew = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
>'J'<
-
Can you switch the y and cntassembly variables so y is the last one?
-
Quicksilver,
Actually that would work. Funny, it's such as easy answer. Though I like using the export to Excel right away method, it has worked 10000% better than I ever imagined, so I will not be using the Array scenario anyway.
I appreciate everyones input and help.
Hope you all have a great weekend.
-
A day late and a penny short. :-D
-
A trick I often use whenever I have a scenario where I would like to grow both dimensions of the array (if I actually did it that way) would be to create an array of arrays ... I know it sounds a bit crazy, but if you consider the way the arrays are handled, the concept is exactly the same except you have to modify the variable a little differently.
Consider the following code
Sub VarTest()
Dim Var1() As Variant
Dim VarA() As Variant
Dim VarB() As Variant
Dim X, Y As Integer
ReDim Preserve VarB(3)
For X = 0 To UBound(VarB)
ReDim Preserve Var1(3)
For Y = 0 To UBound(Var1)
Var1(Y) = X & "," & Y
Next Y
VarB(X) = Var1
Next X
MsgBox GetMDimArray(VarB, 3, 2)
VarB = SetMDimArray(VarB, 3, 2, "This Is A Test")
MsgBox GetMDimArray(VarB, 3, 2)
End Sub
Function GetMDimArray(ByRef MyArray As Variant, X As Integer, Y As Integer) As Variant
GetMDimArray = MyArray(X)(Y)
End Function
Function SetMDimArray(ByRef MArray As Variant, X As Integer, Y As Integer, NewValue As Variant) As Variant
MArray(X)(Y) = NewValue
SetMDimArray = MArray
End Function
As you can see, there is actually an array being placed in the main array, thus you can easily access the depth of the array by using multiple dimensions after the fact .i.e. MyArray(X)(Y). Conceivably, you should be able to add a third or fourth dimension making it MyArray(W)(X)(Y)(Z).
This picture should clarify the idea.