Solved

MSACCESS

Posted on 2009-04-12
6
348 Views
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
0
Comment
Question by:thomas_prem
  • 3
  • 2
6 Comments
 
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?
0
 

Author Comment

by:thomas_prem
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.  
0
 
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.

0
 

Author Comment

by:thomas_prem
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.
0
 
LVL 84

Accepted Solution

by:
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
Else
  Me.YourListbox.RowSource = "SELECT ID, Name FROM SomeTable WHERE [Name] LIKE '" & Me.YourSearchTextbox & "*'"
End If

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now