Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unanswered: get table record count for VBA control loop

    Can anybody think of a good way?

    here's how I did it before:

    Dim dbs As Database
    Dim rst As Recordset

    Set dbs = CurrentDB
    Set rst = dbs.OpenRecordset("some table", dbOpenDynaset)

    If rst.RecordCount \\then I put my condition

    \\more code, then I would end it

    End If

    The problem is the "Dim dbs As Database" That part doesn't work in Access 2000, and I can't think of another way to do this. Cany anybody help?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Does your mdb have the Microsoft DAO Object Library set as a reference?

  3. #3
    Join Date
    Nov 2003
    Posts
    7
    I have no idea. What's the difference between ADO and DOA?

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Well.... for one thing, DAO has the database object.

    To see if you have the library selected:
    Open a code window (or press Ctrl-G)
    Choose Tools, References

    If it's not checked, check the reference for Microsoft DAO Object Library.

  5. #5
    Join Date
    Nov 2003
    Posts
    7
    yeah, I guess that was the problem.

    Can you think of a better way to see if a table is empty, though?

    thanks for your help, btw

  6. #6
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    You're welcome izzle.

    To find out if a table is empty you can use DLookup. If DLookup returns a null value when the field you specify is required, then there are no records in the table. (an autonumber field works well here, if your table has one).

    If IsNull(DLookup("AFieldThatCantBeNull","SomeTable") ) Then
    'table is empty
    Endif

    Initially you asked for the record count. If that's what you really want, you can easily get the record count using DCount rather than DLookup.

    Dim lngSomeTableRecordCount As Long
    lngSomeTableRecordCount = DCount("AFieldThatCantBeNull","SomeTable")

    Although if you need to open the recordset anyway, to process the records you find, then I'd stick with the Database/Recordset approach, like you had in the first place.
    Last edited by JTRockville; 11-13-03 at 02:41.

  7. #7
    Join Date
    Nov 2003
    Posts
    7
    oh yeah, that makes loads of sense. The way I have it now, I borrowed that from a different database that does, in fact, need to process the records.

    All I need to do is make sure it's not empty, so I'll try using the Dlookup.

    thanks for your help, I really appreciate it (the guy at work that always helped me before was laid off...)

  8. #8
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    No problem izzle. Good luck, and stop by again soon.

Posting Permissions

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