VBA - variable set to .RecordCount not working

greenprg
greenprg used Ask the Experts™
on
My code finds records that match a target number.  It loops through the matching records and copies a new record for each matching record to the same table but with a different request number.  My problem is RecCount does not get properly set to the correct number of matching records found.  It looks VERY straight forward.

RecCount = .RecordCount + 1

When I debug, .Record Count is the correct number, 6, but RecCount gets set to  2 instead of 7.  Please help.

Thanks!
Helen

Private Sub CopyDatasets()
Dim rstSource As dao.Recordset
Dim rstInsert As dao.Recordset
Dim strSQL As String
Dim lngLoop As Integer
Dim RecCount As Integer
    
   strSQL = "SELECT * FROM DatasetTable WHERE RequestNumber = " & Chr$(34) & Me!ATLRequestNum _
      & Chr$(34) & " AND Active = True"
   Set rstInsert = CurrentDb.OpenRecordset(strSQL)
   Set rstSource = rstInsert.Clone
   With rstSource
      RecCount = .RecordCount + 1
      lngLoop = 1
      Do While lngLoop < RecCount
         With rstInsert
            .AddNew
            !RequestNumber = Me!newRequestNum
            !DatasetNumber = rstSource("DatasetNumber")
            !Revision = rstSource("Revision")
            !DatasetNum = rstSource("DatasetNum")
            !active = True
            .Update
          End With
          .MoveNext
          lngLoop = lngLoop + 1
      Loop
      rstInsert.Close
      .Close
   End With
  
   Set rstInsert = Nothing
   Set rstSource = Nothing
 
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Hello greenprg,

Never, ever, ever rely on RecordCount.  Just don't do it.  Instead, look for the recordset's EOF.

However, with that said, why loop through a recordset at all here?  An append query will do the job...

strSQL = "INSERT INTO DatasetTable (RequestNumber, DatasetNumber, Revision, DatasetNum, active) " & _
    "SELECT " & Me!newRequestNum & " AS Expr1, DatasetNumber, Revision, DatasetNum, active " & _
    "FROM DatasetTable " & _
    "WHERE RequestNum = '" & Me!ATLRequestNum & "' AND Active = True"

Regards,

Patrick

Author

Commented:
I tried looping until the recordset EOF but it ends up in an infinite loop...possibly because I'm adding more records to the table?

An append query sounds good too but I'm changing one field to a new value.  Other than the one field, all other fields are the same.

Helen

Author

Commented:
OK, I think I understand the code you sent above.  I tried it and now I'm getting the error, "Run-time error '3061':  Too few parameters. Expected 2."  on the CurrentDb.Execute statement.  Am I using the code wrong?

Private Sub CopyDatasets()
Dim db As dao.Database
Dim strSQL As String
 
   Set db = CurrentDb
   
   strSQL = "INSERT INTO DatasetTable (RequestNumber, DatasetNumber, Revision, DatasetNum, active) " & _
      "SELECT " & Me!newRequestNum & " AS Expr1, DatasetNumber, Revision, DatasetNum, active " & _
      "FROM DatasetTable " & _
      "WHERE RequestNum = " & Chr$(34) & Me!ATLRequestNum & Chr$(34) & " AND Active = True"
 
   CurrentDb.Execute (strSQL)
End Sub

Thanks!
Helen
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Top Expert 2006

Commented:
is me.newRequestNum a number or text?

dim sNewRequestNum as string
or
dim lNewRequestNum as long

if len(me.newRequestNum) > 0 then   'Use this or a null check to make sure that there is a value

if it is a string:

sNewRequestNum = me.newRequestNum
strSQL = "INSERT INTO DatasetTable (RequestNumber, DatasetNumber, Revision, DatasetNum, active) " & _
      "SELECT '" & sNewRequestNum  & "' AS Expr1, DatasetNumber, Revision, DatasetNum, active " & _
      "FROM DatasetTable " & _
      "WHERE RequestNum = '" & sNewRequestNum  & "' AND Active = True"


if it is a number:

lNewRequestNum = me.newRequestNum
strSQL = "INSERT INTO DatasetTable (RequestNumber, DatasetNumber, Revision, DatasetNum, active) " & _
      "SELECT " & lNewRequestNum  & " AS Expr1, DatasetNumber, Revision, DatasetNum, active " & _
      "FROM DatasetTable " & _
      "WHERE RequestNum = " & lNewRequestNum  & " AND Active = True"

currentdb.execute (strSQL)

end if

J
 
Commented:
NewRequestNum is a string and it's a field on the ATLUpdate form which gets set prior to this procedure getting called.  The only difference I see in your code is that you defined and added a variable to set to NewRequestNum.  It doesn't seem like much of a difference but I will check it out when I can.  I went back to my original while loop and used DCount to get the record count and it's working.  It would be nice to swap out my 30 lines of code for your 8 or 9 but I've got a lot of other coding to do first.  Thanks for the input.

Helen

Commented:
When you debug - try doing it on line 13.

put a stop on it and in the immediate window run

Print rstSource.RecordCount

Is it returning -1?  If the recordset is not static in onlr returns a propper row-count when you have looped to EOF.

Why not:

  While not rstSource.Eof
         With rstInsert
            .AddNew
            !RequestNumber = Me!newRequestNum
            !DatasetNumber = rstSource("DatasetNumber")
            !Revision = rstSource("Revision")
            !DatasetNum = rstSource("DatasetNum")
            !active = True
            .Update
          End With
          rstSource.MoveNext
          lngLoop = lngLoop + 1
      Loop

Commented:
woops, that "Loop" should be WEND

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial