Empty Query is Returning RecordCount = 1

How about this:


Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryExtrusionStockInventoryNoCostValueError-Monthly")
If MyRS.RecordCount = 0  Then
    blagh blagh blah
Else
   blagh blagh blah
End if

what is the sql statement of your query "qryExtrusionStockInventoryNoCostValueError-Monthly" ?


try removing the AutoNumber field form your query "qryExtrusionStockInventoryNoCostValueError-Monthly"

the column showing (new)

RecordCount does not work either, and when I place a Stop in the code to see what the RecordCount returns, it returns 1.

@ capricorn1:  I checked and can not remove those fields, they are used in the qry as fields that I have some criteria filters in the query.  The sql statement is below, it's a big one.

SQL STATEMENT_________________________
SELECT tblExtrInventory.ExtrInventoryID, tblExtrInventory.POID, Format([tblExtrInventory].[Date],"yyyy") AS YearSort, Format([tblExtrInventory].[Date],"mm") AS MonthSort, tblExtrInventory.Date, tblExtrPart.ExtrPartID, tblExtrPart.PartNumber, tblExtrInventory.ExtrColorID, tblExtrColor.ExtrColor, tblExtrInventory.ExtrTemperID, tblExtrTemper.ExtrTemper, tblExtrInventory.Length, tblProjectInfo.ProjectID, tblExtrInventory.UnitPrice, IIf(Nz([UnitPrice],0)=0,1,0) AS UnitPriceCheck
FROM tblProjectInfo RIGHT JOIN (tblPONumber RIGHT JOIN (tblExtrPart RIGHT JOIN (tblExtrColor INNER JOIN (tblExtrTemper INNER JOIN (tblExtrInventory LEFT JOIN tblPO ON tblExtrInventory.POID = tblPO.POID) ON tblExtrTemper.ExtrTemperID = tblExtrInventory.ExtrTemperID) ON tblExtrColor.ExtrColorID = tblExtrInventory.ExtrColorID) ON tblExtrPart.ExtrPartID = tblExtrInventory.ExtrPartID) ON tblPONumber.PONumberID = tblPO.PONumberID) ON tblProjectInfo.ProjectID = tblPONumber.ProjectID
WHERE (((tblExtrInventory.Date)<=([Forms]![frmPrintExtrusionUsageFromStock]![FrameMonth] & "/1/" & [Forms]![frmPrintExtrusionUsageFromStock]![FrameYear])) AND ((tblProjectInfo.ProjectID) Like 8) AND ((IIf(Nz([UnitPrice],0)=0,1,0)) Like 1)) OR (((Format([tblExtrInventory].[Date],"yyyy"))=[Forms]![frmPrintExtrusionUsageFromStock]![FrameYear]) AND ((tblProjectInfo.ProjectID) Like 8) AND ((IIf(Nz([UnitPrice],0)=0,1,0)) Like 1) AND ((Format([tblExtrInventory].[Date],"m"))=[Forms]![frmPrintExtrusionUsageFromStock]![FrameMonth]));

"it returns 1."
Then I would say it is returning one record.

What is the code above:

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryExtrusionStockInventoryNoCostValueError-Monthly")

?

IE ... the Dim statements, etc.

@ jerryb30:  The DCount seams to be working.  Why is this?  I'm curious why the BOF EOF objects are not working.

@ DatabaseMX:  Look at the attached screen shot, there are no records.  The code I included is just a portion of the actual code.

Some of the Dim statements:
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset

If rs.bof and rs.eof are both true.  Using SQL server?

or is your db set to mimic the sql where 1 = true and 0 = false?

@ jerryb30:  rs.bof and rs.eof both return FALSE.  I am just using MS Access 2010.  I'm not sure what you are asking in the last post with the mimicing.

shrimpfork,

can you upload a copy of your db?

Is that screen shot - the query run - while frmPrintExtrusionUsageFromStock is actually opened ?

In Access, 'true' normally equates to -1. If can be set to follow (normal) SQL conventions that 'True' = 1.
If rs.bof and rs.eof
sounds like
if rs.bof = rs.eof
which should be true, but should also mean recordCount is 0.
I think.
So, I am confused.

