Display single record or duplicate records

[mcervenka1]

MS Access 2010. I am using a split form and when I perform a search for a record, and there are no duplicates, need to display that single record and not the other records in the table. If are two or more records (duplicates), need to display only those duplicates and not other records in the table.

[Narender Sagar]

I think, your question is not clear enough. You need to explain it more to resolve your issue.

[twinnyfo]

mcervenka1,

Welcome to Bytes!

Please review these guidelines for Asking Good Questions. Feel free to modify your question or clarify the exact problem you are having, and we will be glad to look into assisting you.

Thanks for your understanding.

[mcervenka1]

MS Access 2010. I am using a split form with search capability. When I search for a record using a specific search criteria (such as a name), I would like to display only the record and any duplicates of that record found and not the rest of the records in the table. The split form displays the rows of records in table (table format) at the bottom of the screen and displays the individual record found from search at the top half of the screen. In the rows of records at the bottom of the screen, I only want to display the record or its duplicate records found in the search.

[twinnyfo]

You should be able to apply a filter directly to the Form, based upon your Search Criteria. Example:

Private Sub txtSearch_AfterUpdate()
On Error GoTo EH
    Dim strFilter As String

    strFilter = "[FieldName] Like '" & Me.txtSearch & "*'"
    Me.Filter = strFilter
    Me.FilterOn = True

    Exit Sub
EH:
    MsgBox "There was an error filtering the Form!" & _
        vbCrLf & vbCrLf & _
        Err.Number & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", _
        vbCritical, "WARNING!"
    Exit Sub
End Sub

That's about as simple as it gets, but you can make your filter to be as complex as you want/need it to be.

Hope this hepps!

[mcervenka1]

Twinnyfo. The coding works excellent. However, I get the parameter query value popup after I enter my criteria in the search field.

"Enter Parameter Value"
FormsInquiryRec ord!Text259

[mcervenka1]

text259 is the text field I use to enter the value.

[twinnyfo]

What is the code you are using, so we can troubleshoot it?

[mcervenka1]

this is what is in the Filter field on the form property sheet. I am using the ANumber to filter

[ReceiptNumber] Like "*" & Forms![Inquiry Record]!Text259 & "*" Or [ANumber] Like "*" & Forms![Inquiry Record]!Text259 & "*" Or [SerialNumber] Like "*" & Forms![Inquiry Record]!Text259 & "*"

[twinnyfo]

A quick note: If you are referring to controls on the form you are currently using, instead of Forms![Inquiry Record]!Text259, you can simply use "Me": Me.Text259.

Also, when using a wildcard (also because you are using text fields, you must include single quotes around the search text. Thus:

[ReceiptNumber] Like '*" & Me.Text259 & "*' " & _
    "Or [ANumber] Like '*" & Me.Text259 & "*' " & _
    "Or [SerialNumber] Like '*" & Me.Text259 & "*'"

Also note how I added line breaks, to make your code easier to manage on these narrow forums.

Additionally, I will encourage you to name your controls something meaningful, rather than simply Text259. Instead designate it something like txtSearch, so that each time you see that control referenced in your code, you know the purpose of that control. This is all part of having a consistent naming convention for your projects. You can Google search DB Naming conventions--just pick one and stick with it.

Hope this hepps!

[mcervenka1]

THANK YOU......very much

[mcervenka1]

Twinnyfo: Please see coding below. I am still receiving the popup box asking for values

Option Compare Database

Private Sub txt.Search_AfterUpdate()
On Error GoTo EH
    Dim strFilter As String
    
    strFilter = "[ReceiptNumber] Like '*" & Me.Text259 & "*' " & _
                     "Or [ANumber] Like '*" & Me.Text259 & "*' " & _
                     "Or [SerialNumber] Like '*" & Me.Text259 & "*' " & _

    Me.Filter = strFilter
    Me.FilterOn = True
    
    Exit Sub
EH:
    MsgBox "There was an error filtering the Form!"
        vbCrlf & vbCrlf & _
        Err.Number & vbCrlf & _
        Err.Description & vbCrlf & vbCrlf & _
        "Please contact your Database Administrator"
        vbCritical , "WARNING!"
    Exit Sub
End Sub

Private Sub Form_AfterUpdate()

End Sub

[NeoPa]

Please read Before Posting (VBA or SQL) Code. This will make it a lot easier for everyone. You as well as those trying to help you. In this case I can't see how the code you've posted here could possibly run. If you follow the linked instructions we should get past those easily avoided issues though.

mcervenka1:
I am still receiving the popup box asking for values

What values is it asking for? How many?
You've probably used field names that are inaccurate, or simply not available in the dataset you're using. The clue should always be in the message that pops up.

In your case, but not always, your filter string could be set up using :

    With Me
        strFilter = "([ReceiptNumber] Like '*%RS*') OR " _
                  & "([ANumber] Like '*%RS*') OR " _
                  & "([SerialNumber] Like '*%RS*')"
        .Filter = Replace(strFilter, "%RS", .Text259)
        .FilterOn = True
    End With

[twinnyfo]

NeoPa,

For a moment I thought you lost your mind, but line 5 brought it all together! I was thinking about a way to simplify the filter string I wrote, but thought the straightforward approach would be fine.

One question: do Like statements require the usage of parentheses when paired with Or statements?

Great insight and thanks!

[NeoPa]

TwinnyFo:
I was thinking about a way to simplify the filter string I wrote, but thought the straightforward approach would be fine.

And it is Twinny. Perfectly fine. My suggestion is simply an alternative.

TwinnyFo:
One question: do Like statements require the usage of parentheses when paired with Or statements?

Not really no. I prefer to use parentheses because every statement can be mixed with ANDs and ORs quite legally so I like the parentheses to show which of the comparisons are logically separate.

Consider [A] & [B] are Boolean fields.

WHERE [NOPARSE][[/NOPARSE]A] = TRUE AND [NOPARSE][[/NOPARSE]B]

Which (of = & OR) has the higher precedence? They're both comparison operators. Neither is highest or lowest on the precedence tree (Operator Precedence (Transact-SQL) was the closest to Jet/ACE I could find). So, it's not that clear, especially for those that don't know that tree well (Hand on heart do you? I didn't without checking).

So, I always use parentheses to show that :

WHERE ([NOPARSE][[/NOPARSE]A] = TRUE)
  AND ([NOPARSE][[/NOPARSE]B])

is NOT the same as :

WHERE ([NOPARSE][[/NOPARSE]A] = (TRUE AND [NOPARSE][[/NOPARSE]B]))

Obviously, when you know the table you realise that in MOST cases, people will get away without them and not come to any trouble. That seems a highly dodgy approach to me though. You should find in all my posted suggestions that I include parentheses around all separate checks within a WHERE clause.