Posted on 2009-04-12
Last Modified: 2013-11-28
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
Question by:thomas_prem
  • 3
  • 2
LVL 84
ID: 24125897
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?

Author Comment

ID: 33104395
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.  
LVL 84
ID: 33105130
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.


Author Comment

ID: 33135079
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.
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 33136569
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
  Me.YourListbox.RowSource = "SELECT ID, Name FROM SomeTable WHERE [Name] LIKE '" & Me.YourSearchTextbox & "*'"
End If


Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 - query 23 59
A Function to parse a text string 4 34
Attachment field in SQL 3 28
unable to create table-based data macro in MS ACCESS 2013 11 24
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

776 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