473,322 Members | 1,911 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Testing Query with NO Result in it

Hi:

Most of the time when I do a query and it has no matches, there is a single blank line in
the result that I can test. I have one query where no result produces a response with NO
lines at all in it. How do I test this kind of query result to determine that there is no
information in it. I am using macros, and on other blank queries I have been checking a
field that will always been filled in and seeing it if isnull.

This query supports a form, and i want to close the form without showing it if there is no
data in it.

Suggestions?

Thanks in advance

John Baker
Nov 12 '05 #1
4 2302
John Baker wrote:
Hi:

Most of the time when I do a query and it has no matches, there is a single blank line in
the result that I can test. I have one query where no result produces a response with NO
lines at all in it. How do I test this kind of query result to determine that there is no
information in it. I am using macros, and on other blank queries I have been checking a
field that will always been filled in and seeing it if isnull.

This query supports a form, and i want to close the form without showing it if there is no
data in it.

Suggestions?

Thanks in advance

John Baker


How do you open the query? WIth a recordset?
Set rst = Currentdb.Openrecordset("querynam"...)
If rst.Recordcount = 0 then...

or is the query the recorsource of the form.
If Me.Recordsetclone.Recordcount = 0 then Cancel = True

Nov 12 '05 #2
On Fri, 16 Jan 2004 21:08:28 GMT in comp.databases.ms-access, John
Baker <Ba******@Verizon.net> wrote:
Hi:

Most of the time when I do a query and it has no matches, there is a single blank line in
the result that I can test. I have one query where no result produces a response with NO
lines at all in it. How do I test this kind of query result to determine that there is no
information in it. I am using macros, and on other blank queries I have been checking a
field that will always been filled in and seeing it if isnull.

This query supports a form, and i want to close the form without showing it if there is no
data in it.

Suggestions?


This particular query is not updatable, there are many reasons why
this could be. non-updatable queries will not have a blank line in
them for entering new records. There's nothing you can do to change
that except to change the query so that it is updatable.

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #3
"John Baker" <Ba******@Verizon.net> wrote in message
news:8l********************************@4ax.com...
Hi:

Most of the time when I do a query and it has no matches, there is a single blank line in the result that I can test. I have one query where no result produces a response with NO lines at all in it. How do I test this kind of query result to determine that there is no information in it. I am using macros, and on other blank queries I have been checking a field that will always been filled in and seeing it if isnull.

This query supports a form, and i want to close the form without showing it if there is no data in it.

Suggestions?

Thanks in advance

John Baker

John,
Whether you see a 'blank line' or not might be a bit misleading. The
important point is that, blank line or not, the query returns no records.
The difference is whether you can add to the query or not. Imagine you had
an extremely simple table: tblContacts (ConID, ConName) then with a query
like:

SELECT ConID, ConName FROM tblContacts WHERE ConName="xyz"

you would be able to run this query and, if you had no-one called "xyz" in
your contacts table there would be a single blank row where you could add a
new person (with name="xyz" or anything else you chose). However, if your
query was not updateable, like:

SELECT DISTINCT ConID, ConName FROM tblContacts WHERE ConName="xyz"

Then you would see no rows at all.

In either case, you can inspect the form's RecordsetClone.RecordCount
property to decide if you want to open the form - like this:

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_Handler

Cancel = True

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No Data", vbExclamation, _
"Cannot open form"
Else
Cancel = False
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
This assumes that you have some knowledge SQL and viewing the statement for
your queries and that you know where to insert VBA code. Let me know if
this is an assumption too far.

Fletcher
Nov 12 '05 #4
Thanks again guys..You have resolved the problem!!

Regards

John Baker
Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Andy Levy | last post by:
Hi Im trying to create a function that tests if a 'Username' exists in a particular table. If the value is found then the code generates 'Username1' as the username, and check if that username...
1
by: LRW | last post by:
I'm trying to make an If/Else option in the case a database query comes back with nothing. But when I use the code included below, it always comes back saying there is no result. I'm guessing it's...
2
by: jaysonsch | last post by:
Hello! I am having some problems with a database query that I am trying to do. I am trying to develop a way to search a database for an entry and then edit the existing values. Upon submit, the...
11
by: Durai | last post by:
Hi All, I tested "concurrent testing" in MySQL. It works fine. But I couldn't do in PostgreSQL 7.3.4 on HPUX IPF. I got deadlock problem. I used the PHP script to update table( one script...
6
by: AAVF | last post by:
Hi We have a problem with a query. An Access database links via ODBC to a UNIX server. To speed things, we use the ODBC to load the relevant tables to the local PC that runs Access so that...
3
by: cover | last post by:
I have a table with 50 fields that receive input depending on whether that input came in from a 'shaker' form or a 'conveyor' form. Input from the 'conveyor' form might populate 25 fields while...
2
by: chuy08 | last post by:
Basically I am using PHP 5.1.2 with Apache 2.0.5 on a FreeBSD 5.4 box with Mysql 4.1.1 running. I am attempting to write information to a Mysql table called Jabber. I can connect successfully,...
33
by: genc_ymeri | last post by:
Hi over there, Propably this subject is discussed over and over several times. I did google it too but I was a little bit surprised what I read on internet when it comes 'when to use what'. Most...
6
by: JRough | last post by:
How do I test for no data in a string? I tried if !(isset($data)) and $data =="" and neither one returns the message when there are no records found. Or is it a better idea to do the test at the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.