422,530 Members | 1,048 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,530 IT Pros & Developers. It's quick & easy.

Display single record or duplicate records

P: 15
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.
4 Weeks Ago #1
Share this Question
Share on Google+
29 Replies


Narender Sagar
100+
P: 189
I think, your question is not clear enough. You need to explain it more to resolve your issue.
4 Weeks Ago #2

twinnyfo
Expert Mod 100+
P: 2,238
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.
4 Weeks Ago #3

P: 15
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.
4 Weeks Ago #4

twinnyfo
Expert Mod 100+
P: 2,238
You should be able to apply a filter directly to the Form, based upon your Search Criteria. Example:

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSearch_AfterUpdate()
  2. On Error GoTo EH
  3.     Dim strFilter As String
  4.  
  5.     strFilter = "[FieldName] Like '" & Me.txtSearch & "*'"
  6.     Me.Filter = strFilter
  7.     Me.FilterOn = True
  8.  
  9.     Exit Sub
  10. EH:
  11.     MsgBox "There was an error filtering the Form!" & _
  12.         vbCrLf & vbCrLf & _
  13.         Err.Number & vbCrLf & _
  14.         Err.Description & vbCrLf & vbCrLf & _
  15.         "Please contact your Database Administrator.", _
  16.         vbCritical, "WARNING!"
  17.     Exit Sub
  18. 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!
4 Weeks Ago #5

P: 15
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
4 Weeks Ago #6

P: 15
text259 is the text field I use to enter the value.
4 Weeks Ago #7

twinnyfo
Expert Mod 100+
P: 2,238
What is the code you are using, so we can troubleshoot it?
4 Weeks Ago #8

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

Expand|Select|Wrap|Line Numbers
  1. [ReceiptNumber] Like "*" & Forms![Inquiry Record]!Text259 & "*" Or [ANumber] Like "*" & Forms![Inquiry Record]!Text259 & "*" Or [SerialNumber] Like "*" & Forms![Inquiry Record]!Text259 & "*"
4 Weeks Ago #9

twinnyfo
Expert Mod 100+
P: 2,238
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:

Expand|Select|Wrap|Line Numbers
  1. [ReceiptNumber] Like '*" & Me.Text259 & "*' " & _
  2.     "Or [ANumber] Like '*" & Me.Text259 & "*' " & _
  3.     "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!
4 Weeks Ago #10

P: 15
THANK YOU......very much
4 Weeks Ago #11

P: 15
Twinnyfo: Please see coding below. I am still receiving the popup box asking for values
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub txt.Search_AfterUpdate()
  4. On Error GoTo EH
  5.     Dim strFilter As String
  6.  
  7.     strFilter = "[ReceiptNumber] Like '*" & Me.Text259 & "*' " & _
  8.                      "Or [ANumber] Like '*" & Me.Text259 & "*' " & _
  9.                      "Or [SerialNumber] Like '*" & Me.Text259 & "*' " & _
  10.  
  11.     Me.Filter = strFilter
  12.     Me.FilterOn = True
  13.  
  14.     Exit Sub
  15. EH:
  16.     MsgBox "There was an error filtering the Form!"
  17.         vbCrlf & vbCrlf & _
  18.         Err.Number & vbCrlf & _
  19.         Err.Description & vbCrlf & vbCrlf & _
  20.         "Please contact your Database Administrator"
  21.         vbCritical , "WARNING!"
  22.     Exit Sub
  23. End Sub
  24.  
  25. Private Sub Form_AfterUpdate()
  26.  
  27. End Sub
4 Weeks Ago #12

NeoPa
Expert Mod 15k+
P: 30,680
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 :
Expand|Select|Wrap|Line Numbers
  1.     With Me
  2.         strFilter = "([ReceiptNumber] Like '*%RS*') OR " _
  3.                   & "([ANumber] Like '*%RS*') OR " _
  4.                   & "([SerialNumber] Like '*%RS*')"
  5.         .Filter = Replace(strFilter, "%RS", .Text259)
  6.         .FilterOn = True
  7.     End With
4 Weeks Ago #13

twinnyfo
Expert Mod 100+
P: 2,238
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!
4 Weeks Ago #14

NeoPa
Expert Mod 15k+
P: 30,680
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.
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. WHERE ([A] = TRUE)
  2.   AND ([B])
is NOT the same as :
Expand|Select|Wrap|Line Numbers
  1. 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.
4 Weeks Ago #15

P: 15
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
3 Weeks Ago #16

NeoPa
Expert Mod 15k+
P: 30,680
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub txt.Search_AfterUpdate()
  5. On Error GoTo EH
  6.     Dim strFilter As String
  7.  
  8.     With Me
  9.         strFilter = "([ReceiptNumber] Like '*%RS*') OR " _
  10.                   & "([ANumber] Like '*%RS*') OR " _
  11.                   & "([SerialNumber] Like '*%RS*')"
  12.         .Filter = Replace(strFilter, "%RS", .Text259)
  13.         .FilterOn = True
  14.     End With
  15.     Exit Sub
  16.  
  17. EH:
  18.     MsgBox "There was an error filtering the Form!" _
  19.          & vbCrlf & vbCrlf _
  20.          & Err.Number & vbCrlf _
  21.          & Err.Description & vbCrlf & vbCrlf _
  22.          & "Please contact your Database Administrator" _
  23.          , vbCritical _
  24.          , "WARNING!"
  25.     Exit Sub
  26. End Sub
3 Weeks Ago #17

P: 15
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?
1 Week Ago #18

NeoPa
Expert Mod 15k+
P: 30,680
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.
1 Week Ago #19

P: 15
what do you need for me to provide you with
4 Days Ago #20

P: 15
can I use the same VBA commands on to different forms
4 Days Ago #21

NeoPa
Expert Mod 15k+
P: 30,680
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.
4 Days Ago #22

NeoPa
Expert Mod 15k+
P: 30,680
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.
4 Days Ago #23

P: 15
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?
3 Days Ago #24

NeoPa
Expert Mod 15k+
P: 30,680
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?
3 Days Ago #25

P: 15
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.
2 Days Ago #26

NeoPa
Expert Mod 15k+
P: 30,680
It's really a pleasure to help. Feel free to post as a separate question if it's something you still need help with.
2 Days Ago #27

P: 15
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
2 Days Ago #28

twinnyfo
Expert Mod 100+
P: 2,238
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).
2 Days Ago #29

NeoPa
Expert Mod 15k+
P: 30,680
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.
2 Days Ago #30

Post your reply

Sign in to post your reply or Sign up for a free account.