• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Another Access 2007 Search Question

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
jjc9809
Asked:
jjc9809
  • 3
  • 3
1 Solution
 
mbizupCommented:
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
 
jjc9809Author Commented:
Where do I place the coding?  Do you place in the original coding or have it under a Find Next button.
0
 
mbizupCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dale FyeCommented:

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
 
jjc9809Author Commented:
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
 
mbizupCommented:
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
 
jjc9809Author Commented:
Very good!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now