Any updateable query in datasheet view (your screenshot) will display a new record for data entry and show "1 of 1" records at the bottom of the query (you will also see this in forms bound to empty tables if memory serves me right).

To actually test the recordcount, use the .Recordcount property of your recordset... which will display zero unless you have actually started entering records:

Try this, and then take a look at and try MX's code in the first comment (which should work):

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryExtrusionStockInventoryNoCostValueError-Monthly")
msgbox "RecordCount = " & MyRS.Recordcount
If MyRS.BOF And MyRS.EOF Then
    blagh blagh blah
Else
   blagh blagh blah
End if

Select allOpen in new window

"use the .Recordcount property of your recordset... which will display zero unless you have actually started entering records:"

ahhh ... right ...

?

Just another note about the recordcount property in a DAO recordset...

When you open a recordset that is based on a table or query with no data, the recordcount property (RS.Recordcount) will be zero.

If the table has any data in it, your recordset will show a recordcount of 1 when it first opens.

To get the actual number of records in a populated recordset, you would have to move to the end of the recordset:

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryExtrusionStockInventoryNoCostValueError-Monthly")
If MyRS.RecordCount = 0  Then
    Msgbox "Empty recordset - no records"
Else
   MyRs.MoveLast
   MsgBox "RecordCount: " & MyRs.RecordCount & " records"
   MyRs.MoveFirst
   ' etc
End if

Select allOpen in new window

<unless you have actually started entering records:>

then the (new) should show a number and a pencil in the left instead of an astrisk.. if i remember it right

Pretty much like I'm saying.  If the Recordcount is not zero, it's because for whatever reason ... *when the code actually executes* ... the Recordset is returning 1 or more records.

mx

shrimpfork,

can you upload a copy of your db?

Sorry experts, I'm back...

@ Capricorn1:  I can not upload the DB.   It is a very large server based complex FE BE setup.  I would love the chance to show you this monster db.  I'm sure you have some great input.

@ DatabaseMX:  Yes this is a screenshot of the query with the form "frmPrintExtrusionUsageFromStock" open.

@ mbizup:  The mesage box displays that the record count = 1.  There are no records in the query when I open the query manualy.

** Something else is going on here ....

IF this is a DAO recordset ... then if the RecordCount states 1, then there is one record.

If MyRS.RecordCount = 0  Then
    Msgbox "The Count is Zero"  ' if you are not getting this, something else is up
Else
   blagh blagh blah
End if

Actually ... try this ... adding the dbOpenDynaSet option.

Set MyRS = MyDB.OpenRecordset("qryExtrusionStockInventoryNoCostValueError-Monthly", dbOpenDynaSet)

If MyRS.RecordCount = 0  Then
    Msgbox "The Count is Zero"  ' if you are not getting this, something else is up
Else
   blagh blagh blah
End if

@ Database MX:  I added the dbOpenDynaSet and it still returns RecordCount = 1.  But yet if I run this query it returns nothing just as it is pictured in the screen shot.  Also, if I fudge the data to return one (1) record, it still only returns RecordCount = 1.  Yea, something very odd is going on here.

shrimpfork..

you can do this in a copy of your db

copy the SQL statement of your query and paste it to Notepad.

delete your query "qryExtrusionStockInventoryNoCostValueError-Monthly"

now do a compact and repair
go to VBA window and

   'comment the lines that you posted above
   
'Set MyDB = CurrentDb
'Set MyRS = MyDB.OpenRecordset("qryExtrusionStockInventoryNoCostValueError-Monthly")
'If MyRS.BOF And MyRS.EOF Then
'    blagh blagh blah
'Else
'   blagh blagh blah
'End if

now do a Debug >Compile
correct any error raised


* create a new query
 go to SQL view and paste the SQL statement the you copied to notepad

save the query as "qryExtrusionStockInventoryNoCostValueError-Monthly"


uncomment the codes you commented above


run the codes


see if it make any difference.

@ capricorn1:  Ok I did what you asked in the last post.  Very interesting results...the RecordCount now returns 0, however when I fudge the data to return 1 record when I manually run the query, it is still returning 0.  We have flipped the problem now.  I'm have no idea what is going on.

first do a decompile
see this link
http://www.granite.ab.ca/access/decompile.htm

then

create a blank db,
import all the objects to the new db

