Can I create a loop to streamline this code?

[Kathy]

Hi All,
I am using Access 2000.

I would like to streamline this code by using a variable for the column
name. I have three tables with 255 columns each that I would like to
populate with the data from one table that has 1 column. Each of the
three tables will each up with 1 record with 255 columns. This is the
code I wrote (in brief) to demonstare whate I want to do. Is there an
easier way? I don't really want to type 500 lines of code for each
table!
Thanks,
Kathy

Public Sub CopyDataToRecipeTables()

Dim rsMain As ADODB.Recordset
Dim rsTable As ADODB.Recordset

Dim strMain As String
Dim strTable As String

Set rsMain = New ADODB.Recordset
Set rsTable = New ADODB.Recordset

strMain = "Select FIELD1, RowID From RecipeData"
strTable = "Select * FROM Recipe1"

rsMain.Open strMain, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rsTable.Open strTable, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

rsTable.AddNew
rsTable!Field1 = rsMain!Field1
rsMain.MoveNext
rsTable!Field2 = rsMain!Field1
rsMain.MoveNext
rsTable!Field3 = rsMain!Field1
rsMain.MoveNext
rsTable!Field4 = rsMain!Field1
rsTable.Update

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Randy Harris]

"Kathy" <Kathy.merecat.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

Hi All,
I am using Access 2000.

I would like to streamline this code by using a variable for the column
name. I have three tables with 255 columns each that I would like to
populate with the data from one table that has 1 column. Each of the
three tables will each up with 1 record with 255 columns. This is the
code I wrote (in brief) to demonstare whate I want to do. Is there an
easier way? I don't really want to type 500 lines of code for each
table!
Thanks,
Kathy

Public Sub CopyDataToRecipeTables()

Dim rsMain As ADODB.Recordset
Dim rsTable As ADODB.Recordset

Dim strMain As String
Dim strTable As String

Set rsMain = New ADODB.Recordset
Set rsTable = New ADODB.Recordset

strMain = "Select FIELD1, RowID From RecipeData"
strTable = "Select * FROM Recipe1"

rsMain.Open strMain, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rsTable.Open strTable, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

rsTable.AddNew
rsTable!Field1 = rsMain!Field1
rsMain.MoveNext
rsTable!Field2 = rsMain!Field1
rsMain.MoveNext
rsTable!Field3 = rsMain!Field1
rsMain.MoveNext
rsTable!Field4 = rsMain!Field1
rsTable.Update

End Sub

Kathy, it's very hard to imagine why you would want to put the same data in
every field, but you can iterate over the fields this way:

Dim fld as Field

[your code to open both recordsets]

For Each fld in rsTable.Fields
rsTable(fld.NAME) = rsMain!Field1
Next
rsTable.Update

HTH

[Kathy]

Thank You! That worked perfectly. I realize that I didn't include the
code that loops through the RecipeData table.
The recipe data is an imported text file 706 rows long. I must convert
that to a series of tables one row long but 255 columns wide so that the
recipe data can be viewed in a series of forms that resemble the machine
interface.

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Eric Schittlipz]

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:uJ******************@newssvr33.news.prodigy.c om...

"Kathy" <Kathy.merecat.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

Hi All,
I am using Access 2000.

I would like to streamline this code by using a variable for the column
name. I have three tables with 255 columns each that I would like to
populate with the data from one table that has 1 column. Each of the
three tables will each up with 1 record with 255 columns. This is the
code I wrote (in brief) to demonstare whate I want to do. Is there an
easier way? I don't really want to type 500 lines of code for each
table!
Thanks,
Kathy

Public Sub CopyDataToRecipeTables()

Dim rsMain As ADODB.Recordset
Dim rsTable As ADODB.Recordset

Dim strMain As String
Dim strTable As String

Set rsMain = New ADODB.Recordset
Set rsTable = New ADODB.Recordset

strMain = "Select FIELD1, RowID From RecipeData"
strTable = "Select * FROM Recipe1"

rsMain.Open strMain, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rsTable.Open strTable, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

rsTable.AddNew
rsTable!Field1 = rsMain!Field1
rsMain.MoveNext
rsTable!Field2 = rsMain!Field1
rsMain.MoveNext
rsTable!Field3 = rsMain!Field1
rsMain.MoveNext
rsTable!Field4 = rsMain!Field1
rsTable.Update

End Sub

Kathy, it's very hard to imagine why you would want to put the same data

in every field, but you can iterate over the fields this way:

Dim fld as Field

[your code to open both recordsets]

For Each fld in rsTable.Fields
rsTable(fld.NAME) = rsMain!Field1
Next
rsTable.Update

HTH

From the looks of things, it won't be the same value. Each time
rsMain!Field1 is used, it is referencing a different record, since rsMain.Mo
veNext is called each time. I think it might have helped if the recordsets
had been named "rstRead" and "rstWrite" so you can see what is going on.
This sort of routine would need some error handling since you are putting an
unknown number of records into a single record. What if there were more
than 255 records which won't fit into a single table? In fact, whatever
you're doing seems to be going against the grain of relational database
design where tables should be long and skinny, not short and fat (if that
explanation isn't too technical).

[Kathy]

Here is my procedure with your addition.
The reason for this project is not to store the Recipe in these tables
I'm making, but to display them in forms that look like the machine
interface screens and print them as a paper reference for the machine
operators.

I agree that this is an inefficent table structure. But it is what I
thought I would need to do to get the values to display in the forms I
created that resemble the machine interface.

The operator will click a button to import the recipe, convert it from
one long table to 3 wide tables and diplay the result in six forms in a
tabbed control. They can then choose to print any of the reports for
their parer backup.

The tables will be cleared until the next time someone wants to print a
recipe.

Public Sub CopyDataToRecipeTables()

Dim rsMain As ADODB.Recordset
Dim rsTable As ADODB.Recordset

Dim strMain As String
Dim strTable As String
Dim fld As Field
Set rsMain = New ADODB.Recordset
Set rsTable = New ADODB.Recordset

strMain = "Select FIELD1, RowID From Recipe Where RowID < 256"
strTable = "Select * FROM Recipe1"

rsMain.Open strMain, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rsTable.Open strTable, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
rsTable.AddNew
For Each fld In rsTable.Fields
rsTable(fld.Name) = rsMain!Field1
rsMain.MoveNext
Next
rsTable.Update

End Sub
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!