I have the following code which uses a form that has (two date boxes and two comboboxes) to allow the user to select criteria for a query result. It all works fine except when no records are found with that criteria. The query still opens with no records.
Question is, how can I insert a msgbox saying "no matching records found" prior to the query/and eventually a report, opening? - Private Sub cmdSearch_Click()
-
If IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And IsNull([cboIssue]) Then
-
MsgBox "No Search Criteria Selected", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
-
Else
-
If Not IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And IsNull([cboIssue]) Then
-
MsgBox "You must select a date range or issue type to continue", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
-
Else:
-
If IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And IsNull([cboIssue]) Then
-
MsgBox "You must select a VIP or issue type to continue", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
-
Else
-
If Not IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And IsNull([cboIssue]) Then
-
DoCmd.OpenQuery "qryWalkthrough", acViewNormal, acViewNormal
-
Else
-
If Not IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And Not IsNull([cboIssue]) Then
-
DoCmd.OpenQuery "qryWalkthrough", acViewNormal
-
Else
-
If IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And Not IsNull([cboIssue]) Then
-
DoCmd.OpenQuery "qryWalkthrough", acViewNormal
-
Else
-
If Not IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And Not IsNull([cboIssue]) Then
-
MsgBox "Please remove one search criteria", vbDefaultButton1 = vbOKOnly, "To many Critera fields chosen"
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
End If
-
-
End Sub
-
6 1599 zmbd 5,501
Recognized Expert Moderator Expert
You can use a Dcount() function against the query. YOu can F1 help on it or Google.
However, with the structure of your code, we'll have to jump thru hoops to get this to work properly.
I'm on my way out the door with kids to school or I'd take a stab...
Normally, I'd be using a DAO recordset and drastically different code to do what you're doing here.... stacked IF.THEN are not really the easiest/cleanest to work with.
Thanks, I'm all for easier/better ways of doing things but guess I went with what I know.
ADezii 8,834
Recognized Expert Expert zmbd pretty much answered your question for you. You need to first check for the existence of any Return Records for each Query using the DCount() Function before you actually Open the Query: -
Private Sub cmdSearch_Click()
-
On Error GoTo Err_cmdSearch_Click
-
Dim strCriteria As String
-
-
If IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And IsNull([cboIssue]) Then
-
MsgBox "No Search Criteria Selected", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
-
Exit Sub
-
ElseIf Not IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And IsNull([cboIssue]) Then
-
MsgBox "You must select a date range or issue type to continue", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
-
Exit Sub
-
ElseIf IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And IsNull([cboIssue]) Then
-
MsgBox "You must select a VIP or issue type to continue", vbDefaultButton1 = vbOKOnly, "Insufficient Search Critera"
-
Exit Sub
-
ElseIf Not IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And IsNull([cboIssue]) Then
-
strCriteria = "Not IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And IsNull([cboIssue])"
-
If DCount("*", "<Your Table>", strCriteria) <> 0 Then
-
DoCmd.OpenQuery "qryWalkthrough", acViewNormal, acViewNormal
-
End If
-
ElseIf Not IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And Not IsNull([cboIssue]) Then
-
strCriteria = "Not IsNull([cboCustomer]) And IsNull([SDate]) And IsNull([EDate]) And Not IsNull([cboIssue])"
-
If DCount("*", "<Your Table>", strCriteria) <> 0 Then
-
DoCmd.OpenQuery "qryWalkthrough", acViewNormal
-
End If
-
ElseIf IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And Not IsNull([cboIssue]) Then
-
strCriteria = "IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And Not IsNull([cboIssue])"
-
If DCount("*", "<Your Table>", strCriteria) <> 0 Then
-
DoCmd.OpenQuery "qryWalkthrough", acViewNormal
-
End If
-
ElseIf Not IsNull([cboCustomer]) And Not IsNull([SDate]) And Not IsNull([EDate]) And Not IsNull([cboIssue]) Then
-
MsgBox "Please remove one search criteria", vbDefaultButton1 = vbOKOnly, "To many Critera fields chosen"
-
Exit Sub
-
End If
-
End Sub
-
P.S. - The Exit Subs are for illustration purposes only.
ADezii, It needed the query name instead of the table name, but other than that it works perfect.
Thanks again
NeoPa 32,568
Recognized Expert Moderator MVP
There's a NoData event for reports. I expect that's all you need to handle this (unless I misread your intention).
Bear in mind, when Cancel is set in this event the code that calls the Report to Open fails (So this should also be handled for smooth operation).
ADezii 8,834
Recognized Expert Expert
Question is, how can I insert a msgbox saying "no matching records found" prior to the query/and eventually a report, opening?
I totally missed the ' eventually a Report' part. NeoPa's Reply in Post# 6 appears to be the way to go in this light.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Bob Murdoch |
last post by:
I've got an intranet application that presents a list of files in sort of a
'central repository' web page. Each file is an href in the form <a
href=file://server/share/path/filename.ext>.
When the user clicks on an excel file, it is opening within the browser,
which unforntuntely confuses the heck out of them because the usual
Print/Print Preview menu options are not available. I would like the user
to be presented with the typical...
|
by: PM |
last post by:
Has anyone found a way to open a file exclusively where it
will fail if the file is already open, i have tried the
following
_FileStream = new FileStream(@"C:\Data.txt",
FileMode.Open, FileAccess.ReadWrite, FileShare.None);
which does not prevent me from opening the file even if
another user has the file open ie Notepad, it will prevent
me from saving the file in notepad until i close the file
in the app. I can then save changes made in...
|
by: UnixSlaxer |
last post by:
Hello,
Running a query for the first time on DB2 takes a fixed amount of time.
But when query is executed for the second time, the amount of time is
usually less since the query is (most probably) cached already.
I would like to clear out the DB2-UDB 8.2 query cache (I want the
previous execution time again).
Any advice would be appreciated.
|
by: Robert |
last post by:
I have a report that runs on a parameter query. I run the report, enter the
parameter, and it works as long as there is at least one record returned by
the query. But if it comes up empty, I get an error in the report. I think
there is some way to query the query to see if it's got any records and then
exit the report. How is this done?
Robert
|
by: ronald balk |
last post by:
Dear people,
after many efforts I'm putting my hope on you.
Last week I changed a lot of forms and reports in an Access 2000 database.
It all worked fine and I closed the database without any problems. But now I
can't open it!
When I open the file Access starts and I get an empty Access screen instead
of the database window. The filename isn't shown in the title bar. I have
tried repairing, but Access is finished in 0.5 seconds (database...
| |
by: John Baker |
last post by:
Hi:
I have a pop up form based on a query.
I am openings the form, and wish to close it immediately if the query has result.
My method is to put a macro in the "on Open" event , which has the following code
IsNull() Close (the form I just opened)
|
by: MLH |
last post by:
If I give someone a runtime app,
they can open the database
window by pressing the F-11
key. How to prevent???
|
by: Max2006 |
last post by:
Hi,
I have a search result GridView bound to an ObjectDataSource. Once I open
the asp.net page, the ObjectDataSource automatically refreshes the GridView
which is the default behavior.
I am trying to disable automatic query feature in ObjectDataSource, such
that it query the database only when the user click on Search button.
Is there anyway to prevent ObjectDataSource query the database when the page
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |