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"
FormsInquiryRecord!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 [A] = TRUE AND [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 ([A] = TRUE)
  AND ([B])

is NOT the same as :

WHERE ([A] = (TRUE AND [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.

[mcervenka1]

NeopPa and TwinnyFO. thank you both. I have no training in Access and learn from reading and doing. Thank you for so much for the support. May I ask one last favor, I am such a winnie about this, would you please insert your suggested code in the all the code I provided above to display all together . I don't know where to start With Me and End With. Please.. and thank you

[NeoPa]

MCervenka:
May I ask one last favor, I am such a winnie about this, would you please insert your suggested code in the all the code I provided above to display all together.

Sure. Note also the extra line at #2. This is related to Require Variable Declaration. An extremely important point to be aware of. Also your code might need some changes because it's actually unworkable. Always try to compile your code before posting. Uncompilable code is a right royal PITA for everyone involved as it misleads everyone.

NB. My changes will hopefully work but I can't test them. Your original code couldn't possibly compile, so trying to compile will always help you find the more obvious problems before you get into worrying about the logic.

The updated code would be :

Option Compare Database
 Option Explicit
 
Private Sub txt.Search_AfterUpdate()
On Error GoTo EH
    Dim strFilter As String
 
    With Me
        strFilter = "([ReceiptNumber] Like '*%RS*') OR " _
                  & "([ANumber] Like '*%RS*') OR " _
                  & "([SerialNumber] Like '*%RS*')"
        .Filter = Replace(strFilter, "%RS", .Text259)
        .FilterOn = True
    End With
    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

[mcervenka1]

Hello NeoPa. Have been temporary assigned to another task, but finished and now return to finish with problem. I made above changes and works well but still receive popup Parameter Value box "Enter Parameter Value" "Forms!Inquiry Record!Text259". the %RS you provided above, is that a suggest name change to a field name?

[NeoPa]

No. That's a marker in the original text that is replaced by the value found in Me.Text259.

To have an idea why your code is behaving that way I'd need to see exactly what you're using. I can't see any way that would be the result of using the code I suggested. I suspect there's been a slip 'twixt cup & lip.

Show us what you've got and we'll try to understand what's gone wrong.

[mcervenka1]

what do you need for me to provide you with

[mcervenka1]

can I use the same VBA commands on to different forms

[NeoPa]

MCervenka:
what do you need for me to provide you with

The code you're using. Copy/pasted directly from your project. The exact code used that produced the reult you reported in post #18.

[NeoPa]

MCervenka:
can I use the same VBA commands on to different forms

Yes. However, if that's a separate question then it' doesn't fit here. If it's related to this question I don't understand how, so let's take this step by step and build only on what we know.

[mcervenka1]

Neopa so sorry, I reread my posting after I sent it. What I meant to say is, I copied macro from one form "INQUIRY" and used it as another form "UPDATE". Both forms are using the same macro/code. Since the INQUIRY query macro calls for the INQUIRYFORM, is it using the macro/code INQUIRY query and that is why I am receiving the PopUp Message box Parameter Query message which references INQUIRYFORM in my UPDATE form?

[NeoPa]

Now I'm very confused. In post #16 you asked how to implement the code. I posted a suggestion for you. Nothing since then has seemed like a continuation of the conversation. The next step would have been to try that suggestion, however, all of your posts since have been based on what I can only guess is some other idea of a solution. I can't help you with that (I have absolutely no idea what that might even be and it's certainly not something that would fit in this thread).

If you would like to step back to post #17 and proceed logically from there then I'm happy to continue. Otherwise we should neither of us be going off at tangents in a thread that's already - essentially - concluded. Does that all make sense?

[mcervenka1]

Neopa.....not meant to confuse, guess I tried to mix two issues together. I tried to introduce issue on how to stop the pop up message for enter parameter value from the query from displaying when the information is already entered on a form. So I was guestimating (guessing/estimating) that when I copied the form INQUIRY and used it as UPDATE form, it carried over the same macro thus calling for the parameter value used on the INQUIRYFORM instead of the UPDATEFORM. Mia culpa. You have been nothing short of amazing in your assistance and responses.

[NeoPa]

It's really a pleasure to help. Feel free to post as a separate question if it's something you still need help with.

[mcervenka1]

NeoPa. what is the best way to receive assistance if it would be better to provide you copy of a form, query or the table which contains records or query information that is being asked about. do not see way to attach anything

[twinnyfo]

Mcervenka1,

To add attachments to your post, you would click the “advanced” button instead of post reply.

We will accept forms, tables and queries, but that is typically not the preferred method. Usually, there is a specific problem on a specific form/report/query/table and we will look at that issue specifically and provide assistance in troubleshooting or providing possible solutions. Usually, if the issue requires a download of your project, we will ask for it.

As a reminder, if there is a new issue/question, please post to a new threaded discussion (as mentioned previously).

[NeoPa]

By a very long way, the best method of getting assistance is to take the time to put it clearly into words. This has many benefits. It helps you to understand your own situation more clearly. I know this because I've seen it many times on here. I also know it for myself as I've often ended up not requiring help once I've made the effort to explain it as clearly as I can. It also means that we don't all waste loads of time trying to work out what the question should be all the way through the process of trying to help.

Now, I'm not generally considered an idiot, so I do understand that doing that can be hard. More so for some than others. Not everyone speaks English natively and I have a great respect for those that manage to get by in a foreign language. The very idea that it's been attempted though goes a long way to convincing me to spend quality time assisting in such a thread.

Sometimes pictures can be helpful. This is rare. Normally we can see this is an attempt to avoid making the point more clearly in words. Definitely there are exceptions, but they're quite rare. Sometimes a well explained situation can be further clarified by pictures.

As a general rule we will be put off by anyone loading a copy of their project without first being asked to do so. I sometimes do so with members I've developed a friendly relationship with. I'm generally happy to do so for other experts. I'll even use TeamViewer to connect with some if they need it. I'm never happy to see such levels of support simply expected because some members are too dim even to realise what doing that means.

Two other reasons for avoiding this without being specifically requested are :

1. The expert may have a version of Access that's not compatible with the member's project.
2. Many experts are on networks where such downloads are prohibited. These are often enforced electronically so they couldn't even if they wanted to.

Obviously, as Twinny pointed out, there are ways of attaching files. You will see this if you select the Advanced option. Such attachments should be very carefully considered before use though, as explained above.

[mcervenka1]

Hello NeoPA. I think I have a brainteaser for you. When I use the form (Update Record) with the above VPA, it works good but it also ask for input through a parameter query popup box. I have researched and found the parameter query is based on a embedded macro located in another form (Inquiry Record). I believe I caused this when I copied the Inquiry Record form with the embedded macro and renamed it Update Record. Can that be the reason??? If so, how do I get rid of the embedded macro in the Update Record form.

[mcervenka1]

Correction. when I copied the form called "Inquiry Record" with the embedded macro and renamed it "Update Record" .....

[mcervenka1]

oh yes to make it more interesting ... in the Update Record command button properties "On Click" it only shows event proceedings (You VBA code) it does not show the embedded macro coding.

[NeoPa]

I'm not sure I can answer that. It would require some understanding of what's in the macro that you have somehow managed to get into your Form. If you need this macro and also need help with it then I suggest you post a question thread about it. I don't believe it fits in this one.

If you don't need it then simply remove it. I can't help you decide as I have little understanding of what it is or why you have it in the first place.

I'm sure someone will be able to help if you post a new question. I'll probably see it but have very limited experience with macros in Access so may not be able to be of much use. Nevertheless, there are many here who can and will offer help.

[mcervenka1]

Hello NeoPa
PLease look at the attachment containing the VBA that you provided me and advised if I have everything written correctly. When I open form I am getting the error message "Complier Error, expect end of statement". Also while researching I looked at several VBA coding and they all had the "On Error Goto instructions" after the DIM statements? Does it matter where the On Error Goto statement is placed?

[twinnyfo]

mcervenka1,

Please double check that your Text Box is named "txt.Search". If not, then remove the dot from that Sub's Name. If so, rename it to "txtSearch" and remove the dot from the subs name.

See if that will help, as that dot appears to be the cause of the problem in your error.

[NeoPa]

Good spot Twinny. I certainly hadn't intended to include the dot (.) and that would certainly cause it to fail.

TBF my original post did have a Government Health Warning on it in case of any mistakes, as I wasn't able to test it.

[mcervenka1]

NeoPA... I do not know it this is allowed, if not I apologize. Attached is screen shots of records in database, query for INQUIRY RECORD, INQUIRY RECORD FORM, and Properties and VBA coding. This is the same type of documents used in UPDATE RECORD. You asked to see some information when you were trying to help earlier. I made changes to txtSearch. Now when I enter the receiptnumber or ANumber or Serial number in the search field, the search function does not work. Happy to say I do not get a compile error... Are you able to assist? If not, I understand and thank you for the time you have spent trying to assist me

[NeoPa]

MCervenka:
Are you able to assist?

Not easily. I see nothing which indicates what you're trying to search for and I don't see what your .Filter property is set to.

Please add, just before the first End With line, a line that says Debug.Print .Filter; then post the results of that line.

Certainly I'd like to help if I can, but it's very difficult when I don't have the information I require available. If I were doing it for myself I'd follow a number of steps - each one dependent on the results of the last - to take me slowly but surely towards an understanding of what's wrong. That's very difficult to manage via a forum. Let's see what we can do anyway.

NB. This is perfectly acceptable usage of the forum. You're continuing to explore and work on a single issue. That's fine - as long as you continue with the same topic.

[mcervenka1]

If you are talking about Form.Filter Property? If not where can I find the information for you

Filter......(no Setting)
Filter on Load ...NO.....( has NO for a setting)

also inserted the Debug.Print .Filter; are able to enter the receipt number but the search function is not working. No change window after click on the Search button. No errors when I ran the debug. Sent snap shot of screen.

[NeoPa]

Before or after you run that code type Ctrl-G from anywhere in Access and it will go to (and make visible, if not already) the Immediate pane. Please copy the contents of that after the code has run once and we'll see what it says.

PS. How you've updated the code is correct. The indenting is wrong but that won't affect what we're looking at here so ignore that - at least for now.

[mcervenka1]

Please see attachment

[twinnyfo]

After you execute the search, there should be some text, indicating the contents of the Filter, listed just below the "Immediate". Either the Filter is completely empty, or you provided this screenshot before you ran the code.

[mcervenka1]

see attachment. used SRC3333333333 in the search field. There is a record in the table with this receipt number. No search results were given as seen on the form screen snapshot. Used ctrl-g after and provided snapshot of that. I understand about filter being empty. Is it empty because like I said, when I entered the receipt number and push the SEARCH button no resolts were returned.

[twinnyfo]

First, it is not necessary to provide screenshots of the immediate window. Simply copy and paste the results into your post. This is much easier for us to manage.

Second, I am not certain your code is even firing when you enter search criteria. At an absolute minimum, the Immeidate Window should show this:
[ReceiptNumber] Like '') OR ([ANumber] Like '') OR ([SerialNumber] Like '') and that assumes the search criteria is blank.

Again, are you certain that this VBA module is attached to the form you are working on, that the text box is named txtSearch and that the procedure is properly associated with this text box?

[NeoPa]

I'm with Twinny on this. It looks very much like your Event Procedure is not set to be called for the appropriate event. A quick way to fix this, assuming the name of the Event Procedure is correct and matches the name of the Control, is to Cut and Paste all the code from the module. The act of Pasting it back in causes Access to assign the correct properties automatically for validly designed and named Event Procedures.

You can test if it's properly set by putting a breakpoint (See Debugging in VBA.) on the line after the Debug.Print line.

Also, though it shouldn't make too much difference, your Immediate Pane is scrolled off to the right when you took the last picture so would miss the start of any text printed there. As Twinny says, copy the contents instead.

[mcervenka1]

oK. I deleted and created a new UPDATE RECORD form. I copies all VBA from previous form into new form and made applicable changes for the titles of the new command buttons....ie..Private Sub Command 445 Click() and Filter Text 438. (See below). When I open the form and enter valid receipt number the form does not respond. When I go to design view and press RUN/RunSubform, I receive a MACRO box
asking for MACRO NAME. When I run DEBUG, I receive no errors. I ran the Cntrl G and it only displayed the word "Immediate" with nothing below it

Option Compare Database
Option Explicit
 
Private Sub txtSearch_AfterUpdate()
On Error GoTo EH
    Dim strFilter As String
 
    With Me
         strFilter = "([ReceiptNumber] Like '*%RS*') OR " _
                  & "([ANumber] Like '*%RS*') OR " _
                  & "([SerialNumber] Like '*%RS*')"
         .Filter = Replace(strFilter, "%RS", .Text438)
         .FilterOn = True
    Debug.Print .Filter;
    End With
    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 Command445_Click()
 
End Sub

[twinnyfo]

Is your Text Box properly named txtSearch and when you look at the properties for that control, does it have [Event Procedure] listed in the "On Click" Event?

[mcervenka1]

for the command button labeled "Search" and contains VBA above:
Name - Command445
Caption - Search
On Click - event procedures

For Text box where Receipt Number or ANumber or Serial Number is entered:
Name - Text438
Caption - empty
On Click - empty no event procedures

For list box command button:
Name - Label439
Caption - 438
Event - shows nothing

[twinnyfo]

So, if you look closely at the cod eyou posted in Post #47, the name of the Procedure is txtSearch_AfterUpdate. This means that the code is looking for a Text box with the name of txtSearch, and when someone enters data into that text box and hits Enter or Tab, the Text Box is then "updated" and this code will fire.

No real need for a "Search button" as your code should execute without it.