Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Field question in DAO 1

Status
Not open for further replies.

VickyC

Technical User
Sep 25, 2010
206
CA
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.

Code:
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?

Code:
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
 
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.
 
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
 
Just a little of pseudo-code (not tested):

Code:
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top