Search Results Form

[RandyMyers]

Ok, here is a challenge...

I have a search form where various criteria can be entered...

The results are listed in a form for review and the user can click on a record to open the work order on that particular record. All works fine.

What I would like is if there are no records that meet the matching criteria for the system to display a message that there are no records that match the entered criteria and then return them to the search form...

Here is the code that is on the OK button of the criteria search popup form....


Private Sub Command31_Click()
On Error GoTo WorkOrdersFilter_Err

If (Eval("[Forms]![frmWorkOrdersSearch]![StartDate] Is Null And ([Forms]![frmWorkOrdersSearch]![Open]=0 Or [Forms]![frmWorkOrdersSearch]![Open] Is Null)")) Then
' Open the work orders filtered screen
DoCmd.OpenForm "frmWorkOrdersFilter", acNormal, "qryWorkOrdersFilter", "", , acNormal
End If

If (Eval("[Forms]![frmWorkOrdersSearch]![StartDate] Is Null And [Forms]![frmWorkOrdersSearch]![Open]=-1")) Then
' Open the work orders filtered screen
DoCmd.OpenForm "frmWorkOrdersFilter", acNormal, "qryWorkOrdersFilterOpen", "", , acNormal
End If

If (Eval("[Forms]![frmWorkOrdersSearch]![StartDate] Is Not Null And ([Forms]![frmWorkOrdersSearch]![Open]=0 Or [Forms]![frmWorkOrdersSearch]![Open] Is Null)")) Then
' Open the work orders filtered screen
DoCmd.OpenForm "frmWorkOrdersFilter", acNormal, "qryWorkOrdersFilterDate", "", , acNormal
End If

If (Eval("[Forms]![frmWorkOrdersSearch]![StartDate] Is Not Null And [Forms]![frmWorkOrdersSearch]![Open]=-1")) Then
' Open the work orders filtered screen
DoCmd.OpenForm "frmWorkOrdersFilter", acNormal, "qryWorkOrdersFilterOpenDate", "", , acNormal
End If

' Close the main menu
Form_frmMainMenu.Visible = False

WorkOrdersFilter_Exit:
Exit Sub

WorkOrdersFilter_Err:
MsgBox Error$
Resume WorkOrdersFilter_Exit

End Sub

Any ideas..... I have been trying to play with the RecordCount property, but I have not had any success....

 

[FancyPrairie]

In the OnOpen event of the form, do something like this:

Private Sub Form_Open(Cancel As Integer)

If (Me.Recordset.BOF) And (Me.Recordset.EOF) Then
Cancel = True
DoCmd.Close
End If


End Sub


By the way, you might want to look at this FAQ faq181-5497 It contains a function that will build the Where clause for you. It works for single and multi-select list boxes, combo boxes, text boxes, ranges, dates, options groups, and check boxes. And don't worry about understanding the code, just open a new module and highlight the code in the FAQ and paste it into your new module. Then simply call it.

Your code would be simplified to just this:

Private Sub Command31_Click()
On Error GoTo WorkOrdersFilter_Err

 DoCmd.OpenForm "frmWorkOrdersFilter",,,BuildWhere(Me)

    ' Close the main menu
    Form_frmMainMenu.Visible = False

WorkOrdersFilter_Exit:
    Exit Sub

WorkOrdersFilter_Err:
    MsgBox Error$
    Resume WorkOrdersFilter_Exit

End Sub

 

[RandyMyers]

Ok, this works with one issue...

Private Sub Form_Open(Cancel As Integer)

DoCmd.SetWarnings False
On Error GoTo Err_Command

If (Me.Recordset.BOF) And (Me.Recordset.EOF) Then
' Give message that the item is not found
Beep
MsgBox "Click Ok To Change The Search Criteria", vbInformation, "No Records Where Found Meeting The Criteria"
Cancel = True
DoCmd.SetWarnings True
Exit Sub
Else
Form_frmSystemsSearch.Visible = False
Form_frmSystemsFilter.OrderByOn = False
DoCmd.SetWarnings True
End If

Exit_Command:
Exit Sub

Err_Command:
MsgBox Err.Description
Resume Exit_Command

End Sub


I still get the warning message The OpenForm action was canceled.

Turning warnings off does not surpress this meesage. Any idea of how to stop this message from displaying?

 

[FancyPrairie]

The routine that opens the form needs to check for an error (Cancel causes an error to be returned). Something like this:

OnError Resume Next
DoCmd.OpenForm....
If (Err.Number <> error that cancel produces) then
msgbox "fatal error"