Solved

MSACCESS

Posted on 2009-04-12
6
354 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
[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
  • 2
6 Comments
 
LVL 85
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 85
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 85

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

623 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