Full Version: Return Msgbox if Form is Blank
UtterAccess Forums > Microsoft® Access > Access Forms
nw2vba
I have 2 forms (main and sub form). The main form contains text and combo boxes for the user to specify criteria. Records based on user criteria from the main form are returned in the sub form. Also, the subform is hidden until the search button is clicked.
If no records are returned in the subform, I would like to display msgbox "No Data Returned" and the subform to remain hidden.
I do appreciate your help!
AQM_UK
If IsNull(Me!fsubMySubForm.Form![PrimaryKeyID]) Then
msgBox "Not data, sorry try again"
You will then need some code to return you back to the unfiltered state.
However, I would not do it this way.
I would do it so that the combos cascade, and if there is not a related record, then that selection does not show up in the list, thus negating the need for this.
It also does not [censored] the user off if they spend 25 attempts and get "sorry no data" 25 times.
Just a few cents extra.
Jim
theDBguy
Welcome to Utter Access!
here's a couple of ways to get the result you want. It depends on how you're populating the subform. Can you show us the method/code you're using to generate the data for the subform?
nw2vba
The subform (Search Results) is based on a query. The form is filtered by the input from the main form.
Code}:
Private Sub cmdGenSearch_Click()
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If cboLocation
If Not IsNull(Me.cboLocTitle) Then
strWhere = strWhere & " AND Employees.[Location] = '" & Me.cboLocTitle & "'"
End If

'If Search text box
If Not IsNull(Me.cboKeyWords) Then
strWhere = strWhere & " AND Employees.[Position] Like '*" & Me.cboKeyWords & "*'"
End If

If Not IsNull(Me.cboKeyWords) And Me.optTitleMatch = True Then
strWhere = strWhere & " AND Employees.[Position] = '" & Me.cboKeyWords & "'"
End If

If IsNull(Me.cboLocTitle) And IsNull(Me.cboKeyWords) Then
MsgBox "Please Enter Search Criteria"
Else
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.[Search Return].Form.Filter = strWhere
Me.[Search Return].Form.FilterOn = True

End If
End Sub
Thanks...
theDBguy
Hi,
One more thing, please... What's the SQL for the query on the subform? If it's a saved query, what is its name?
nw2vba
Sorry it has taken so long for me to respond.
ubform uses the query EmpResultsData.
Thanks,
nw2vba
Oh, I forgot to add the SQL. Here it is:
SELECT Employees.[First Name], Employees.[Last Name], Employees.[E-mail Address], Employees.[Phone Number], Employees.Position, Employees.Location, Employees.Department, Employees.[Dept Other], Employees.[Location]
FROM Employees
Orderot to add the SQL. Here it is:
SELECT Employees.[First Name], Employees.[Last Name], Employees.[E-mail Address], Employees.[Phone Number], Employees.Position, Employees.Location, Employees.Department, Employees.[Dept Other], Employees.[Location]
FROM Employees
ORDER BY Employees.[Last Name];
Thanks...
theDBguy
That's alright. Since you are using a saved query, we can ignore the SQL. Try modifying your code to something like:
!--coloro:blue-->If DCount("*", "EmpResultsData", strWhere) > 0 Then
Me.[Search Return].Form.Filter = strWhere
Me.[Search Return].Form.FilterOn = True
Else
MsgBox "No Data Returned", vbInformation, "No Data"
End If

(untested)
Hope that helps...
djcox100
Hey the DBguy...I have the exact same situation, however, rather then using a saved query, I'm creating an SQL based on the user's selection, and then assigning that to the record source of the sub-form. How can I use the dcount method to give a count of the number of records returned?
djcox100
Actually, it is ok, I have figured it our. I just used the where part of the SQL I created to be the criteria for the dcount. Thanks guys, I have gotten lots of great ideas from this post.
nw2vba
Thanks DBguy, it works like a charm!!!
theDBguy
You're welcome. Happy to help. Sorry it took a long time to get all the information needed to provide the solution. Good luck with your project.
theDBguy
djcox,
Glad to hear that you learned something here and was able to figure out your problem on your own.
Continued success with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.