?
Solved

Another Access 2007 Search Question

Posted on 2011-09-08
7
Medium Priority
?
366 Views
Last Modified: 2012-06-27
I have the following coding that will find Alias1, Alias2, Alias3, Alias4, and Alias5.  What If I have two or more records with the same Alias.  I need some coding to find records with the same Alias on any of the Alias1, Alis2, Alis3, Alias4, and Alias5.

I need a Find Next Button to find these records.  How can the VBA cdoing be written to do this?

My coding:

Private Sub Command9_Click()

Dim rs As DAO.Recordset

Set rs = Me.Recordset.Clone
rs.FindFirst "[Alias1] = '" & Me.Text10 & "' Or [Alias2] = '" & Me.Text10 & "' Or [Alias3] = '" & Me.Text10 & "' Or [Alias4] = '" & Me.Text10 & "' OR [Alias5] = '" & Me.Text10 & "'"
If rs.NoMatch = True Then
    MsgBox "There are No Records Found for this Alias Name.  Please Try Again"
    Me.Text10.SetFocus
Else
    Me.Bookmark = rs.Bookmark
End If
End Sub

Any help is appreciated.
0
Comment
Question by:jjc9809
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36502904
Try setting a filter:

Me.Filter = ""
Me.Filter =  "[Alias1] = '" & Me.Text10 & "' Or [Alias2] = '" & Me.Text10 & "' Or [Alias3] = '" & Me.Text10 & "' Or [Alias4] = '" & Me.Text10 & "' OR [Alias5] = '" & Me.Text10 & "'"
Me.FilterOn = True
0
 

Author Comment

by:jjc9809
ID: 36503036
Where do I place the coding?  Do you place in the original coding or have it under a Find Next button.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36503074
Hi -

It's completely independent from the other code (don't mix them).

I'd recommend placing it behind a "Find", "Filter" or "Get Records" button (or some other meaningful label).

That code finds and displays all records meeting the specified criteria at once (so it is not a 'find next'... it gets all of them).
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 36503469

No points please, just expanding.

I like to use the filter method suggested by mbizup, but when more than one record matches the filter I also use a popup form that displays the records that match the criteria in a listbox.  In that case, I use the double click event of the listbox, to allow the user to identify the specific record they are looking for.
0
 

Author Comment

by:jjc9809
ID: 36503564
This will work, but I need a place to put an error mesage if no records are found at all.  I do not believe I can say If Me.Filter = Nothing display a message, because this is incorrect use of an object.
So how do i do this with the coding you provided.  Your coding is better than my original coding so I was going to use your coding for my Find Button becuase I am trying to find all records anyway with aan Alias Name.

jjc9809
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 36503713
Use the recordcount property...

Me.Filter = ""
Me.Filter =  "[Alias1] = '" & Me.Text10 & "' Or [Alias2] = '" & Me.Text10 & "' Or [Alias3] = '" & Me.Text10 & "' Or [Alias4] = '" & Me.Text10 & "' OR [Alias5] = '" & Me.Text10 & "'"
Me.FilterOn = True 
If me.recordsetclone.recordcount = 0 then msgbox "No records were found"

Open in new window


You also may want to add a button to toggle the filter off (clear the filter)

Me.Filter = ""
Me.FilterOn = False

Open in new window



___
fyed,

I use a similar approach in many applications.  If there are a bunch of fields that the user needs to be able to filter on, I place unbound controls for them and and an "Apply filter" button to a form, and use a mult-select listbox as you described for a quick view of the results,  to allow the user to select which record(s) to view.
0
 

Author Closing Comment

by:jjc9809
ID: 36504012
Very good!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question