All Forums >> [Scripting] >> WSH & Client Side VBScript >> Need message when no records found Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!
I need some help creating a msgbox that informs a user when no records were found after a search. I’m working with a Microsoft Access data access page that retrieves records based on two date fields; data is required in both fields for the search to take place. The following code was used and works without any problems:
<SCRIPT language=vbscript> Dim fInited fInited=FALSE
Sub OnFilterComboChange() Dim stWhere 'Don't do anything unless user selects a value from each list box. If (FromMondayDate.value <> "" and ToSundayDate.value <> "") Then stWhere = "CrewTime.CrewDate>=" & "#" & FromMondayDate.value & "#" & " " stWhere = stWhere & "AND CrewTime.CrewDate<=" & "#" & ToSundayDate.value & "#" stWhere = stWhere & "AND CrewTime.TotalHours>" & " 0.0" stWhere = stWhere & "AND CrewTime.WhichCrew =" & "2" 'Set the server filter on the CrewTime Recordset. MSODSC.RecordsetDefs.Item("CrewTime Other Hours Query").ServerFilter = stWhere End if End Sub </SCRIPT>
Currently if the search doesn’t retrieve any records matching the criteria, nothing happens and it appears to the user that the search didn’t even occur. I’d like the user to receive a message that no records were found. I’ve tried adding the following “Elseif” statement to the code above between “End if” and “End Sub” (I’m pretty much a novice when it comes to writing code, so I’ve probably tried to make some things work that obviously won’t to someone with more experience.):
Elseif msodsc.DataPages(0).Recordset.RecordCount = 0 then msgbox “No matches found” End if
I’ve also tried a variety of “Else” statements including just a simple Else msgbox “No matches found” before the “End if”. I thought about trying to add an Attachevent to the button click that runs the OnFilterComboChange but I'm unsure about how to do it.
I’ve found code online that includes a message if there are no records found but they seem to either run automatically after the page initially loads (rather than after a search) or they clone the entire recordset first. Logically it would seem like it should fit into my OnFilterComboChange subprocedure as some kind of Else or Elseif statement.
sorry I can't help you with "Microsoft Access data access page" programming, but one part of your problems seems to be a misunderstanding about IF in general.
If your If clause looks like this:
If (FromMondayDate.value <> "" and ToSundayDate.value <> "") Then
Then all Else or ElseIf clauses will be executed only, if the expression
(FromMondayDate.value <> "" and ToSundayDate.value <> "")
evaluates to False. But you want to test for an empty result set for the case (condition) that the user entered valid date values. So the structure of your code should be:
If (FromMondayDate.value <> "" and ToSundayDate.value <> "") Then do the filtering If <result set is empty > Then Message "Nix found for " & FromMondayDate & " - " & ToSundayDate Else ' Diag Message "found something" End If Else Message "bad user input; can't filter!" End If
I hope you can use the sample you found to check for "<result set is empty >".
Good luck!
< Message edited by ehvbs -- 7/10/2006 1:30:39 AM >
Thanks for your reply. I think I understand where you’re going about the IF statements. So rewrote the code moving my second “IF” statement but it still doesn’t work.
Sub OnFilterComboChange() Dim stWhere 'Don't do anything unless user selects a value from each list box. If (FromMondayDate.value <> "" and ToSundayDate.value <> "") Then stWhere = "CrewTime.CrewDate>=" & "#" & FromMondayDate.value & "#" & " " stWhere = stWhere & "AND CrewTime.CrewDate<=" & "#" & ToSundayDate.value & "#" stWhere = stWhere & "AND CrewTime.TotalHours>" & " 0.0" stWhere = stWhere & "AND CrewTime.WhichCrew =" & "2" 'Set the server filter on the CrewTime Recordset. MSODSC.RecordsetDefs.Item("CrewTime Other Hours Query").ServerFilter = stWhere If msodsc.DataPages(0).Recordset.RecordCount = 0 then msgbox "No data found." End if End if End Sub
Where I’m getting stuck is what code will check the results of my search. Currently, the user puts in their dates and if there are records that fall into that time block that meet the additional criteria spelled out in the “stWhere” statements, it returns and displays those records. The original sub code doesn’t call for a count but the second If statement does. My thoughts are either a) the second If statement isn’t written correctly (although I don’t get an error message when the page opens), or 2) if the first part of the code isn’t calling for a count, the second part can’t either - it should be written as a separate subprocedure. If that’s the case, how do I get it to run AFTER my search and not as soon as the page is loaded?
If DAPs are just one more kind of client side scripts to access a database, than "MsgBox <WhatEver>" should work. Futhermore I trust you can tweak the settings of your IExplorer to show errors.
Then check:
'Set the server filter on the CrewTime Recordset. MSODSC.RecordsetDefs.Item("CrewTime Other Hours Query").ServerFilter = stWhere ' <- no error here MsgBoooox msodsc.DataPages(0).Recordset.RecordCount ' <-- error here
'Set the server filter on the CrewTime Recordset. MSODSC.RecordsetDefs.Item("CrewTime Other Hours Query").ServerFilter = stWhere ' <- no error here MsgBox msodsc.DataPages(0).Recordset.RecordCount ' <-- no error here, correct count for non empty result set, 0 for empty result set
If .RecordCount is -1 always, try to test for .EOF
'Set the server filter on the CrewTime Recordset. MSODSC.RecordsetDefs.Item("CrewTime Other Hours Query").ServerFilter = stWhere ' <- no error here If msodsc.DataPages(0).Recordset.EOF Then ' EOF may be a bad name MsgBox "empty" Else MsgBox "Not empty" End If
HEY, I/we could need some help from people knowing all about DAPs!
Playing with the code some more, I think I’m finally grasping what you were trying to say about the IF and Else statements. If I write an Else statement it’s just going to go back and check itself against my initial IF statement - If (FromMondayDate.value <> "" and ToSundayDate.value <> "") Then - which is always going to be True because the user is always going to select dates so the fields will not be empty. I was trying to write my ELSE statement against my conditions after the THEN and that’s not going to work.
So at least I know why it fails. I just need to figure out how to write a subprocedure to check the results of the search and then fire off a message if nothing is found. So even though I haven’t found the solution yet, I won’t keep spinning wills on code that won’t work.
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Need message when no records found 
Printable Version
Need message when no records found - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread