Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002

    Unanswered: Using a field as a Table Name

    I have a master Campaign table that defines the entity "Campaign". Each "Campaign" entity has a "Contact" table as a child. Each time a record is created in the "Campaign" table, a coorsponding "Contact" table is created. Vice Versa, if you delete a "Campaign" record the entire child "Contact" table is deleted. There is a field in the "Campaign" table that defined the name of the child "Contact" table.

    How can I progressively move through the child "Contact" tables and count the records?

    Close as I can come in concept is.........

    Select Count(PrimaryID) as CountOfID FROM "Select [contacttable] FROM [campaign]"

    I think that this can only be done in code but I don't know where to begin, any help would be appreciated, thank you.

  2. #2
    Join Date
    Oct 2001
    Sub RecordCount()

    Dim dbsTmp As Database
    Dim rstTmp As Recordset

    Set dbsTmp = CurrentDb()

    ' Open the recordset and loop through it
    Set rstTmp = dbsTmp.OpenRecordset("Select [contacttable] FROM [campaign]", dbOpenSnapshot)

    With rstTmp
    Do Until .EOF
    Debug.Print ![contacttable] & " Count: " & DCount("*", ![contacttable])
    End With

    Set dbsTmp = Nothing

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts