Solved

Empty Query is Returning RecordCount = 1

Posted on 2013-01-22
47
680 Views
Last Modified: 2013-01-24
I have a query that runs within Visual Basic.  The query is to detect for records.  When there is no records the query still shows 1 record and results in a RecordCount = 1.  Also the functions "MyRS.BOF" and "MyRS.EOF" both return false.  This should be simple as I've done this many times before.  I have included a screen shot of the query that is running and there are no records, but it states 1 record on the bottom.  What am I missing here?

Here is the protion of the code:

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
QueryScreenShot.png
0
Comment
Question by:shrimpfork
  • 18
  • 14
  • 8
  • +2
47 Comments
 
LVL 75
ID: 38807992
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38808006
what is the sql statement of your query "qryExtrusionStockInventoryNoCostValueError-Monthly" ?


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

the column showing (new)
0
 

Author Comment

by:shrimpfork
ID: 38808008
RecordCount does not work either, and when I place a Stop in the code to see what the RecordCount returns, it returns 1.
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 125 total points
ID: 38808023
if dcount("*", "qryExtsionStockInventoryNoCostValueError-Monthly") < 1 then


do something
endif
0
 

Author Comment

by:shrimpfork
ID: 38808054
@ 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]));
0
 
LVL 75
ID: 38808072
"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.
0
 

Author Comment

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

Author Comment

by:shrimpfork
ID: 38808086
@ 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
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38808087
If rs.bof and rs.eof are both true.  Using SQL server?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38808092
or is your db set to mimic the sql where 1 = true and 0 = false?
0
 

Author Comment

by:shrimpfork
ID: 38808109
@ 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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38808115
shrimpfork,

can you upload a copy of your db?
0
 
LVL 75
ID: 38808116
Is that screen shot - the query run - while frmPrintExtrusionUsageFromStock is actually opened ?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38808131
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38808137
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

Open in new window

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

ahhh ... right ...

?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38808161
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

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38808168
<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
0
 
LVL 75
ID: 38808171
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38808173
shrimpfork,

can you upload a copy of your db?
0
 

Author Comment

by:shrimpfork
ID: 38810919
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.
0
 
LVL 75
ID: 38810994
** 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
0
 

Author Comment

by:shrimpfork
ID: 38811738
@ 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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38811771
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.
0
 

Author Comment

by:shrimpfork
ID: 38811846
@ 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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38811874
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
0
 

Author Comment

by:shrimpfork
ID: 38811878
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.
0
 

Author Comment

by:shrimpfork
ID: 38811888
@ 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?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38811891
yes...
0
 
LVL 75
ID: 38811912
Do what ?
0
 

Author Comment

by:shrimpfork
ID: 38815078
@ 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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38815105
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.
0
 
LVL 75
ID: 38815386
"compiled Front End DataBase"

But aren't the records in the Back End?

Did you Compact & Repair that ?

mx
0
 

Author Comment

by:shrimpfork
ID: 38815449
@ 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...
0
 

Author Comment

by:shrimpfork
ID: 38815516
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
0
 
LVL 75
ID: 38815703
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
0
 
LVL 75
ID: 38815729
And the error you are getting when attempting to Open myRS is:


err
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.
0
 

Author Comment

by:shrimpfork
ID: 38815755
@ 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.)
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 375 total points
ID: 38815778
If you wrap the Form references with the Eval() function, the problem is eliminated:


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 (tblExtrPart RIGHT JOIN (tblPONumber RIGHT JOIN (tblExtrTemper INNER JOIN (tblExtrColor INNER JOIN (tblExtrInventory LEFT JOIN tblPO ON tblExtrInventory.POID = tblPO.POID) ON tblExtrColor.ExtrColorID = tblExtrInventory.ExtrColorID) ON tblExtrTemper.ExtrTemperID = tblExtrInventory.ExtrTemperID) ON tblPONumber.PONumberID = tblPO.PONumberID) ON tblExtrPart.ExtrPartID = tblExtrInventory.ExtrPartID) ON tblProjectInfo.ProjectID = tblPONumber.ProjectID
WHERE (((tblExtrInventory.Date)<=(Eval("[Forms]![frmPrintExtrusionUsageFromStock]![FrameMonth]") & "/1/" & Eval("[Forms]![frmPrintExtrusionUsageFromStock]![FrameYear]"))) AND ((tblProjectInfo.ProjectID) Like 8) AND ((IIf(Nz([UnitPrice],0)=0,1,0)) Like 1)) OR (((Format([tblExtrInventory].[Date],"yyyy"))=Eval("[Forms]![frmPrintExtrusionUsageFromStock]![FrameYear]")) AND ((tblProjectInfo.ProjectID) Like 8) AND ((IIf(Nz([UnitPrice],0)=0,1,0)) Like 1) AND ((Format([tblExtrInventory].[Date],"m"))=Eval("[Forms]![frmPrintExtrusionUsageFromStock]![FrameMonth]")));


eval()
0
 
LVL 75
ID: 38815790
"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
0
 
LVL 75
ID: 38815834
Note ... I had to Remove the Outlook Reference ... fyi

mx
0
 

Author Comment

by:shrimpfork
ID: 38815841
@ 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?
0
 
LVL 75
ID: 38815851
Note: I add to remove the Outlook Reference in References ..,. fyi
Query-Record-Error-Example-MX01.zip
0
 

Author Comment

by:shrimpfork
ID: 38815933
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!
0
 

Author Comment

by:shrimpfork
ID: 38815968
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)
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38815987
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.
0
 

Author Comment

by:shrimpfork
ID: 38815996
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.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now