Having Trouble Poplulating an Array (1 Viewer)

[gmann]

Having Trouble with an Array, ChemicalArray(65)
Basically i want this code to search through records for unique values (chemicals) in this one field (parameter). and create a new table with the values in the array as fields.

For Some reason the first paramater is the only one that gets inputed into the array.

Does it need to be opened a different way, other than OpenDynaSet
Because it seems like it doesn't move to the next record.

Any Help Would be greatly appreciated
Thanks Greg

Private Sub Command36_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim ChemicalArray(65) As String
Dim CurrentArrayRec As Integer
Dim i As Integer
Dim fldLoop As Field
Dim prpLoop As Property
Dim CurrentRecord As Integer
Dim TempChemical As String
Dim tdfNew As TableDef

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("LAB10", dbOpenDynaset)
CurrentArrayRec = 0
CurrentRecord = 0
i = 0

' Create a new TableDef object.
Set tdfNew = dbs.CreateTableDef("New")

With rst
rst.MoveFirst
Do While Not rst.EOF

'Checks if the parameter is already in the array
For i = 0 To CurrentArrayRec
'If the parameter is already in the array
'it exits out of the For Loop
If ChemicalArray(i) = ([parameter]) Then
Exit For
End If
'if it is at the last record, and it hasn't found the parameter
'it will add the parameter to the Array
If i = CurrentArrayRec Then
ChemicalArray(i) = ([parameter])
CurrentArrayRec = 1 + CurrentArrayRec
Exit For
End If

Next i

rst.MoveNext
Loop

i = 0
With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' Current Db

'Should generate the chemical fields from the array
For i = 0 To CurrentArrayRec
If ChemicalArray(i) = "" Then
Exit For
End If
.Fields.Append .CreateField(ChemicalArray(i), dbText)

Next i
'Generate other fields here
.Fields.Append .CreateField("Station", dbText)
.Fields.Append .CreateField("Elevation", dbInteger)

End With

dbs.TableDefs.Append tdfNew

'//////////////////////////////////////////////////////////////////////
'Input the data from the lab table to the new table

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

.Close
End With

dbs.Close

End Sub

 

[Travis]

If the Array in the Local Values area does not seem to have all of the values you are expecting then place a Break Point on this code and use the F8 key to walk through each line. Check the values of each variable and see if you get what you expect.


Do While Not rst.EOF

'Checks if the parameter is already in the array
For i = 0 To CurrentArrayRec
'If the parameter is already in the array
'it exits out of the For Loop
If ChemicalArray(i) = ([parameter]) Then
Exit For
End If
'if it is at the last record, and it hasn't found the parameter
'it will add the parameter to the Array
If i = CurrentArrayRec Then
ChemicalArray(i) = ([parameter])
CurrentArrayRec = 1 + CurrentArrayRec
Exit For
End If

Next i

rst.MoveNext
Loop


Another way to populate an array with values is to use "GetRows". You can create a query that only returns the Unique Chemicals and populate the array that way. Then use the Ubound/Lbound functions for the array size/loops.

 

[Rich]

Why would you turn values into fields?

 

[gmann]

Thanks for your Help, I fixed the problem

I had to convert them into field names for reports.
Its the way the DB is set up, there isn't a table for all the chemicals, just a field called parameter that u can pick them from.
There is over 1500 chemicals, thats why it is done this way

Greg