compact and repair
debug>compile

test

Something interesting:
I think I may have stumbled on something here.  When I create a condition where 1 record is manually displayed that which matches the 2nd criteria in the query, it returns a ZERO record count.  However, when I create a condition where 1 record is manually displayed that matches the 1st criteria, then ONE record count is displayed.  I might try to export this data and try to recreate it in a second DB and post it.

@ capricorn1: I've had to do this not long ago and it really took a few years off my life.  I would like to try to avoid this, are you thinking this is a corruption issue?

yes...

Do what ?

@ capricorn1: Ok, I'm back with a new decompiled, imported, compacted & repaired and compiled Front End DataBase.  I have run the tests and it is still returning RecordCount = 1 for ZERO records.  (Ignore post ID: 38811846 & ID: 38811878.)  I feel a bit older.


I might try to export this portion of our DB into a single database to post and play with.

after you created the sample db, test it first..


i am guessing the corruption is coming from a record or records  in one of your tables.

"compiled Front End DataBase"

But aren't the records in the Back End?

Did you Compact & Repair that ?

mx

@ DatabaseMX: I have compacted & repaired all BE databases and the FE database.  I am going to post the portion of the database that will duplicate the problem.  I am working to import everything into a single DB.  Stand by...

Ok Experts, here is the portion of the DB that will duplicate the issue.  I have put a comment in the code where I am testing the various settings and code suggestions from you guys.  You can search for "EXPERTS" to find the location.

Let me give you the run down of the files and setup...

Form: "frmPrintExtrusionUsageFromStock" - This is where it is getting the date criteria for the report

Query: "qryExtrusionStockInventoryNoCostValueError-Monthly" - This is the query that the module is running to check for records.  You can also run manually as long as the Form is open.

Date selections on the form:
July / 2012 should return 0 records
August / 2012 should return 1 record
October / 2012 should return 1 record
Query-Record-Error-Example.mdb

Here is the issue (at least one(:

    Set MyDB = CurrentDb
   
'This next line generates an Error - and goes to your error trap, which has a Resume Next
' then, then next line executed is the Msgbox - and the Record Count of One is from the previous opening of RS above - so you are being fooled.

    Set MyRS = MyDB.OpenRecordset("qryExtrusionStockInventoryNoCostValueError-Monthly")

' more code


HandleError:
    'MsgBox Err.Description
    Resume Next
    Resume ExitHere
End Sub

And the error you are getting when attempting to Open myRS is:



So ... the Set MyRS from the code above is still in effect when the MsgBox displays RecordCount of 1


        Set MyRS = MyDB.OpenRecordset(sSQL, dbOpenForwardOnly)
        LookupLoop = LookupLoop + 1
        Loop  'loop required to look up deposited bundle amounts.

@ DatabaseMX:  Wow, good catch with the error reporting.  I need to close the record set before I open it a second time.  If the code detects an error, it will try to fix it.  That is why I have it looping twice.

(Mine does not give me an error on Set MyDB = CurrentDb.)

"I need to close the record set before I open it a second time.  I"

Not the problem.  See SQL above ... where I used Eval() in 3 places.

RS opens fine - with RecordCount of Zero as expected.

mx

Note ... I had to Remove the Outlook Reference ... fyi

mx

@ DatabaseMX:  I can not get the same results.  I have only done the Eval() in the query and still get 1 record.  I also get the "Too few parameters" error.  What did I miss?

Note: I add to remove the Outlook Reference in References ..,. fyi
Query-Record-Error-Example-MX01.zip

I see what I did, I had Eval() before <=.  I also had to also include quotes "" in the string.  It is working now with Eval().  Wow.  Now I need to divy the points.  Thanks Experts!

I'm trying to figure out how to award this.  There was a post by jerryb30 that suggested to use
"if dcount("*", "qryExtsionStockInventoryNoCostValueError-Monthly") < 1 then..." which seemed to work with the query as it was.  Could this suggestion been a fix for this problem?

(See ID: 38808023 by jerryb30)

On the face of it, it would have, but I do not know what else would have been hidden had not joe determined the other issues.

Ok, I'll give credit to the both of ya, but will weigh the points to the solution I went with.  Again, thanks to all the experts here.  I always get a good lesson.