Solved: Looping through a table

[MWE]

I admit, this is a pretty simple question ...

I have an Access2K DB with several tables. I want to loop through Table "A" and do various "examinations" of the fields (call them A1, A2, ...) for each record. How do I do that?

[geekgirlau]

This will loop through all fields in a specific table:

[VBA]
Sub TableLoop()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

On Error GoTo ErrHandler

Set db = CurrentDb
Set tdf = db.TableDefs("tblTest")

For Each fld In tdf.Fields
' your code here
Next fld

ExitHere:
On Error Resume Next
db.Close
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
End Sub

[/VBA]

If you want to perform the same actions on all tables you could do the following:


[VBA]
Sub TableLoop()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

On Error GoTo ErrHandler

Set db = CurrentDb

For Each tdf In db.TableDefs
' usually don't want to change system or temp tables
If LCase(Left(tdf.Name, 4)) <> "msys" And Left(tdf.Name, 1) <> "~" Then
For Each fld In tdf.Fields
' your code here
Next fld
End If
Next tdf


ExitHere:
On Error Resume Next
db.Close
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
End Sub

[/VBA]

[MWE]

Thanks for the prompt and informative reply.

[MWE]

Thanks again, but this is not working. It does what you thought I wanted, but not what I want.

I can not seem to work out the hierarchy from table to record to field. I don't really care which way Access thinks about things, i.e., Table.Record.Field or Table.Field.Record but I can not find (using the object browser) an object path that works either way.

So, clearly there is something I do not understand. Let me restate my need. Given a table "A" with fields "A1", "A2", ... and records 1, 2, 3, ... I want to loop through all records and examine, say, field A2.

FYI: I am somewhat hampered in my efforts because VBA Help in access does not work. Seems to work fine in other appls, but not in Access.

[Norie]

Try something like this.

[vba]

Set db = CurrentDb

Set rst = db.OpenRecordSet("A")

rst.MoveFirst

While Not(rst.EOF)

Msbox rst.Fields("A2").Value

rst.MoveNext

Wend[/vba]

[MWE]

Try something like this.

[vba]

Set db = CurrentDb

Set rst = db.OpenRecordSet("A")

rst.MoveFirst

While Not(rst.EOF)

Msbox rst.Fields("A2").Value

rst.MoveNext

Wend[/vba]

Thanks. This works.

Now to do it the way I ultimately need to, .MoveFirst and .MoveNext can not be used except for the first pass. This is handling the table like a roll of mag tape. I need to be able to index directly to specific records. So:

1. What Type is rst in the above example? Seems to me that it should be of type RecordSet (and that agrees with the object browser), and rst.RecordCount does give me a correct record count. But if I Dim rst as RecordSet, the statement

Set rst = db.OpenRecordset("Books")

fails. ("A" = "Books")

2. How do I refer to record "i"

Thanks again.

[Norie]

I'm sorry but I don't understand your questions fully.

What are you actually trying to do?

How is the statement failing?

[MWE]

I'm sorry but I don't understand your questions fully.
...
How is the statement failing?

Sorry that I did not make things clearer. Consider the two procedures below. The only difference is that rst is not declared/typed in Test1 and it is declared/typed in Test2:
[VBA]Private Sub Test1()
Dim db As DAO.Database
Dim I As Long
'Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Books")
rst.MoveFirst
While Not (rst.EOF)
I = I + 1
'
' other code
'
rst.MoveNext
Wend
MsgBox "# records processed = " & I
End Sub
[/VBA]
[VBA]Private Sub Test2()
Dim db As DAO.Database
Dim I As Long
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Books")
rst.MoveFirst
While Not (rst.EOF)
I = I + 1
'
' other code
'
rst.MoveNext
Wend
MsgBox "# records processed = " & I
End Sub[/VBA]
If Option Explict is "on" then
Test1 fails at compile at the Set rst = ... statement with a "variable not defined" error.
Test2 fails on execution at the Set rst = ... statement with a runtime error 13: Type MisMatch

If Option Explict is "off" then
Test1 runs to completion
Test2 fails on execution at the Set rst = ... statement with a runtime error 13: Type MisMatch

Thus if I have Option Explicit "off" and do not declare rst, the procedure runs. But if I declare rst what I think it should be, the procedure fails.

I'm sorry but I don't understand your questions fully.

What are you actually trying to do?
...

I want to search through all records of a dataset. The initial search can use the sequential methods of .movefirst, .movenext, etc. But later I will want to go to specific records and do not want to sequentially search through the records each time. It is not efficient to step through 6000 records when I know I want record 6001. Make sense?

[xCav8r]

I should be... [VBA]dim rst as DAO.Recordset[/VBA] If you want to return to specific records later, look at setting bookmarks. Alternatives in DAO to navigating to a particular record are find and seek.

[MWE]

I should be... [VBA]dim rst as DAO.Recordset[/VBA] If you want to return to specific records, look at setting bookmarks. See DAO documentation for more help on that.

Thanks. Unfortunately, my Access help is essentially non-functional. 75% of the time, I can search (or ask a question) and get the silly list of hits, but when I click on any hit, nothing happens. Help seems to work OK in all other MS applications. I guess I should be happy that MSHelp works as well as it does. On my previous day-to-day computer, every time I updated Office2K with a patch, SP, SR, etc., the utility of MSHelp declined. By the time I was running the most recent updates, MSHelp did not work at all for any appl. So on my current machine I am running SR-1.

So I play around with the browser and try to ferret out what might work. I am at another disadvantage in that my thought process and Access are close to orthogonal. I honestly do not understand why MS has made Access so cumbersome and illogical.

I thought of using bookmarks (their intent seems almost logical) but believed that there must be some way to simply access the "kth" field of the "jth" record of the "ith" table as, e.g.,

db.table(I).record(J).field(K).property

Heck, an Access table is just a sparsely populated, 2D tensor, what is the big deal?

[xCav8r]

Access VBA Reference: http://msdn.microsoft.com/library/de...HV05188068.asp

Alternatively, here's a snapshot in CHM: http://www.microsoft.com/downloads/d...DisplayLang=en

Here's the 3.6 DAO library: http://msdn.microsoft.com/library/de...ectLibrary.asp

As for navigating to a particular record, might there be another simpler way to do what you want to do? (Maybe give us some more details so we can suggest alternatives.)

Heck, an Access table is just a sparsely populated, 2D tensor, what is the big deal?

If you open the table, then you can navigate like you want: [VBA]docmd.gotorecord[/VBA]