asked on

VBA - variable set to .RecordCount not working

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

Patrick Matthews

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

greenprg

ASKER

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

greenprg

ASKER

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

jefftwilley

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

ASKER CERTIFIED SOLUTION

greenprg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dentab

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

dentab

woops, that "Loop" should be WEND