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 CopyDataToRecip eTables()

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,
adLockOptimisti c
rsTable.Open strTable, CurrentProject. Connection, adOpenDynamic,
adLockOptimisti c

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!

Nov 13 '05 #1

Subscribe Reply

2729

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 CopyDataToRecip eTables()

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,
adLockOptimisti c
rsTable.Open strTable, CurrentProject. Connection, adOpenDynamic,
adLockOptimisti c

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.NAM E) = rsMain!Field1
Next
rsTable.Update

HTH

Nov 13 '05 #2

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!

Nov 13 '05 #3

Eric Schittlipz

"Randy Harris" <ra***@SpamFree .com> wrote in message
news:uJ******** **********@news svr33.news.prod igy.com...

"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 CopyDataToRecip eTables()

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,
adLockOptimisti c
rsTable.Open strTable, CurrentProject. Connection, adOpenDynamic,
adLockOptimisti c

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.NAM E) = 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).

Nov 13 '05 #4

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 CopyDataToRecip eTables()

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,
adLockOptimisti c
rsTable.Open strTable, CurrentProject. Connection, adOpenDynamic,
adLockOptimisti c
rsTable.AddNew
For Each fld In rsTable.Fields
rsTable(fld.Nam e) = 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!

Nov 13 '05 #5

Similar topics

8852

How do I create pdf reports from Access?

by: dog | last post by:

I've seen plenty of articles on this topic but none of them have been able to solve my problem. I am working with an Access 97 database on an NT4.0 machine, which has many Access reports. I want my users to be able to select a report, click on a command button on a form, which will then automatically create the report as a pdf file and save it to the user's machine. I am using Adobe Acrobat (5.0 I think) and have Adobe Distiller as a

Microsoft Access / VBA

3306

How many threads to create?

by: SamIAm | last post by:

Hi am creating a email application that needs to mail out a very large amount of emails. I have created a multithreaded c# application that using message queuing. I have created a threadpool of 5 threads and each thread checks the queue, receives the message and sends an email. How do I determine the right amount of threads to create? Thanks, S

C# / C Sharp

3238

Create Form from Async-Callback

by: Roemer | last post by:

Hi all I stumbled over a new problem: I have a programm with just a class that is asynchronous listening for network connections. As soon as someone connected, a new form needs to be created. The Form gets created but hangs after creation. I'ts logical that that happens because the new Form doesn't get a Message Loop. The Message Loop is created on Application.Run() on the Main Method on the Main Thread. The Callback is (normally)...

.NET Framework

7397

create a dynamic array of pointers with initial values of NULL

by: sandy | last post by:

I need (okay, I want) to make a dynamic array of my class 'Directory', within my class Directory (Can you already smell disaster?) Each Directory can have subdirectories so I thought to put these in an array. The application compiles but aborts without giving me any useful information. What I suspect is happening is infinite recursion. Each Directory object creates an array of Subdirectories each of which has an array of...

C / C++

12428

Can Access create Word documents?

by: etuncer | last post by:

Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is this: can Access create the document and place it as an OLE object to the relevant table? Any help is greatly appreciated. Ricky

Microsoft Access / VBA

3969

How to create a Multi dimensional array

by: SM | last post by:

Hello I'm trying to create a multi dimensional array in JavaScript, but after some reading i still can't figure out how to apply it to my model. Here it is: I have a list A and for each item in the list A i want to associate an undetermined number of items. The complication for me is the fact that the items to associate in list A are undetermined.

Javascript

11351

Create C# controls names at runtime

by: Matt | last post by:

Hi all, I'm trying to create a system where it reads a number of records from a database and then creates a row in the GUI that contains a single field from the database and a button that has a reference to the record. This way, once the task has been accomplished, the button can be pressed next to the field and a time-stamp is placed into the database. Can anyone point me in the direction of either a tutorial or

C# / C Sharp

6902

File.Create - ArgumentException was unhandled - illegal characters in path

by: Vlad | last post by:

I am having problems using the file.create method within a function that is called when looping through an array of filepaths. If I call my function with a hardcoded file path --C:\Temp.txt the function creates the file as expected. When I loop through my array I get the error - "ArgumentException was unhandled - Illegal characters in path" The value "C:\Temp.txt" is the first value in the array - as it works

Visual Basic .NET

1260

To streamline or not to streamline...that is the question...

by: rw2007 | last post by:

I'd like to hear what you think about this... I'm working on a new architecture for an existing software product. I want this to be a flexible - and especially extendable - architecture for future modifications. I've been researching best practices artices and seeking advice on the proper way to architect the product. Right now, I have the architecture broken out into the following layers: - UI / UI Process layer - Process Service layer...

Software Development

8459

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8378

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8890

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8791

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8653

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7398

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6206

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5677

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4202

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration