Access Module Beginner 3

[jhb11]

How can I loop through each individual value in a table and examine it and possible change it. I need a command similar to the variable = ActiveCell.Value command in excel. If someone could point me in the right direction I would be extremely grateful. Thanks in Advance.

Jason

 

[Junior1544]

looks like this would be a good use of an update query instead of a module(Impling a recordset)...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[hermanlaksko]

Dim DB as dao.database, RSet as dao.recordset
Dim TestThis
set db = currentdb
set rset=db.openrecordset("Select * From YrTbl&quot

do while not rset.eof
testthis=rset!yrfld
if testthis = YrValue
do your stuff
rset.edit
rset=testthis
rset.update
end if
rset.movenext
loop

Herman

 

[vb6novice]

jhb11,

Welcome to the world of RECORDSETS. Recordsets is what VBA uses to loop through a table. Obviously you have the concept of going through a table row by row, or column by column, comparing values to something and making changes if necessary. In Access Modules it is similar, but first you must tell VBA what is the data you want to work on. The data that you want to work on is a recordset. A recordset can be an entire table or can be some of the columns for all records, or some of the records for all columns, or some of the records for some of the columns. More on that later.

In Access VBA (Access 97), you must declare variables for the database that is to be worked on (kind of like telling Excel which workbook), and declare a variable for the recordset (kind of like telling Excel which worksheet). You do those as follows:

Dim dbs as Database
Dim rst as Recordset


Let's say you want to loop through ALL the records in a table called MyTable, and wanted to look in the third column (Field) called MyField. First you must tell it what database to work on. If it is the database you are working in you can set it using

Set dbs = currentDb

Then you must tell it what table within the current database you want to work with. You do that using

Set rst = dbs.OpenRecordset("MyTable&quot

The table is now open as a recordset and can be checked, written to, added to, deleted from, etc. To get to the third field we called MyField, you reference it similar to Excel cells. To set the value of MyField in the current record to 99 you would write

rst.Edit 'Not needed in Access 2000
rst![MyField] = 99
rst.Update 'Not needed in Access 2000

To read the value of MyField in into a variable called MyVar, you would write

MyVar = rst![MyField]

Brackets are required around field names only if they have spaces.

The other way to refer to the column is by its index (kind of like you word refer to an Excel worksheet, either by its name, such as Worksheets("Sheet1&quot, or its index number Worksheets(1). The third column in MyTable has index of 2 (because column index numbers start at 0). To use the column number to refer to it, you write

rst.Fields(2)


To actually loop through the code from top to bottom, the easiest way requires three lines of code plus the lines for the action to take.

Do Until rst.EOF = True
Action you want to take
rst.MoveNext
Loop


Remeber to close the recordset and database objects when you are finished with them
rst.Close
dbs.Close

Some people set the database and recordset to nothing (Set dbs = Nothing), but that always gives me an error.

Hope that helps.

 

[jhb11]

The Variable
Dim dbs As Database

is not recognized in Access 2000

 

[hermanlaksko]

1. Goto tools/refrences and select microsoft dao var 3.x
2. Dim dbs as dao.database

Herman

 

[jhb11]

I am getting an error 13 type mismatch using the following line of code:

Set rst = dbs.OpenRecordset("Person&quot

any suggestions

 

[Junior1544]

Is "Person" the name of a table??

I usualy find it much easier to use a SQL statement in my recordset's... But that's just me...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[hermanlaksko]

Try this:
Set rst = dbs.OpenRecordset("Select * From Person&quot

Should do the trick ;-)