New records not showing in form

[matey56]

I have a problem I can't seem to figure out. I have a form with a bunch of search filters to help users find records. They can also create new records. The underlying query shows all records created but the form doesn't show any of the new records we add regardless of clearing all filters. I checked and it's updatable. I have tried all the things that are obvious to me but what could be some reasons this is happening?

[madpiet]

I have a problem I can't seem to figure out. I have a form with a bunch of search filters to help users find records. They can also create new records. The underlying query shows all records created but the form doesn't show any of the new records we add regardless of clearing all filters. I checked and it's updatable. I have tried all the things that are obvious to me but what could be some reasons this is happening?

If you open a form in Data Entry mode, you can't see the existing records. That's by design.

[matey56]

If you open a form in Data Entry mode, you can't see the existing records. That's by design.

It's not in data entry mode when it's opened. I have a button for users to add records via the GoToRecord - New macro. Could this be the problem?

[moke123]

Have you tried refreshing or requerying the form after the new records are added?

[matey56]

Have you tried refreshing or requerying the form after the new records are added?

Okay I found the reason. I just don't know how to fix it. I have four fields at the top of the form where users can filter records based on the data in the below fields. The problem is, if any of those four fields are blank the record doesn't show in the form. What can I do to my code to allow it to show records if any of the fields are blank?

Me.Filter = "[Number] like '*" & FIND & "*'" & " AND [Region] like '" & FIND2 & "*'" & " AND [Status] like '*" & FIND3 & "*'" & " AND [Location] like '*" & FIND4 & "*'"

[madpiet]

Okay I found the reason. I just don't know how to fix it. I have four fields at the top of the form where users can filter records based on the data in the below fields. The problem is, if any of those four fields are blank the record doesn't show in the form. What can I do to my code to allow it to show records if any of the fields are blank?

Me.Filter = "[Number] like '*" & FIND & "*'" & " AND [Region] like '" & FIND2 & "*'" & " AND [Status] like '*" & FIND3 & "*'" & " AND [Location] like '*" & FIND4 & "*'"

Add another button that turns off the filtering?

[matey56]

Add another button that turns off the filtering?

I do have a Clear button with the code below. The records with blank fields still don't show after I run the event.

Private Sub btnClearAll_Click()Me.FIND.SetFocus
Me.FIND = ""
Me.FIND2.SetFocus
Me.FIND2 = ""
Me.FIND3.SetFocus
Me.FIND3 = ""
Me.FIND4.SetFocus
Me.FIND4 = ""
Me.FilterOn = False
Me.Requery
End Sub

[June7]

By "four fields at the top of the form", do you actually mean "four unbound controls"?

Provide an alternate value for record field when it is Null. Since all 4 fields are string type, use an empty string. "Nz([Number],'') Like

[jojowhite]

' What can I do to my code to allow it to show records if any of the fields are blank?
' try evaluating each filters

Dim strFilter As String
If Not IsNull(Me!Find) Then
    strFilter = strFilter & " And [Number] Like '*" & Me!Find & "*'"
End If
If Not IsNull(Me!Find2) Then
    strFilter = strFilter & " And [Region] Like '*" & Me!Find2 & "*'"
End If
If Not IsNull(Me!Find3) Then
    strFilter = strFilter & " And [Status] Like '*" & Me!Find3 & "*'"
End If
If Not IsNull(Me!Find4) Then
    strFilter = strFilter & " And [Location] Like '*" & Me!Find & "*'"
End If
If Len(strFilter) <> 0 Then
    strFilter = Mid$(strFilter, 5)
    Me.Filter = strFilter
    Me.FilterOn = True
Else
    Me.FilterOn = False
End If

you can simplify code in clear button:

Private Sub btnClearAll_Click()
Me.Find = Null
Me.Find2 = Null
Me.Find3 = Null
Me.Find4 = Null
Me.FilterOn = False
Me.Requery
End Sub

[Ken Sheridan]

Okay I found the reason. I just don't know how to fix it. I have four fields at the top of the form where users can filter records based on the data in the below fields. The problem is, if any of those four fields are blank the record doesn't show in the form. What can I do to my code to allow it to show records if any of the fields are blank?

Me.Filter = "[Number] like '*" & FIND & "*'" & " AND [Region] like '" & FIND2 & "*'" & " AND [Status] like '*" & FIND3 & "*'" & " AND [Location] like '*" & FIND4 & "*'"

Calling the LIKE operator with wildcards is unreliable as it does not allow for Nulls at the column positions. A reliable way is to use a query like the following as the form's RecordSource property:

SELECT *
FROM [TableNameGoesHere]
WHERE ([Number] = (Forms![FormNameGoesHere]![Find]
     OR Forms![FormNameGoesHere]![Find] IS NULL)
AND ([Region] = (Forms![FormNameGoesHere]![Find2]
     OR Forms![FormNameGoesHere]![Find2] IS NULL)
AND ([Status] = (Forms![FormNameGoesHere]![Find3]
     OR Forms![FormNameGoesHere]![Find3] IS NULL)
AND ([Location] = (Forms![FormNameGoesHere]![Find4]
     OR Forms![FormNameGoesHere]![Find4] IS NULL);

In each unbound control's AfterUpdate event procedure requery the form with:

Me.Requery

Rather than using text boxes to enter the parameters, however, using combo boxes which return distinct values will ensure that only values in the table can be selected, e.g a combo box with a RowSource property of:

SELECT DISTINCT Region
FROM [NameOfTableGoesHere]
ORDER BY Region;

will ensure that only valid region names can be entered or selected.

[matey56]

Calling the LIKE operator with wildcards is unreliable as it does not allow for Nulls at the column positions. A reliable way is to use a query like the following as the form's RecordSource property:

SELECT *
FROM [TableNameGoesHere]
WHERE ([Number] = (Forms![FormNameGoesHere]![Find]
     OR Forms![FormNameGoesHere]![Find] IS NULL)
AND ([Region] = (Forms![FormNameGoesHere]![Find2]
     OR Forms![FormNameGoesHere]![Find2] IS NULL)
AND ([Status] = (Forms![FormNameGoesHere]![Find3]
     OR Forms![FormNameGoesHere]![Find3] IS NULL)
AND ([Location] = (Forms![FormNameGoesHere]![Find4]
     OR Forms![FormNameGoesHere]![Find4] IS NULL);

In each unbound control's AfterUpdate event procedure requery the form with:

Me.Requery

Rather than using text boxes to enter the parameters, however, using combo boxes which return distinct values will ensure that only values in the table can be selected, e.g a combo box with a RowSource property of:

SELECT DISTINCT Region
FROM [NameOfTableGoesHere]
ORDER BY Region;

will ensure that only valid region names can be entered or selected.

Thank you!