VBA Query Record Count Error

[dac214]

Hi,



I'm fairly new to programming in Access with VBA. I have a large bit of code I'm working with to create an overall document rating for several documents that are used to rate certain standards for Child Care facilities. Most of it seems to be working except where I'm attempting to count the number of records returned by a query and use that count to run through a For..Next loop. Here is the code:

strSQLSelCenter = "SELECT MaxRatingPerYear.state_name, (MaxRatingPerYear.center_rating) AS RetCenter_family, (MaxRatingPerYear.tracking_date)AS CenterOriginDate " & _
                    "FROM MaxRatingPerYear " & _
                    "WHERE (((MaxRatingPerYear.state_name)='" & thisState_Name & "') AND ((MaxRatingPerYear.comp_id)='" & thisComp_ID & "') AND ((MaxRatingPerYear.tracking_date)<=" & iYear & ") " & _
                    "AND ((MaxRatingPerYear.center_rating)<>0));"

Set rsstrSQLSelCenter = db.OpenRecordset(strSQLSelCenter)
iDocNum = rsstrSQLSelCenter.Fields.Count
If iDocNum > 0 Then
    For CtrCount = 0 To iDocNum
    Set CurrentUberRat = rsstrSQLSelCenter.Fields("RetCenter_family")
    Select Case CurrentUberRat
      Case 1
        If thisRetCenter_family > 2 Then
          thisRetCenter_family = thisRetCenter_family
        Else
          thisRetCenter_family = CurrentUberRat
        End If
      Case 2
        If thisRetCenter_family > 2 Then
          thisRetCenter_family = thisRetCenter_family
        ElseIf thisRetCenter_family = 1 Then
          thisRetCenter_family = thisRetCenter_family
        Else
          thisRetCenter_family = CurrentUberRat
        End If
      Case 3
        thisRetCenter_family = CurrentUberRat
      Case 4
        thisRetCenter_family = CurrentUberRat
        rsstrSQLSelCenter.MoveLast
      Case 0
        thisRetCenter_family = thisRetCenter_family
    End Select
  Next CtrCount

This code actually works well for about 295 record creations then it gets a "Run-time error '3021': No current record."
I have narrowed down the problem to being the rs.fields.count statement returning a larger count number than the query actually returns. For example, a query that returns 1 record may show a record count of 3. I have looked into using DCount instead but cannot see a way to make it work when the query uses variable values from this VBA module.

This is a project I've inherited and have reached the end of my knowledge to fix the problems that existed in the original code. If I can get this last piece figured out I think it will finally be functional. Anny help would be appreciated in getting this count issue resolved.
Thanks!

[pbaldy]

Well for starters, this:

iDocNum = rsstrSQLSelCenter.Fields.Count

returns the number of fields in the recordset, not the number of records. You want the RecordCount.

[orange]

You might try
rsstrSQLSelCenter.MoveLast before you do a
iDocNum = rsstrSQLSelCenter.Fields.Count

What is CurrentUberRat I don't see it Dimmed in your code?

[dac214]

@pbaldy

recordcount was in the original code and it returned 63 records instead of 3, although it did not error out. So I tried again and took Orange's advice (thank you BTW) and added the MoveLast command before the RecordCount command and it returns the correct number of records now...however I still get the error at record 296.

Looking at the query and the table it looks at I see now that this particular record returns no records at this point because the value is zero (0) for all records, which is a possibility I hadn't forseen. I assume I have to check for this by doing something like:
If Not (rsstrEtc.BOF and rsstrEtc.EOF) Then
But where do I place that in the code? The error now occurs at the MoveLast command.

Set rsstrSQLSelLarge = db.OpenRecordset(strSQLSelLarge)
rsstrSQLSelLarge.MoveLast
iDocNum = rsstrSQLSelLarge.RecordCount
If iDocNum > 0 Then
  For LrgCount = 0 To iDocNum

So I guess I'll try it there. Let me know if you have any other thoughts. Appreciate the help.

[dac214]

BTW Orange,
currentUberRat is an integer Dimed at teh start of the code - in case it was killing you.

[pbaldy]

It would before the MoveLast, which will error on an empty recordset (personally I only test for EOF). I don't really understand what the code is doing, since it doesn't loop the recordset.

[dac214]

The count is working now however when this runs it returns a different query result than if I take the query and place it in Access and not in VBA. It almost seems as though the records are stepping through like they should.
I have attached a text file of the entire module for easier viewing. If I can just get this last issue resolved life will be great.

thanks for everyone's help.

[pbaldy]

A different query result meaning different records, or what? Again, I don't understand what you're trying to do, particularly with the For/Next loops. You loop through the number of records in the recordset, but the code within always uses the first record, so what's the point? Unless I'm having Friday afternoon brain cramps.

[dac214]

Paul, I think you just showed be what I've been overlooking. As I stated at the start of this post this is an inherited project. the database itself is poorly constructed and does not seem to go past 1st normalization. Where a perfectly natural primary key exists in one table the creator went with a pk generation instead, just as a few examples. This VBA code already existed but wasn not functional. Under the tight time line I'm under to get it working and considering I have not used Access in 4 years it seemed easier to use the existing code. hindsight now and all that aside...

I see what you are saying. I need it to step through for each state and then for each component for that state and run the query as it does so. The default code is stepping through the records of the generated table instead and running the query.

As I thought, I just needed a fresh pair of eyes to show me what was obvious and I was overlooking. This always seems to happen when I get too immersed in code.

I can't thank you enough.

[pbaldy]

No problem. To step through records, I typically use this type of loop instead of a For/Next loop:

Do While Not rs.EOF
...
rs.MoveNext
Loop