Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Posts
    19

    Unanswered: Number of rows in a query

    Can someone please help me with the basic VB code required to retrieve the number of rows in a query (its a union query, if that makes any difference).

    I know I'll prob need to make use of RecordSet, but with all the experienced Access people using this forum, I'm sure someone knows off the top of their head what i need.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2004
    Location
    Kampala, Uganda
    Posts
    32
    SELECT COUNT(*) FROM.... returns the number of rows returned in a query.

    If you've got the rows in a recordset, and its a dynamic recordset, use the MoveLast command to move to the end then look at the Bookmark property, that will give you the number of rows.

    You cant move forwards and backwards in ForwardOnly recordsets, so cant use the above method.

  3. #3
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100
    Apart from the village idiot solution, you can use Recordset.Recordcount as well.

  4. #4
    Join Date
    Apr 2004
    Posts
    19
    I'm afraid this is a new area for me, so i'd really appreciate if you could give me some example code of getting a recordset for a query.

    I don't want to build the count into the query. Instead I want using VB from a form to look into a query and find the number number of rows present (more importantly if there are any present at all).

    I presume counting the number of rows from VB instead of using a count function directly in a query is simple, but what do I know.

    Any advice, please.

  5. #5
    Join Date
    Apr 2004
    Posts
    19
    Thanks!!!

    Recordset.Recordcount is much better solution. Thought it would be simple.

    What's a village without an idiot!!!

    Thanks pipeio

  6. #6
    Join Date
    Apr 2004
    Posts
    19
    Um, i've got another problem following on from this.

    For some reason I can't seem to make use of the answer to the number of records in a query as pasrt of a conditional statement.

    Here's my statement (crescendo is the database name):

    If crescendo.[Form_Appointment Clashes].Recordset.RecordCount >= 1 Then

    It just seems to always evaluate the statement as true, even using >= 200.

    Any ideas???

  7. #7
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100
    Quote Originally Posted by markb_1984
    Um, i've got another problem following on from this.

    For some reason I can't seem to make use of the answer to the number of records in a query as pasrt of a conditional statement.

    Here's my statement (crescendo is the database name):

    If crescendo.[Form_Appointment Clashes].Recordset.RecordCount >= 1 Then

    It just seems to always evaluate the statement as true, even using >= 200.

    Any ideas???
    I don't think you can use Recordset as a property of the form. A recordset is a group of records due to a SELECT query.

  8. #8
    Join Date
    Apr 2004
    Posts
    19
    Um......

    Not sure you're right on that one cos when I use a message box:

    MsgBox (crescendo.[Form_Appointment Clashes].Recordset.RecordCount)

    If the underlying query of the form has records, it pops up with the number of records, and obviously pops up 0 if there ain't any.

    That's what I can't get my head around. If it's working within a message box, telling me the number of records a form has in its query/table, then how come the conditional fails?????

    Hoping someone has a solution, and thanks for the help so far.

  9. #9
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100
    Hey Mark,

    Are you sure that Form.Recordset.Recordcount has the same number as Recordset.Recordcount. Because the Recordset is a variable itself, not a property of the form, it would be really weird. Ok, why don't you use in your msgbox the Recordset variable. I mean, if your recordset variable is called RS, then MsgBox RS.Recordcount

    It is strange if it is the same result ...

  10. #10
    Join Date
    Apr 2004
    Posts
    19
    It's not as straightforward as that, RecordSet.RecordCount will tell me the number of records in the underlying query/table of the current form.

    I want to write VB in one form to open up another and count the records in that form.

    That is why i use FormName.RecordSet.RecordCount

    It does work like I said cos I keep comparing the number in the pop up message box with the number visible through opening the query.

    So why is it that using the condition:

    If crescendo.[Form_Appointment Clashes].Recordset.RecordCount >= 1 Then

    It is unable to compare??? Don't suppose i've got to use any special syntax, have I??? I mean: ....RecordCount >= someinteger should work shouldn't it???

  11. #11
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100
    yes, it should work, I don't know why.

    Maybe, if you want to use one value from one for to another, you can define the Recordset variable as Public.

    ...

  12. #12
    Join Date
    Apr 2004
    Posts
    19
    Ok, i'm sure you can help me with the alternative. How do i perform a recordset.recordcount on a query in VB of a form???

    I know databaseName.queryName.recordset.recordnumber doesn't work. Nor does queryName.recordset.recordnumber

    What's the correct method please???

Posting Permissions

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