UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

2 Pages V   1 2 >  
Reply to this topicStart new topic
> if query returns no records, msg box "no records"- how can I do    
 
   
nolte17
post Jul 10 2007, 05:56 PM
Post #1

New Member
Posts: 5



I have a parameter query doing a part search. If no parts match the search, I want to display a msg box or equivalent saying so. I've tried using an if statement in the code of the part search results page whose control source is the appropriate query,
If IsNull(Me![parts]) then
msgbox "no records found"
end if
Odon't understand why this wouldn't work. Any suggestions?
Go to the top of the page
 
+
datAdrenaline
post Jul 10 2007, 06:01 PM
Post #2

UtterAccess Editor
Posts: 16,782
From: Northern Virginia, USA



I beleive that more of your code, or more details about your situation, configuration (ie: your work flow) would be good to indicate ... dazed.gif ...
Looking forward to more info!
Go to the top of the page
 
+
Alan_G
post Jul 10 2007, 06:01 PM
Post #3

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,232
From: Devon UK



Hi
Welcome to UA sad.gif
Try
If DCount("*","NameOfYourQuery") = 0 Then
Msgbox "No records found
End If
Go to the top of the page
 
+
nolte17
post Jul 11 2007, 12:01 AM
Post #4

New Member
Posts: 5



Thanks for the quick responses! Unfortunately the DCount code didn't work for me, I got a msg saying Runtime error '2001' You canceled the previous operation.
Is for more details, I have a table with several fields, one being part description. When I run a query to search within the table for the part typed into the parameter query box, if there's no matching part description to what the user entered, I'd like a way of letting the user know that a matching part description does not exist. The query works perfectly when there's a matching part. I would just like a way tell the user that there isn't a match instead of getting a blank form.
More details? Or is that any better?
Thanks!
Go to the top of the page
 
+
Alan_G
post Jul 11 2007, 05:14 AM
Post #5

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,232
From: Devon UK



Where are you trying to run the code from (which event ?). Are you using a form to supply the criteria for the part..........?
Go to the top of the page
 
+
nolte17
post Jul 11 2007, 05:17 PM
Post #6

New Member
Posts: 5



The criteria is supplied through the msg box that pops up from the parameter query upon opening the form that is controlled by this same parameter query.
The reason for using the parameter query is that I wanted to have a wildcard "*" both before and after the part description... and the only way that I could figure out how to do that was to run it as a parameter.
-Ally
Go to the top of the page
 
+
nolte17
post Jul 17 2007, 08:43 PM
Post #7

New Member
Posts: 5



Any Suggestions?

Thanks!

Ally
Edited by: nolte17 on Tue Jul 17 21:43:34 EDT 2007.
Go to the top of the page
 
+
fish3s
post Jul 19 2007, 04:09 PM
Post #8

UtterAccess Member
Posts: 37



Hi Alan,
too am trying to get this to work with a form populated by records from a parameter query. Works fine when the query returns records, but when the parameter entered causes no records to be found, a blank form opens and is confusing to users. So far I've used an If statement identical to yours in just about every event that would make any sense to try with no luck. I've also tried checking the form records instead of the query, as in:
If DCount("*","NameOfForm") = 0 Then
Msgbox "No records found"
End If
or
If DCount("[NameOfControlComboBox]","NameOfForm") = 0 Then
Msgbox "No records found"
End If
It continues to display a blank form when a parameter is entered that returns no records from query. Mostly I've been trying these variations in the On Load event but have also tried them in several other events with same result. Not even an error msg., just the blank form.
Any suggestions would be greatly appreciated.
Go to the top of the page
 
+
Alan_G
post Jul 19 2007, 07:32 PM
Post #9

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,232
From: Devon UK



You'd typically use it in the On Open event procedure of the form, along the lines of
If DCount("*","NameOfYourQuery") = 0 Then
Msgbox "No Records"
Cancel = True
End If
If it's a bound form, you could use the RecordsetClone instead........
If Me.RecordsetClone.RecordCount = 0 Then
Msgbox "No Records"
Cancel = True
End If
PS - Welcome to UA sad.gif
Go to the top of the page
 
+
fish3s
post Jul 20 2007, 09:07 AM
Post #10

UtterAccess Member
Posts: 37



