Form blank when no records found

[kevinh2320]

On my form, I have a StartDate and EndDate fields for the user to search records. I am using the AfterUpdate event on the EndDate field to trigger the action. If my vba code is set to:

Me.Requery

The code works perfectly and displays the records within the chosen date range. However, if the user enters a date range where no records are found the form displays only the form header leaving the user with a poor experience. I'm not sure how to fix this. I've tried several things to prompt the user their search found no records but keep getting coding errors. Below is my latest attempt. Any suggestion of how to make this work would be appreciated.

Private Sub mo_assigned_date_max_search_txtbox_AfterUpdate()

If Me.Requery Is Null Then

MsgBox "There are no records within the date range entered. Please try again."

Else

Me.Requery

End If

End Sub

 

[Gasman]

Me.Requery can never be Null, it is not a property of the form, but a method.
Use a Dcount() with the same criteria to see if any records exist first before setting the filter.

I would have thought that would be reported on a compile?

 

[tvanstiphout]

> for the user to search records
We have to use precise language. This is not searching, but filtering.

> if the user enters a date range where no records are found the form displays only the form header
That is unusual. Is the form's AllowAdditions property set to False? Is DataEntry set to True?

To check if a form is displaying zero records, you'd write:
if me.recordsetclone.recordcount = 0 then

BTW, why not use the zero-code approach of having the date field on the form, and explaining to the users how to right-click that control and get many more date-specific filter options than you could (should) implement on your own?

 

[Pat Hartman]

I think this is "searching" if the where clause is in the RecordSource query.

The problem with the form is that it is set to not allow additions. Maybe a better view might be an unbound main form that only shows the subform when records are found.

 

[The_Doc_Man]

On my form, I have a StartDate and EndDate fields for the user to search records. I am using the AfterUpdate event on the EndDate field to trigger the action. If my vba code is set to:

Me.Requery

The code works perfectly and displays the records within the chosen date range. However, if the user enters a date range where no records are found the form displays only the form header leaving the user with a poor experience. I'm not sure how to fix this. I've tried several things to prompt the user their search found no records but keep getting coding errors. Below is my latest attempt. Any suggestion of how to make this work would be appreciated.

Private Sub mo_assigned_date_max_search_txtbox_AfterUpdate()

If Me.Requery Is Null Then

MsgBox "There are no records within the date range entered. Please try again."

Else

Me.Requery

End If

End Sub

This should have caused an error to be displayed. Do you have notifications turned off? As pointed out by Gasman, Me.Requery is defined by the rules of a subroutine and not of a function. Therefore it has no value associated with it. The compiler should have barfed immediately.

If you have notifications turned off, turn them on immediately. Notifications are your friends during debugging and development.

 

[kevinh2320]

> for the user to search records
We have to use precise language. This is not searching, but filtering.

> if the user enters a date range where no records are found the form displays only the form header
That is unusual. Is the form's AllowAdditions property set to False? Is DataEntry set to True?

To check if a form is displaying zero records, you'd write:
if me.recordsetclone.recordcount = 0 then

BTW, why not use the zero-code approach of having the date field on the form, and explaining to the users how to right-click that control and get many more date-specific filter options than you could (should) implement on your own?

The Allow Additions is turned off. All of the data that comes into the database is imported via an external source. I don't want users to be able to manually enter new records. I'm only allowing them to update certain fields. If I turn on Allow Additions and enter a date range that I know is not in the dataset then the form does display normally.

I'm new to VBA so struggling here a bit. My main objective here is to present the user with a msgbox notifying them that they've enter a date's not within the dataset and tell them to try again.

 

[Edgar_]

if the user enters a date range where no records are found the form displays only the form header leaving the user with a poor experience. I'm not sure how to fix this. I've tried several things to prompt the user their search found no records but keep getting coding errors.

There are a few alternatives for you, but you could have a main form with subform setup where you put both your textbox for filtering and your subform control in the main form. Then you make a form that you want to display when there are no records, instead of showing a message (because a msgbox is a bad user experience in this case).

Once you have that setup, which is typical, you can put this in a module:

'// #############################################################################################
'// Email: edgarfreelancing@gmail.com
'// Last update: 21/08/2022
'// 
'// Description:
'// Filters a subform from a textbox in a main form, if no results, it shows another form
'// 
'// Parameters:
'// mainForm: Main form object where the textbox and the subform controls are
'// textBoxName: The name of the textbox that will be used to filter the subform, must be in main
'// filterField: The name of the field by which you will filter, must be part of subform recordset
'// subformControlName: The name of the subform CONTROL, do not confuse with the name of the form
'// targetFormName: The name of the form you're filtering
'// emptyFormName: The name of the form you want to show where there are no records
'// 
'// Example usage in textbox change event (can be modified for other cases):
'// FancyResults Me, "txtSearch", "CityName", "sf_cities", "f_cities", "f_empty"
'// #############################################################################################

Public Sub FancyResults( _
    mainForm As Form, _
    textBoxName As String, _
    filterField As String, _
    subformControlName As String, _
    targetFormName As String, _
    emptyFormName As String)

    Dim search As String
    search = mainForm.Controls(textBoxName).Text
 
    Dim filterCriteria As String
    filterCriteria = filterField & " LIKE '*" & search & "*'"
 
    With mainForm.Controls(subformControlName)
        If .Form.Name <> targetFormName Then .SourceObject = targetFormName
        .Form.Filter = filterCriteria
        .Form.FilterOn = True
        If .Form.Recordset.RecordCount = 0 Then .SourceObject = emptyFormName
    End With
End Sub

Check the attached file to see it in action. You can call this from the change event of your textbox, check the example usage too.

 

[ebs17]

present the user with a msgbox

Me.Requery
If Me.Recordset.EOF Then MsgBox "No records. Do something different.", vbOKOnly

 

[Pat Hartman]

I'm new to VBA so struggling here a bit. My main objective here is to present the user with a msgbox notifying them that they've enter a date's not within the dataset and tell them to try again.

There are other very simple ways to keep users from adding records in addition to setting the AllowAdditions to No.

You can use the subform technique I mentioned earlier or you can leave your form as just a main form.

Set the AllowAdditions to Yes.
Then in the form's BeforeInsert event, quietly cancel any addition - or give the user an error message.

Msgbox "Please do not attempt to enter new records using this form.",vbokOnly
Cancel = True
Me.Undo

Cancel = True tells Access to not save the dirty record
Me.Undo removes the typing so the user doesn't have to.

 

[kevinh2320]

Me.Requery
If Me.Recordset.EOF Then MsgBox "No records. Do something different.", vbOKOnly

This produced the results I was looking for. Thank you so much!