Suggestions to address blank form when filter finds no results?

[gojets1721]

So I have a form where i recently turned off 'allow additions' and 'allow deletions'. There should never be a time when a user is adding or deleting records from the form itself.

One issue that resulted from that is anytime a user filters the form (whether through a command or even just right clicking on a field & using access' built in filter features) and the filter finds no matching records, the form just goes blank and leaves nothing but the header. This requires the user to exit the form and get back in which can be a hassle.

Previously, when 'allow additions' was on, it would just filter to a blank record and then the user could just clear the filter.

Any suggestions on how to avoid the blank form but not have to turn on 'allow additions'?

 

[theDBguy]

Hi. What happened with the solution we gave you earlier?

Help with filter command when no records found

I have the below command that allows the user to search a field in a form and filter to whatever the user inputs. It works great but I just realized that if no matching records are found, the form just goes completely blank. This requires the user to exit the form and get back in. Ideally, if...

www.access-programmers.co.uk

 

[MajP]

Hi. What happened with the solution we gave you earlier?

Help with filter command when no records found

I have the below command that allows the user to search a field in a form and filter to whatever the user inputs. It works great but I just realized that if no matching records are found, the form just goes completely blank. This requires the user to exit the form and get back in. Ideally, if...

www.access-programmers.co.uk

However I think this is harder than it looks. The OP is now addressing any applied filter not just using a command button. I am not sure how I would do that for filters applied using built in features. The Apply filter event happens before you filter so you do not know what the resulting count would be. The OnFilter event is basically useless.

Occurs when the user opens a filter window by choosing Filter by Form, Advanced Filter/Sort, or Server Filter By Form

Applying a filter will cause the on current event, and I played with the current event and most of the time removing the filter in this event does nothing or crashes the application. So I would be curious how to do this when using any built in method to filter (i.e. pull downs in data sheet or right click in a control). Maybe I am over thinking this, but cannot find a workable event to capture the "after filter" event.
The prior discussion is based on the user using a command button, so that solution is well known.

 

[MajP]

One possible approach is using the apply filter event and building your own recordset using the selected filter and the recordsource. Getting that syntax correct for any recordsource is not trivial either.

 

[theDBguy]

I am not sure how I would do that for filters applied using built in features.

I don't have it in front of me right now, but I believe I used a combination of the OnApplyFilter and the Current events to handle this situation. I'll come back after I get a chance to review it.

 

[MajP]

I don't have it in front of me right now, but I believe I used a combination of the OnApplyFilter and the Current events to handle this situation. I'll come back after I get a chance to review it.

Thanks

 

[CJ_London]

If you are prepared to have the Navigation buttons showing at the bottom of the form then you can clear it there.

Or just have a 'clear filter' button in the header

Or some code that checks whether any records will be returned with that filter and if not, abort.

Code will depend on how the form is being populated at the moment

 

[gojets1721]

Hi. What happened with the solution we gave you earlier?

Help with filter command when no records found

I have the below command that allows the user to search a field in a form and filter to whatever the user inputs. It works great but I just realized that if no matching records are found, the form just goes completely blank. This requires the user to exit the form and get back in. Ideally, if...

www.access-programmers.co.uk

This seemed like a larger issue than just the command button because it affects all filters, so I thought making a new post was appropriate. Apologies if I should have just added onto the first post

 

[MajP]

So I think I figured out how to do this and it is not trivial IMO.

You have to get the count of what the recordset will have after applying the filter. I do not think you can easily build a filter string that handles all cases. So I did the little known trick of applying a filter to a recordset and opening a new recordset from the filtered recordset. What does not work well is the MsgBox appears 4 times. I have no idea why the event would fire 4 times. The Cancel will cause another msgbox to appear asking for valid data. So I would remove the message box and train the user that if they apply a filter that returns no records they will get an "apply valid data" message. It is a little confusing without the previous message.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
  On Error GoTo errlbl
  Dim fltr As String
  Dim rs As DAO.Recordset
  fltr = Me.Filter
  If fltr <> "" Then
    fltr = Replace(fltr, Chr(34), Chr(39))
    fltr = Replace(fltr, ";", "")
    Set rs = Me.RecordsetClone
    rs.Filter = fltr
    Set rs = rs.OpenRecordset()
    If rs.EOF Then
      MsgBox "No records are returned with the Filter" & vbCrLf & fltr
      Cancel = True
    End If
  End If
  Exit Sub
errlbl:
  If Not Err = 3464 Then
    '3464 is data type mistmatch and the application will prompt for a valid value
    MsgBox Err.Number & " " & Err.Description
  End If
End Sub

This will not fire in the following conditions

The ApplyFilter event doesn't occur when the user does one of the following:

• Applies or removes a filter by using the ApplyFilter, OpenReport, or ShowAllRecords actions in a macro, or their corresponding methods of the DoCmd object in Visual Basic.
• Uses the Close action or the Close method of the DoCmd object to close the Advanced Filter/Sort, Filter By Form, or Server Filter By Form window.
• Sets the Filter or ServerFilter property or FilterOn or ServerFilterByForm property in a macro or Visual Basic (although you can set these properties in an ApplyFilter event procedure or macro).

If someone has a simpler way I would be interested, because this seems awful complicated for something you would assume would be trivial.

 

[arnelgp]

i recently turned off 'allow additions'

you turn it to Yes again.
now, to disable adding new record, add code to the Form's BeforeInsert event to disallow creating new record:

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
End Sub

 

[CJ_London]

I tried a slightly different method to determine records returned and resetting the filter removes the unwanted messages

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

On Error GoTo errlbl
Static prevFltr As String
Dim rCount As Long
Dim fltr As String

  fltr = Me.Filter
  If fltr <> "" Then
    fltr = Replace(fltr, Chr(34), Chr(39))
    fltr = Replace(fltr, ";", "")
  
    rCount = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM (" & Replace(RecordSource, ";", "") & ")" & IIf("" & fltr = "", "", " WHERE " & fltr))(0)
   If rCount = 0 Then
  
      MsgBox "No records are returned with the Filter" & vbCrLf & fltr
      Filter = prevFltr 'assign previous filter
      Cancel = True

    End If
  
  End If

  prevFltr = Filter 'to preserve any pre-existing filters
  Exit Sub

errlbl:

  If Not Err = 3464 Then
    '3464 is data type mistmatch and the application will prompt for a valid value
    MsgBox Err.Number & " " & Err.Description
  End If

End Sub

 

[theDBguy]

Thanks

Okay, sorry for the delay. I was out of town yesterday. I finally got a chance to take a look at my db at work, and @arnelgp is correct. I changed the form settings to AllowAdditions=Yes.

In the Current Event, I have something like this to prevent users from adding new records.

If Me.NewRecord Then
    DoCmd.GoToRecord , , acFirst
End If

In the ApplyFilter Event, this is what I have.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
If ApplyType = acApplyFilter Then
    If DCount("*", "qryPreliminaryProduct", Me.Filter) = 0 Then
        Cancel = True
        MsgBox "No matching records found...", vbInformation, "No Record!"
    End If

End If

End Sub

I haven't reviewed the posted demoes and code, so I apologize if this is duplicate info already.

Cheers!

 

[MajP]

I haven't reviewed the posted demoes and code, so I apologize if this is duplicate info already.

Cheers!

The demos are all slightly different variations of the same technique.
1. Determine the number of records that the filter will return in the apply filter event.
2. Cancel the apply filter event if 0 returned.

 

[theDBguy]

The demos are all slightly different variations of the same technique.
1. Determine the number of records that the filter will return in the apply filter event.
2. Cancel the apply filter event if 0 returned.

I see. Thanks! I think the sticking point was the "blank form" when AllowAdditions=No. So, I just set it to Yes and handled it in the Current event. Cheers!

 

[gojets1721]

Okay, sorry for the delay. I was out of town yesterday. I finally got a chance to take a look at my db at work, and @arnelgp is correct. I changed the form settings to AllowAdditions=Yes.

In the Current Event, I have something like this to prevent users from adding new records.

If Me.NewRecord Then
    DoCmd.GoToRecord , , acFirst
End If

In the ApplyFilter Event, this is what I have.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
If ApplyType = acApplyFilter Then
    If DCount("*", "qryPreliminaryProduct", Me.Filter) = 0 Then
        Cancel = True
        MsgBox "No matching records found...", vbInformation, "No Record!"
    End If

End If

End Sub

I haven't reviewed the posted demoes and code, so I apologize if this is duplicate info already.

Cheers!

Hi @theDBguy. So sorry for the delay. Below is the filter code I use. It's a command that lets the user input a customer name. Where should I place the applyfilter code you listed above?

Private Sub btnSearchCustomerName_Click()
On Error GoTo btnSearchCustomerName_Click_Err

    Dim S As String
    
    S = InputBox("Enter Customer Name", "Customer Name Search")
    If S = "" Then Exit Sub
    
    Me.Filter = "CustomerName LIKE ""*" & S & "*"""
    Me.FilterOn = True
 
btnSearchCustomerName_Click_Exit:
    Exit Sub

btnSearchCustomerName_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
    
    Resume btnSearchCustomerName_Click_Exit
  
End Sub

 

[theDBguy]

Hi @theDBguy. So sorry for the delay. Below is the filter code I use. It's a command that lets the user input a customer name. Where should I place the applyfilter code you listed above?

Private Sub btnSearchCustomerName_Click()
On Error GoTo btnSearchCustomerName_Click_Err

    Dim S As String
   
    S = InputBox("Enter Customer Name", "Customer Name Search")
    If S = "" Then Exit Sub
   
    Me.Filter = "CustomerName LIKE ""*" & S & "*"""
    Me.FilterOn = True

btnSearchCustomerName_Click_Exit:
    Exit Sub

btnSearchCustomerName_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
   
    Resume btnSearchCustomerName_Click_Exit
 
End Sub

Try to copy and paste the OnApplyFilter code I posted in the module for your form. You will have to modify it to use the same query as your form. Then, in the On Apply Filter event of your form, select [Event Procedure] in the dropdown box. I'll have to test if the OnApplyFilter fires when the form is filtered using your code. I am guessing it does, so this might work for you.

 

[gojets1721]

Try to copy and paste the OnApplyFilter code I posted in the module for your form. You will have to modify it to use the same query as your form. Then, in the On Apply Filter event of your form, select [Event Procedure] in the dropdown box. I'll have to test if the OnApplyFilter fires when the form is filtered using your code. I am guessing it does, so this might work for you.

Gotcha. So I did that but it's only partially working. The form doesn't disappear when no records are found. It just shows a blank entry. However no msgbox pops up from the ApplyFilter event. Not sure what is wrong.

Could my original filter code be conflicting with it?

 

[theDBguy]

Gotcha. So I did that but it's only partially working. The form doesn't disappear when no records are found. It just shows a blank entry. However no msgbox pops up from the ApplyFilter event. Not sure what is wrong.

Could my original filter code be conflicting with it?

Can you post a sample copy of your db, so we can test it?