MSACCESS

I want to have progressive search from the listview control pop-up and update the textbox.  The search from any row or any column and filter accordingly
thomas_premAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Please explain further. Your question doesn't really make much sense.

What do you want to filter? Are you trying to filter a Form, or do you want to filter a Listbox?

What textbox do you want to update, and where would the updated data come from?
0
thomas_premAuthor Commented:
On click in a text box, I want listview pop-up menu should open, from that I should select any row or any column should filter automatically.  That may be either text or numeric and automatically the text box should be updated from the selection.  
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There is no facility in Access to do this. You can build a small form with a Listbox on it, and then populate that Listbox with the data your user should select from. Once the user selects a value and closed that popup form, you can then use VBA code to fill the Textbox on your main form.

The simplest way to do this is to pass in the name of the calling Form and Control in the OpenForm method. You can do this with the OpenArgs argument, and then refer to those values when closing the Listbox form down:

DoCmd.OpenForm "YourListboxForm", acViewPreview, , , , acDialog, Me.Name & ";TheControlNameToUpdate"

Now, in your Listbox form, after the user makes a selection:

Dim sForm as string
Dim sControl as string

sForm = Split(Me.OpenArgs, ";")(0)
sControl = Split(Me.OpenArgs, ";")(1)

Forms(sForm).Controls(sControl) = Me.YourListBox

As to "any row or column should filter automatically" - again, I don't know what you're talking about. The Access Listbox doesn't do native filtering or sorting. You must supply that.

0
thomas_premAuthor Commented:
Thank you for suggestion.
Actually the listview control the first column have the numeric, in the same row a name of hospital , for example if I type say 1 in my textbox and press enter, it should automatically fill the the name of the hospital or otherwise if I would type name of the hospital few letters in the same text box and it should fill the name of the hospital in the text box.  This is exactly I want it.  Either I would type numeric or hospital name it should fill hospital name.
Thank you and await for further reply.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use a Combo with AutoComplete turned on (it's on by default), but that will search for text matches only. For example, in a combo with these values:

ID  Name
1    Scott
2    Sam
3    John
4    Bill

If my combo is setup with a ColumnCount of 2 and ColumnWidths of 0;1, then when I type "S" I would see Scott and Sam. However, if I type 1 I would see nothing, since my first column is hidden.

As far as a Listbox is concerned, you cannot enter anything into a Listbox, but you could have a separate Textbox where the user could search and then show the results in that listbox. To do that, just set the RowSource property of your Listbox in the AfterUpdate or Change event of your textbox:

Me.YourListbox.RowSource = "SELECT ID, Name FROM SomeTable WHERE [Name] LIKE '" & Me.YourSearchTextbox & "*'"

You could then differentiate between text and numeric values:

If IsNumeric(Me.YourSearchTextbox) Then
  Me.YourListbox.RowSource = "SELECT ID, Name FROM SomeTable WHERE ID=" & Me.YourSearchTextbox
Else
  Me.YourListbox.RowSource = "SELECT ID, Name FROM SomeTable WHERE [Name] LIKE '" & Me.YourSearchTextbox & "*'"
End If

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.