Thanks so much for the response. It is a bound form with the parameter query specified in the Record Source property, so I copied the RecordsetClone If statement you provided into the On Open event of the form property and still get the same result. I'm fairly new at this so I'm sure it's something obvious that I'm missing. One thing that strikes me as odd and may be a clue is that it seems no matter what I try the result is always the same. I would expect to get some other type of result or error at least once with all the different combinations of statements I've tried.
Any other ideas? And thanks again for the quick response. This forum is great!
Go to the top of the page
 
+
Alan_G
post Jul 20 2007, 10:11 AM
Post #11

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,232
From: Devon UK



First, check that your query isn't returning any records (ie, make sure the parameter you enter results in no records being returned) otherwise the code wont fire as the recordcount will be greater than 0
econd, whereabouts are you puting the code? It needs to be in the On Open event procedure of your form. Click on the three little elipses (...) at the right hand side of the On Open event which will open up the VBE window. (If you get a pop up box when you click on the 3 elipses choose Code Builder). That's where you put the code, which will look like this.....
CODE
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records"
Cancel = True
End If
End Sub
Go to the top of the page
 
+
fish3s
post Jul 20 2007, 11:53 AM
Post #12

UtterAccess Member
Posts: 37



I double checked that the query is not returning any records.
The code is being put in the On Open event procedure of the form in the VBE code builder window. Could the problem be because there are other codes for other control buttons in the code builder? There are buttons to close form, print a report, email report, etc. that were installed/coded by using the control wizard.
Go to the top of the page
 
+
Alan_G
post Jul 20 2007, 03:13 PM
Post #13

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,232
From: Devon UK



No, it shouldn't make any difference about the wizard created code, as long there's no other code running before you check the record count that would/could interfere with it.........
Is the record source of your form set to the query, in other words your form is bound to the query..........?
Go to the top of the page
 
+
fish3s
post Jul 20 2007, 03:18 PM
Post #14

UtterAccess Member
Posts: 37



Yes, the form is bound to the query. On the form property sheet the query is designated as the record source. And there are no other object with the same name as the query.
Go to the top of the page
 
+
Alan_G
post Jul 20 2007, 03:42 PM
Post #15

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,232
From: Devon UK



Put a message box before the code I gave you to see what the record count is and let me know..........
!--c1-->
CODE
Private Sub Form_Open(Cancel As Integer)
Msgbox Me.RecordsetClone.RecordCount
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records"
Cancel = True
End If
End Sub
Go to the top of the page
 
+
fish3s
post Jul 20 2007, 03:59 PM
Post #16

UtterAccess Member
Posts: 37



I'll have to wait till Monday to work on this some more. But thanks lots. Have a good weekend!
Go to the top of the page
 
+
Alan_G
post Jul 20 2007, 04:27 PM
Post #17

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,232
From: Devon UK



No problem sad.gif
Go to the top of the page
 
+
fish3s
post Jul 23 2007, 08:29 AM
Post #18

UtterAccess Member
Posts: 37



I put the message box line you suggested before the code and it does the same thing when I select a parameter that I know isn't in the table - displays a blank form, no message of record count. By blank form I mean the window with the caption of the form opens and there is nothing in it. No field names without data, just empty window. If I right click the mouse within in the window and select Data Sheet View, there are no records in the file.
changed the security setting to enable all macros for the time being just in case. I'm groping here, but that's one of my learning techniques:)
Since the form works when it's cued to run from a control button on main menu form, but doesn't respond to the code in the VBA module, I was thinking there might be a setting that's incorrect somewhere. However, that may not be the case, since there is other code in the module that run processes like close form, print, etc. Those control buttons work when the query produces records and the form displays the results.
I was thinking that since it works fine when the parameter finds results, but doesn't seem to run the code when the parameter produces no records, it might be the event, so I put the code in the On Error event but got the same results.
Go to the top of the page
 
+
Alan_G
post Jul 23 2007, 08:50 AM
Post #19

Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,232
From: Devon UK



Can you post the full code you're using in the On Open event procedure of your form.............
Go to the top of the page
 
+
fish3s
post Jul 23 2007, 08:52 AM
Post #20

UtterAccess Member
Posts: 37



Private Sub Form_Open(Cancel As Integer)
MsgBox Me.RecordsetClone.RecordCount
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No Records"
Cancel = True
End If
End Sub
Go to the top of the page
 
+

2 Pages V   1 2 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 27th November 2014 - 05:57 PM