Update a table in a loop

[hceonetman]

I need help writing a vb module that will open a table (Access 2000) and change the value of one field in each record. The field name is 'Include' and it is a Yes/No field. I would like a loop to change each record. I am somewhat of a newbie when it comes to vb code and the book I am using for reference is Access 97, which I understand, is a bit different.
Thanks for any help.

HCEONETMAN

Don't you have anything without Spam in it? I hate Spam.

 

[Trendsetter]

Dim rst as Recordset
set rst = CurrentDb.OpenRecordset("Select * From tblNames&quot
rst.MoveFirst
Do Until rst.EOF
rst.Update
rst!Include = True ' or Yes
rst.Update
rst.MoveNext
Loop
rst.Close
set rst = nothing

 

[Steve101]

Minor correction to the above post:

Dim rst as Recordset
set rst = CurrentDb.OpenRecordset("Select * From tblNames&quot
rst.MoveFirst
Do Until rst.EOF
rst.edit
rst!Include = True ' or Yes
rst.Update
rst.MoveNext
Loop
rst.Close
set rst = nothing


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Trendsetter]

Thanks Steve.... Done on the fly so it's easy to miss the obvious...

 

[Steve101]

Trendsetter,

Yes, know the feeling. One of the good things about this technology, is that its easy to review other people's contributions (and visa versa of course).

I read in your details that you've been using Access for 13 years. Wow, that got me thinking ... I've been using it as long (ie. from Version 1), but never thought that that was 13 years ago. Gee, must be getting old !!!

Anyway, sure I'm not meant to be reminiscing and rambling here, so will check out.

Be well !!


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[MichaelRed]

<< &quot; ... you've been using Access for 13 ... &quot; >>


I'd say a bit more than &quot;WOW&quot;, this couls only be from one of hte original &quot;Jet&quot; developers, as Ms. Only released Ms. A. ~~ 10.5 years ago

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[hceonetman]

Steve101/Trendsetter
Thanks, that looks like what I need. However, I inserted the code and get a compiler error on the line 'rst.edit'. The error indicates 'Method or data member not found'. In your code I replaced tblNames with the name of the table. I assume that is correct because it gets past that to the edit line within the loop before stopping. Also, I use the 'Step Over' command in the debug window but the error keeps repeating. I can see no change to the data in the table.
Correct me if I'm wrong, I see the 'Edit' command as equivalent to the old Foxpro 'Scatter memvar' which copies the current record to a memory array for manipulation. I'm not sure what the compiler finds wrong with it.

Thanks again,
Hceonetman

 

[Steve101]

Michael,

Thanks for the correction; you're probably right; I really don't know if its 10.5 or 13; its not important to me, and I'm probably guilty of believing the 13 years at face value.

As usual, thanks for your usual positive contributions to my responses,

Regards,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Trendsetter]

You may be right but what I remember is using dbase and Access as a toy for small bits of dbs. The DOS based versions of all other dbs needed copious amounts of enviroment setup code before you could contemplate the development of a form.... whereas with Access being graphical based on &quot;Windows 286&quot; (with 1Mb of RAM and 10Mb Hard disk) you could produce a &quot;toy&quot; database in hours instead of days or weeks. Nobody took it seriously at the time (which I thought was around 1990 which is the time I started that particular job) and it was not considered to be a programmer tool because it was all drag'n'drop and people without programming experience could make a useful stab at getting something to work..... The Black Art was on the way out.

 

[MichaelRed]

hmmmmmmmmmm ....

depends (I guess) on how 'rounded' you mean.


&quot;On Monday, September 16, Microsoft kicks off a month-long web celebration of Microsoft Access' 10 year anniversary. Yes, Microsoft Access 1.0 released in November of 1992. So, technically, we're a little bit early. We've worked with a variety of sites to offer you a wealth of new technical content, online chats, and the possiblity to win great prizes,
like a copy of Microsoft Access signed by Bill Gates!

Starting at about 10:00 AM PST on Monday, September 16, MSDN, microsoft.com, and Office Tools on the Web (OTOW) each begin hosting different content and activities, which we invite you to discover:

MSDN, located at

http://msdn.microsoft.com&quot;

The above poosted here by Thornmaster in Sept 2003, Search for Happy Birthday Access et al.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[hceonetman]

Gentlemen:
Back to the original subject: I still get a compile error and I think it may be because I'm not properly referencing the table. The line as I have it is
Set rst = CurrentDb.OpenRecordset(&quot;Select * From Road_Data_Types&quot
Road_Data_Types is the name of the table. The program halts with a compiler error 'Method or data member not found'. I have also tried putting square brackets or quotes around the table name with no success. Any ideas?

Thanks,
HCEONETMAN

 

[Trendsetter]

Michael
I have been trying to remember why I am a misanthrope!
you have just reminded me......

Surely you can now get a life

 

[MichaelRed]

quite possibly you are using Ms. A. 2K (or later). this would require the inclusion of the (lartest) DAO 'library' in your references and the Prepending of &quot;DAO.&quot; to the db and rs declarations.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

hceonetman,

back (again) to the subject ... There seems to be absoloutly NO reason to do this trivial exercise in code. ONE reason I did not respond to the &quot;subject&quot; in the beginning is that it APPEARS to just be an exercise (homework). The 'functionallity' is more easily (AND QUICKLY) achieved through a simple query. Subsquent posts only convince me of the 'low level' nature of the exercise. Sorry to rain on the parade with the off-topic posts.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[hceonetman]

MichaelRed,
I guess you could call it 'homework'. I am trying to get a better feel for VB code by working with some simple modules first, but it is part of a working application. And yes, a query will do the job, but there are times when a query is not enough and I'd like to be able to handle the code too. I will change the app to use the query, and create a new test app to work with the ADO/DAO issues.

Thanks again,
HCEONETMAN