Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    55

    Unanswered: Counting Records

    I'm trying to count records in a table. Then use the number of records in the table to determine how many iterations it will take to loop through the table and look at each record. Here is what I am trying to do:

    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("PL-03", dbOpenDynaset)
    'strBuffer = "SELECT COUNT(*) FROM [PL-03];"
    'DoCmd.RunSQL strBuffer

    1 For i = 0 To (rst.RecordCount)
    2 With rst
    3 .FindFirst "[FIND NO] = '" + Str(i) + "'"
    4 If .NoMatch = False Then
    5 strBuffer = "UPDATE [PL-03] SET NHA = " + strPLNum(0) + "
    6 WHERE [FIND NO] = " + Str(i) + ";"
    7 DoCmd.RunSQL strBuffer
    8 End If
    9 End With
    10Next

    The problem lies in the rst.RecordCount(line 1). It returns 1 when there are many more records in the table, say 80. Then when it reaches line 4 it returns the correct number of records. However, this does not help me because the for loop is already looking for the stopping condition of 1.

    Can anyone help?

    Thanks,

    Animaul

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Counting Records

    Originally posted by Animaul
    I'm trying to count records in a table. Then use the number of records in the table to determine how many iterations it will take to loop through the table and look at each record. Here is what I am trying to do:

    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("PL-03", dbOpenDynaset)
    'strBuffer = "SELECT COUNT(*) FROM [PL-03];"
    'DoCmd.RunSQL strBuffer

    1 For i = 0 To (rst.RecordCount)
    2 With rst
    3 .FindFirst "[FIND NO] = '" + Str(i) + "'"
    4 If .NoMatch = False Then
    5 strBuffer = "UPDATE [PL-03] SET NHA = " + strPLNum(0) + "
    6 WHERE [FIND NO] = " + Str(i) + ";"
    7 DoCmd.RunSQL strBuffer
    8 End If
    9 End With
    10Next

    The problem lies in the rst.RecordCount(line 1). It returns 1 when there are many more records in the table, say 80. Then when it reaches line 4 it returns the correct number of records. However, this does not help me because the for loop is already looking for the stopping condition of 1.

    Can anyone help?

    Thanks,

    Animaul
    Before you use the Rs.Count, you have to use rs.MoveLast then rs.MoveFirst

  3. #3
    Join Date
    Nov 2003
    Posts
    55
    Thanks. That did it.

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by Animaul
    Thanks. That did it.
    Note: Before you do that, you should check whether the Rs.RecCount=0 or not, if it equals to zero then don't use the Rs.MoveLast [You will receive an error message]

    Hope it's clear.

  5. #5
    Join Date
    Nov 2003
    Posts
    55
    thanks, agian. I will check for Zero.

    Animaul

Posting Permissions

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