Having Trouble populating an array from table values 
Author Message
 Having Trouble populating an array from table values

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



Sat, 11 Dec 2004 03:54:28 GMT  
 Having Trouble populating an array from table values
This seems an inefficient way to go about this, since you can easily open a
recordset on a query that returns *only* unique values for [parameter]:

    Set rst = dbs.OpenRecordset( _
                    "SELECT DISTINCT [parameter] FROM LAB10;" )

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Quote:
> 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



Sat, 11 Dec 2004 04:55:29 GMT  
 Having Trouble populating an array from table values
Hi Greg

It seems that your main problem here is that you refer to an entity named
[parameter].  This is being resolved from somewhere (perhaps you have a
textbox of that name on your form?) but it is not referring to a field of
that name in your recordset.  For this, you need one of the following
syntaxes:
    rst![parameter]
    rst("parameter")
or, since you are within a "With rst" block:
    ![parameter]
    .Fields("parameter")

BUT, as Dirk has pointed out, you don't need to check for duplicates if you
eliminate them from your recordset in the first place:
    Set rst = dbs.OpenRecordset( _
                    "SELECT DISTINCT [parameter] FROM LAB10;" )

BUT BUT BUT.......
Why are you doing this anyway?  You want to create a table with fieldnames
corresponding to your data, one for each data value?  This is breaking the
first rule of normalized database design.

From what few clues you give about your application, it seems to me you
should have (at least) three tables:

Stations:
    StationID (autonumber, primary key)
    StationName (text, no duplicates)
    Elevation (numeric)

Parameters:
    ParamID (autonumber, primary key)
    ParamName (text, no duplicates - the parameter field from your LAB10
table)

Readings:
    StationID (foreign key related to Stations table)
    ParamID  (FK related to Parameters table)
    RecordedValue (numeric? - the data you would have stored in your "New"
table)
also, perhaps:
    Timestamp (date/time of the reading)

It is then an easy matter, if required, to make a crosstab query presenting
one row per station/timestamp, and one column per parameter.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.

Please post new questions or followups to newsgroup.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.

Please post new questions or followups to newsgroup.


Quote:
> 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



Sat, 11 Dec 2004 06:54:27 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Having trouble saving array in Session object

2. Using VBA to populate array from Access table

3. Populate Array From Table

4. Populating ComboBoxes with Values from Access 97 tables?

5. Populating Picture Array from query table

6. Method for populating a combo box (ASP) with values from an access table

7. Selecting a subset of a table and populating with a default value

8. Populating arrays and lookup tables

9. Write nonstring value to INI having no such value yet

10. Put array values in table?

11. Populate rectangular array with a one dimensional array

12. How to get Table value to Array?

 

 
Powered by phpBB® Forum Software