.RecordCount funtion not giving expected results

[sherlocked]

Hello Experts,

I have the below function passing a value to a variable that is used to populate an unbound text field on a report. For some reason, the result I'm getting is "1" instead of the expected number. It seems the function is only counting the first record in the recordset.

Any idea on why, and what can be done?

I have also tried this as IDJan = rst!CountofID but get an error when there are no records in the recordset.

Thank you in advance! My code:

Public Function GetIdentity()

Dim rst As Recordset
Dim IDJan as Integer
     
Set rst = CurrentDb.OpenRecordset("SELECT Count(tblRecords.ID) AS CountOfID " _
      & "FROM tblRecords " _
      & "GROUP BY tblRecords.ExecYear, tblRecords.ExecMonth, tblRecords.FraudType " _
      & "HAVING tblRecords.ExecYear='" & Form_frmMain.cmboYear & "' AND tblRecords.ExecMonth='1' AND tblRecords.FraudType='Identity'")

IDJan = rst.RecordCount

rst.Close

Set rst = Nothing

End Function

 

[sneuberg]

You need to go to the last record to populate the RecordCount. So add something like

If Not rst.EOF then
    rst.MoveLast
    rst.MoveFirst
End If
IDJan = rst.RecordCount

 

[sneuberg]

Also I don't see where you are assigning a return value to GetIdentity. I'd think you be getting 0 rather than 1. If you want CountOfID to be returned then you need to add something like:

GetIdentity = rst!CountOfID

 

[sherlocked]

Thank you kindly, but I am still getting a "1" result from this

 

[sherlocked]

GetIdentity is called in the On_Load event of my report, it populates the text boxes with the variable values. It's working fine with recordsets that have no results, but with this one, which should have 3, it's only displaying a "1"

When I try rst!CountofID, I get a "no current record" error when the recordset has no results.

Hope this helps clarify

 

[sneuberg]

Ok then I suggest temporarily adding

Debug.Print "SELECT Count(tblRecords.ID) AS CountOfID " _
      & "FROM tblRecords " _
      & "GROUP BY tblRecords.ExecYear, tblRecords.ExecMonth, tblRecords.FraudType " _
      & "HAVING tblRecords.ExecYear='" & Form_frmMain.cmboYear & "' AND tblRecords.ExecMonth='1' AND tblRecords.FraudType='Identity'"

And run this code as it normally is run. This will output the query in the Immediate window with the value Form_frmMain.cmboYear filled in. Copy and paste the contents of Immediate window into the SQL view of the query designer and run the query. That will probably give you the same result (one record) but maybe you can figure out why.

 

[sherlocked]

Thank you, but I have already tried this. When I put the SELECT statement in the SQL query builder, I get 3 as I expect.

Any other thoughts?

 

[sneuberg]

Can you upload the database?

 

[sherlocked]

I'm afraid not. It contains sensitive information that cannot be publicly shared.

 

[sherlocked]

Update - I tried this, but am still getting "1"

Public Function GetIdentity()

Dim rst As Recordset
Dim IDJan as Integer
     
Set rst = CurrentDb.OpenRecordset("SELECT Count(tblRecords.ID) AS CountOfID " _
      & "FROM tblRecords " _
      & "GROUP BY tblRecords.ExecYear, tblRecords.ExecMonth, tblRecords.FraudType " _
      & "HAVING tblRecords.ExecYear='" & Form_frmMain.cmboYear & "' AND tblRecords.ExecMonth='1' AND tblRecords.FraudType='Identity'")

If Not rst.EOF Then

rst.MoveLast

IDJan = rst.RecordCount

rst.Close

Set rst = Nothing

Else

IDJan = "0"

rst.Close

Set rst = Nothing

End If

End Function

 

[sneuberg]

Where are you seeing "1"? If you put

Debug.Print IDJan

after

IDJan = rst.RecordCount

what do you see in the Immediate Window.

 

[MarkK]

This reference is not reliable...

Form_frmMain.cmboYear

That code may or may not return the object you think it does. In the case the form is not open, that reference will open it, but then you will have no control over which record is displayed.

That reference should be to an object in the Forms collection, like...

Forms!frmMain.cmboYear

...and that reference should fail if the form is not already open.

 

[sneuberg]

This reference is not reliable...

Form_frmMain.cmboYear

Thanks for that input. That's interesting. I never used that format myself but I always assumed Form_FormName was the same as Forms!FormName. What does Form_FormName refer to and when would this reference be used?

 

[sherlocked]

Tried both suggestions.

Debug.Print on IDJan gives me "1"

Changing to Forms!frmMain.cmboYear does not correct the problem.

 

[sneuberg]

Could you possibly strip down your database so that it could be uploaded. Or maybe create a new database and import the parts that are relevant to this problem and then sanitize and upload that?

 

[MarkK]

Taking a second look at your SQL, I don't see how it can return more than one record. You are grouping by month, year, and type. You are specifying a single month, year and type. That will yield you one row.

Maybe you mean to read the value in the first field of the recordset--which executes a count of the grouping--rather than count the rows??? Consider...

dim sql as string
sql = _
   "SELECT Count(ID) " & _
   "FROM tblRecords " & _
   "GROUP BY ExecYear, ExecMonth, FraudType " & _
   "HAVING ExecYear = '" & Forms!frmMain.cmboYear & "' " & _
      "AND ExecMonth = '1' " & _
      "AND FraudType='Identity'"

With Currentdb.OpenRecordset(sql)
[COLOR="Green"]   'show the value in the first field of the first row...[/COLOR]
   if not .eof then msgbox .Fields(0).value
   .close
end with

 

[MarkK]

Steve, if there isn't an open instance, the Form_Form1 notation creates a new non-default instance of the class, so if you make sure Form1 is not open, and then do this in the immediate pane...

Form_Form1.visible = true

...it opens Form1 and makes it visible. But if you close that form and do this in the immediate pane...

? Form_Form1.Name
 Form1

...this opens a hidden instance. It opens in order to find it's own Name property, and returns the data as you would expect, but now any future calls to Form_Form1 will return this hidden instance.

But now, if we do this in the immediate pane...

DoCmd.OpenForm "Form1"

...we open a second instance of Form1. Both are present in the Forms collection, but now Forms!Form1 and Form_Form1 refer to two different instances of the same class. So the call to "Form_FormName.someValue" is risky on the grounds that it may create a hidden object, and return unexpected values.

Does that make sense? Is that explanation followable?

Cheers man,

 

[sherlocked]

MarkK, this seems to have done the trick. Thank you a million times, you are a genius!

 

[MarkK]

You bet sherlock, glad you got it figured out!

 

[The_Doc_Man]

When in doubt, SIMPLIFY.

SELECT Count(tblRecords.ID) AS CountOfID
FROM tblRecords 
GROUP BY tblRecords.ExecYear, tblRecords.ExecMonth, tblRecords.FraudType
HAVING tblRecords.ExecYear='" & Form_frmMain.cmboYear & "' AND tblRecords.ExecMonth='1' AND tblRecords.FraudType='Identity'

This terribly complicates your life. What would you get from:

X = DCount( "*", "tblRecords", 
"[tblRecords.ExecYear]='" & Form_frmMain.cmboYear & "' AND tblRecords.ExecMonth='1' AND tblRecords.FraudType='Identity'")

That group-by stuff obfuscates the question. Get rid of it if you don't need it.