×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Field question in DAO

Field question in DAO

Field question in DAO

(OP)
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
                                
        Set fld = tdf.Fields("sType")
        If fld.Value = "K" Then
            
            Call CountTableSymmetricRecords(strExternalPathName, tdf.Name)
                    
        End If
                
    End If

Next 

thanks in advance!
Vickt C

RE: Field question in DAO

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.

RE: Field question in DAO

(OP)
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

RE: Field question in DAO

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
        'Call CountTableSymmetricRecords(strExternalPathName, tdf.Name)
        rst1.Close                
    End If
Next 


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close