LOOP WITHIN A LOOP

Good morning,

I am using Access 2000 and just about to write my first database. I have
some code for looping through the records in a certain field but is it
possible to have an inner loop which will lop through the reords for the
first field then go to the next field and lop again and so on.......

Yes, you can loop through the Fields of the Recordset, as well as looping
through the records:
For i = 0 To rs.Fields.Count -1
Do While Not rs.EOF
rs.MoveNext
Loop
Next

Realistically Sean, I can't think of a practical reason to do that. You
rarely need to loop all records in Access. If you are trying to alter
values, an UPDATE query will usually be more efficient.

If you did want to alter the fields, it would be much more efficient to loop
the fields in the record (a single Edit and Update) instead of performing an
Edit and Update on every field again through every record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SEAN DI''''ANNO" wrote in message
news
Good morning,

I am using Access 2000 and just about to write my first database. I have
some code for looping through the records in a certain field but is it
possible to have an inner loop which will lop through the reords for the
first field then go to the next field and lop again and so on.......

What's the end goal of what you want to accomplish?

You probably don't want to loop through the fields as the outer loop,
because depending on the cursor type you've chosen for your recordset, you
may not be able to get back to the first record to loop again.

You're probably better off looping through field in rs.fields as the inner
loop rather than the outer loop.

HTH;

Amy

"SEAN DI''''ANNO" wrote in message
news
Good morning,

I am using Access 2000 and just about to write my first database. I have
some code for looping through the records in a certain field but is it
possible to have an inner loop which will lop through the reords for the
first field then go to the next field and lop again and so on.......

"SEAN DI''''ANNO" wrote in
message news
Good morning,

I am using Access 2000 and just about to write my first database.
I have
some code for looping through the records in a certain field but
is it
possible to have an inner loop which will lop through the reords
for the
first field then go to the next field and lop again and so
on.......

Sean,

If you loop through all rows for one column, and then do it again,
then you'll be going through the table once per column. This will
be extraordinarly costly.

If you're going to take the costly step of looping through a table
in the first place, operate on every column at once. That way, you
only need to loop through the table once.

The earlier comments about using standard SQL statements if at all
possible should be taken seriously.


Sincerely,

Chris O.

PS Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.

Thanks guys, I think I understand what you mean

Outer loop - Loop through record 1 to end of records
Inner loop - Loop through each field within the record

I will explain why I want to do this.

I am writing a database to monitor the accuracy of new data entered. (It is
exported into access and not input) I have to give each record a data score
depending on accurate the data is. The only way I can think to do this is to
loop the fields and then using the value of the field name and the value
entered for the field, look up any SHOULD NOT USE VALUES stored in another
table using a DLOOKUP function (I think...ha ha)

How do I know the name of the field that the loop is on. Is it
rs.fields.value?

"Chris2" wrote:


"SEAN DI''''ANNO" wrote in
message news
Good morning,

I am using Access 2000 and just about to write my first database.
I have
some code for looping through the records in a certain field but
is it
possible to have an inner loop which will lop through the reords
for the
first field then go to the next field and lop again and so
on.......

Sean,

If you loop through all rows for one column, and then do it again,
then you'll be going through the table once per column. This will
be extraordinarly costly.

If you're going to take the costly step of looping through a table
in the first place, operate on every column at once. That way, you
only need to loop through the table once.

The earlier comments about using standard SQL statements if at all
possible should be taken seriously.


Sincerely,

Chris O.

PS Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.

The field name is:
rs.Fields(i).Name
in the example loop code I posted.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SEAN DI''''ANNO" wrote in message
...
Thanks guys, I think I understand what you mean

Outer loop - Loop through record 1 to end of records
Inner loop - Loop through each field within the record

I will explain why I want to do this.

I am writing a database to monitor the accuracy of new data entered. (It
is
exported into access and not input) I have to give each record a data
score
depending on accurate the data is. The only way I can think to do this is
to
loop the fields and then using the value of the field name and the value
entered for the field, look up any SHOULD NOT USE VALUES stored in another
table using a DLOOKUP function (I think...ha ha)

How do I know the name of the field that the loop is on. Is it
rs.fields.value?

"Chris2" wrote:


"SEAN DI''''ANNO" wrote in
message news
Good morning,

I am using Access 2000 and just about to write my first database.
I have
some code for looping through the records in a certain field but
is it
possible to have an inner loop which will lop through the reords
for the
first field then go to the next field and lop again and so
on.......

Sean,

If you loop through all rows for one column, and then do it again,
then you'll be going through the table once per column. This will
be extraordinarly costly.

If you're going to take the costly step of looping through a table
in the first place, operate on every column at once. That way, you
only need to loop through the table once.

The earlier comments about using standard SQL statements if at all
possible should be taken seriously.

On Sat, 26 Nov 2005 02:37:03 -0800, SEAN DI''''ANNO
wrote:

Thanks guys, I think I understand what you mean

Outer loop - Loop through record 1 to end of records
Inner loop - Loop through each field within the record

I will explain why I want to do this.

I am writing a database to monitor the accuracy of new data entered. (It is
exported into access and not input) I have to give each record a data score
depending on accurate the data is. The only way I can think to do this is to
loop the fields and then using the value of the field name and the value
entered for the field, look up any SHOULD NOT USE VALUES stored in another
table using a DLOOKUP function (I think...ha ha)

I VERY much doubt that either VBA or looping through records and
fields is necessary in order to do this.

Access (or any SQL-language RDBMS) is very good at flexible Queries
which can retrieve all your data in one swell foop, without doing it
"procedurally". If you have a table of forbidden field values, I
strongly suspect an Unmatched query joining your table to the
forbidden-values table will validate your data MUCH more quickly than
any loop such as you suggest. DLookUp is powerful - but it can't lay
much claim to being fast or efficient!

John W. Vinson[MVP]

"SEAN DI''''ANNO" wrote in
message ...
Thanks guys, I think I understand what you mean

