How can I remove the extra files?

Question

I am trying to create a form out of a query and a search form which gathers the inputs from the user. The three inputs are Start Date, SO #, and Job.

In this example I only inserted 11/06/2025 for Start Date. It resulted in 7 records which is correct, now I just wonder how I can take out the "extra" records being displayed on the form itself. As you can see on the picture of the form it shows 7 on the bottom left. When i go onto the 2nd, 3rd, and 4th record, they are the exact same as the first. How can I get rid of those "extra" records?

This the code I used for the search form

Private Sub btnFind_Click()

Dim strWhere As String

' Start Date

If Not IsNull(Me.txtStartDate) Then

    strWhere = strWhere & "[START DATE] = #" & _

               Format(Me.txtStartDate, "mm/dd/yyyy") & "# AND "

End If

' SO #

If Not IsNull(Me.txtSO) And Me.txtSO <> "" Then

    strWhere = strWhere & "[SO#] LIKE '*" & _

               Me.txtSO & "*' AND "

End If

' Job

If Not IsNull(Me.txtJob) And Me.txtJob <> "" Then

    strWhere = strWhere & "[JOB] LIKE '*" & _

               Me.txtJob & "*' AND "

End If

' Remove last AND

If strWhere <> "" Then

    strWhere = Left(strWhere, Len(strWhere) - 5)

Debug.Print strWhere



    DoCmd.OpenForm "BackOrderTicket", , , strWhere

Else

    MsgBox "Please enter at least one search value.", vbInformation

End If

End Sub

Answer 1

I took a look at the sample accdb. If that is real data, I urge you to remove it from that public Dropbox location where anyone can get to it.

That said, everything is working as it is currently set up, so what we need to know is WHY you want a different result.

The query called TempBO filters by date. It returns 7 records for the hard-coded date in it because that's how many records have that date.

In the subform, your query, called BackOrderSubForm, filters on two fields: Job and SO# (which is a dangerous name for a field in a table, btw). So any record in the table which has those three fields in common will be displayed in the subform. There are 4 such records among the 7 filtered by date and by JOB 880035 and by SO# 501800. There are three such records among the 7 filtered by date and by JOB 880034 and by SO# 201800. When you look at any record in the subform among the 7, that's what you see.

Interestingly enough, the size of the subform allows only 3 records to be visible. It has scrollbars disabled, so that hides the fourth record in it, thus potentially misleading the user about the results. They'd never see the fourth or later records in the subform!

Explain what you DO want to see. There are 7 records, as filtered by the selected date -- that would change, of course, for other selected dates. The subform filters according to the parameter in the query which is its recordsource, by JOB and SO#. Because the there are two different JOBs in the 7 selected for the date, 4 will appear in the subform with one JOB and 3 will appear in the subform with the other. The "hidden" row in the subform -- due to lack of scrollbars and insufficient display height-- probably confuses things.

So, in order to suggest changes, other than adding the vertical scrollbar, we need to know, from a business perspective, why this is not accurate and appropriate.

Answer 2

Hi. Thanks for sharing your db. In your first post, you said the query produces the correct 7 records. Then, you also said the following:

When i go onto the 2nd, 3rd, and 4th record, they are the exact same as the first.

When I opened your query, I noticed that the top 3 records have the same Job# and Case Description. But then, the 4th record has a different Job# and Case Description from the first three. Of course, the rest of the records had the same Job# and Case Description as the 4th record. Based on that observation, I am not sure what you mean by your quoted statement above. It seems if the query is correct, then the form must be correct also. Am I missing something?

As you can see from the images above, the fourth record is not the same as the first one.

Answer 3

My guess is that your query might be producing duplicate records. It's hard to verify that without seeing your db.