Loop through records

[Trever B]

Would like help on opening a query in vba and reading every record
changing if necessary. Need to know how to :-

1) Updating file

2) Stop when end of records reached

3) Close file

 

[Allan Murphy]

You could use an update query. Select the field(s) that you want to update
by setting the update to criteria using an if statement etc.

 

[Trever B]

Thanks Allan but does not help.

How do I open records & find out when i am at the end of the records.

Hope someone else can help

 

[Trever B]

Thanks Mike

Have tried the following but it does not like "rs.edit" for Info running
windows nt

Can you please help me get it right.

Dim db As Database

Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Directions")
rs.MoveFirst
Do While Not rs.EOF
rs.Edit 'does not like this why?
rs!Dir = UCase(rs!Dir)
rs.Update
rs.MoveNext
Loop

rs.Close

 

[Duane Hookom]

It doesn't get an more efficient or easier than an update query:

UPDATE Directions
SET [Dir] = UCase([Dir]);

 

[Trever B]

Thanks

That would be helpful if i wanted all records to be updated but the
situation is I have to read every record in order as one field has to be the
same as the one above but only if current field is null.

Thats why i need it to work

It doesn't get an more efficient or easier than an update query:

UPDATE Directions
SET [Dir] = UCase([Dir]);

--
Duane Hookom
MS Access MVP

Thanks Mike

Have tried the following but it does not like "rs.edit" for Info running
windows nt

Can you please help me get it right.

Dim db As Database

Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Directions")
rs.MoveFirst
Do While Not rs.EOF
rs.Edit 'does not like this why?
rs!Dir = UCase(rs!Dir)
rs.Update
rs.MoveNext
Loop

rs.Close

 

[Dirk Goldgar]

Thanks Mike

Have tried the following but it does not like "rs.edit" for Info
running windows nt

Can you please help me get it right.

Dim db As Database

Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Directions")
rs.MoveFirst
Do While Not rs.EOF
rs.Edit 'does not like this
why? rs!Dir = UCase(rs!Dir)
rs.Update
rs.MoveNext
Loop

rs.Close

Your problem is probably because Access thinks rs is an ADODB recordset
(which doesn't have an Edit method), not a DAO recordset. So declare it
more explicitly:

Dim rs As DAO.Recordset

Note two things:

1. The line ...

rs.MoveFirst

.... is unnecessary in this case, since the freshly opened recordset will
be positioned at the first record, if there is one. In fact, calling
MoveFirst when the recordset is empty will cause an error.

2. As others have said, for this particular process, an update query
would be *much* more efficient. Of course, there can be times when an
update query isn't appropriate or feasible, but it's always a good idea
to look for an SQL solution first.

 

[Trever B]

Dirk Thanks very much took a long time exactly what I wanted.

 

[Duane Hookom]

I guess I missed anything in your code that met these new requirements.

--
Duane Hookom
MS Access MVP

Thanks

That would be helpful if i wanted all records to be updated but the
situation is I have to read every record in order as one field has to be
the
same as the one above but only if current field is null.

Thats why i need it to work

It doesn't get an more efficient or easier than an update query:

UPDATE Directions
SET [Dir] = UCase([Dir]);

--
Duane Hookom
MS Access MVP

Thanks Mike

Have tried the following but it does not like "rs.edit" for Info
running
windows nt

Can you please help me get it right.

Dim db As Database

Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Directions")
rs.MoveFirst
Do While Not rs.EOF
rs.Edit 'does not like this why?
rs!Dir = UCase(rs!Dir)
rs.Update
rs.MoveNext
Loop

rs.Close




:

Trever B wrote:
Would like help on opening a query in vba and reading every record
changing if necessary. Need to know how to :-

1) Updating file

2) Stop when end of records reached

3) Close file

All of this is found in help EOF is what you check for to see if you
are
at
the end.

99.9% of the time an update query will do what you want *MUCH* faster
and
with no need to write code.

If you have eyeball each record and make a decision based on some
unrelated
issue then you are in the .1%