Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number of Rows (Access2000/Access 2003)

    I have a Purge Module that purges rows from multiple tables. I like to show the client the number of rows on a table before the purge and after the purge. So is there any way to have VBA run a SQL like "Select Count(*) as StateKount From tblState;" so I can get the StateKount figure and place it on a form without getting the data sheet? In other words, I wish to run the query and only get the Number of Rows as a result. Also are there any books out there that go into this kind of stuff? Most of the books I have just cover the basics, none seem to go into using VBA and SQL together much.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Number of Rows (Access2000/Access 2003)

    DCount may get what you want. Put a text box on your form and put the following as the Control source:

    =DCount("*","tblState")

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number of Rows (Access2000/Access 2003)

    Ok that works (Dcount). But why can not get my "Select Count(*) as StateKount From tblState:" to work. As DCount does not help me post the number of rows in the table after the purge. I know I can take the begining total and subtract the number deleted and show the result as what is still on the table. But you should always count the rows, so if something goes wrong it will stick out like a sore thumb!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number of Rows (Access2000/Access 2003)

    You cannot use a SQL sttatement as the control source of a text box. You should let the form recalculate after running the macro:

    Me.Recalc

Posting Permissions

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