Outer loop - Loop through record 1 to end of records
Inner loop - Loop through each field within the record

I will explain why I want to do this.

I am writing a database to monitor the accuracy of new data
entered. (It is
exported into access and not input) I have to give each record a
data score
depending on accurate the data is. The only way I can think to do
this is to
loop the fields and then using the value of the field name and the
value
entered for the field, look up any SHOULD NOT USE VALUES stored in
another
table using a DLOOKUP function (I think...ha ha)

How do I know the name of the field that the loop is on. Is it
rs.fields.value?

"Chris2" wrote:

Sean,

I am not sure why this operation cannot be accomplished with one
UPDATE statement.

The narratives given so far present no compelling reason to use VBA
that I can see.

It is difficult to tell, though, as I am not sure what your schema
is, or what your source data and desired results are.


Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.


Pure Guesswork:

UPDATE DataEntryScores AS DS1
INNER JOIN
DataEntry DE1
ON DS1.pk = DE1.pk
SET DS1.col1 = your accuracy check on DE1.col1
,DS1.col2 = your accuracy check on DE1.col2
,DS1.col3 = your accuracy check on DE1.col3
,etc.
,etc.
,etc.

Or possibly the SET will look like:

SET DS1.col1 = your accuracy check on all DE1 columns


Sincerely,

Chris O.

Good morning ladies and gentleman. First Of all, a big thank you to everyone
who has kindly given me advice and support in regards to my question. I did
previously pose the question as (BASIC CODE TO USE IN MY FIRST DATABASE but I
think the question implied that I was being lazy and wanted some one to do
all the work for me. Not the case, I have just found it hard knowing what
syntax to use for code. I have looked for a good book to guide me through
writing VB in accedss for beginners but can not find one. If anyone knows
of a good book which I can order on the internet to teach me properly I would
really appreciate it. Ps I live in the UK. In reagrds to my question which
I think is the engine to the whole database I need to write;

Chris2 - I really don ot know how I can achieve this with a single update
statement. In essense my database will work by;

Comparing a list of records containing contact addresses against a new file
of contact addresses with the "new" records being the ones to validate.

The fields will be along the lines of;

Contact Name, Address 1, Address 2, Address 3 Tel No, Post Code, Fax No

The reason I asked the loop within a loop question was so that I can loop
through all of the fields for each record anh have a running score. Eg

Outer Loop From 1 to end of new records
No Of New Records Counter=0
If outerloop record is not duplicate add 1 to New Records Counter

Inner Loop From Field 1 to End Of fields
DataScore=20 (20 Fields To Validate in total so 20 is potential best
score)

VALIDATION CHECKS
Compare Value for Outerloop, innerloop in forbidden values

John Vinson, looking at your reply - it has got me thinking, a simple query
links one table to another e.g. Customer No to Customer No, I guess I could
link the forbidden values table to the values of inner loop
(rs.fields(i).name and outer loop by just using as the criteria??

If the value matches up to a value in forbidden values then
DATASCORE=DATASCORE-1
NEXT INNER LOOP

RECORD SCORE IN TABLE (so that reporting can be done)

NEXT OUTERLOOP

And thats it...easy in theory...ha ah. Thanks again, I have slowly but
surely gaining confidence from all of the input and I hope in time might be
able to offer the same help to others.




"Chris2" wrote:


"SEAN DI''''ANNO" wrote in
message ...
Thanks guys, I think I understand what you mean

Outer loop - Loop through record 1 to end of records
Inner loop - Loop through each field within the record

I will explain why I want to do this.

I am writing a database to monitor the accuracy of new data
entered. (It is
exported into access and not input) I have to give each record a
data score
depending on accurate the data is. The only way I can think to do
this is to
loop the fields and then using the value of the field name and the
value
entered for the field, look up any SHOULD NOT USE VALUES stored in
another
table using a DLOOKUP function (I think...ha ha)

How do I know the name of the field that the loop is on. Is it
rs.fields.value?

"Chris2" wrote:

Sean,

I am not sure why this operation cannot be accomplished with one
UPDATE statement.

The narratives given so far present no compelling reason to use VBA
that I can see.

It is difficult to tell, though, as I am not sure what your schema
is, or what your source data and desired results are.


Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.


Pure Guesswork:

UPDATE DataEntryScores AS DS1
INNER JOIN
DataEntry DE1
ON DS1.pk = DE1.pk
SET DS1.col1 = your accuracy check on DE1.col1
,DS1.col2 = your accuracy check on DE1.col2
,DS1.col3 = your accuracy check on DE1.col3
,etc.
,etc.
,etc.

Or possibly the SET will look like:

SET DS1.col1 = your accuracy check on all DE1 columns


Sincerely,

Chris O.

Morning again guys.......Sorry shoudl have pointed out that IFONLYIKNEWCODE
AND Sean Di'Anno are one in the same...Sean is the user name I use at home.

"SEAN DI''''ANNO" wrote:

Good morning,

I am using Access 2000 and just about to write my first database. I have
some code for looping through the records in a certain field but is it
possible to have an inner loop which will lop through the reords for the
first field then go to the next field and lop again and so on.......