Field question in DAO 1

[VickyC]

hello all

Consider the code fragment below. I loop through a set of tables in an mdb file. If the table matches "tbl_*", then I call a function.
This works PERFECTLY.

Dim db As DAO.Database, tdf As DAO.TableDef

...

Set db = Workspaces(0).OpenDatabase(strExternalPathName)

For Each tdf In db.TableDefs

    If tdf.Name Like "tbl_*" Then
                                
        Call CountTableSymmetricRecords(strExternalPathName, tdf.Name)
                
    End If

Next

BUT, I want to add another condition before calling the function - I want to only call the function if the table field called sType = "K".
The code below that is in red doesn't work.

Could some guru out there tell me how to do this?

Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field

...

Set db = Workspaces(0).OpenDatabase(strExternalPathName)

For Each tdf In db.TableDefs

    If tdf.Name Like "tbl_*" Then
                                
[b][COLOR=#CC0000]        Set fld = tdf.Fields("sType")
        If fld.Value = "K" Then[/color][/b]
            
            Call CountTableSymmetricRecords(strExternalPathName, tdf.Name)
                    
        End If
                
    End If

Next

thanks in advance!
Vickt C

 

[Andrzejek]

So you can loop thru DAO.TableDef and pick the table(s) by name just fine.
But now you want to see if the Field sType exists in the table and check its value ("K") on which record in this table?

Don't you use a recordset to do that?


---- Andy

There is a great need for a sarcasm font.

 

[VickyC]

Thanks for responding, Andy. You're right - the point is... on which record in this table? That brings it into clearer focus.

For each table that matches "tbl_*", I want to count the records where Field sType = "K".
. How could I set that up?

Thanks, Vicky

 

[Andrzejek]

Just a little of pseudo-code (not tested):

Dim db As DAO.Database, tdf As DAO.TableDef
...
Set db = Workspaces(0).OpenDatabase(strExternalPathName)

For Each tdf In db.TableDefs
    If tdf.Name Like "tbl_*" Then
        rst1.Open "Select * From " & tdf.Name
        For X = 0 To rst.Fields.Count
            If rst1.Field(X).Name = "sType" Then
                rst.Open "Select Count(*) From " & tdf.Name & _
                    " Where sType = 'K'"
                    MsgBox rst.RecordCount & " records with K in sType in " & tdf.Name
                rst.Close
                Exit For
            End If
        Next X[green]
        'Call CountTableSymmetricRecords(strExternalPathName, tdf.Name)[/green]
        rst1.Close                
    End If
Next

---- Andy

There is a great need for a sarcasm font.