Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


search form

Posted on 2011-02-16
Medium Priority
Last Modified: 2013-11-28
i have a search form named SEARCHFRM with a text box named search and list box named search1 where it is showing the fields 'NAME', 'EMPID', 'POSITION'.
what i want to do with this form is when i type 'name' or 'empid'  on the text box, a list of name starts with the the letters or words or numbers i typed in text box will show in listbox if its available.

Text Box : john (this is the word i want to search)

List Box

 john X              1754
Billie john           2576
john paul           7554
arnold john ed   8951

All names with john are shown in the list box.

if i type the EMPID

textbox : 7554


john paul           7554

this is what will show in list box.

I hope that you can help me to solve this problem



Question by:Czher
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
  • 6
  • 6
  • 3

Author Comment

ID: 34905508
i forgot to mentioned that if i select and click a name which is shown in the list box a another form named EMPRECFRM will open showing the information related to the name i select.

LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 total points
ID: 34905552
You can set the RowSource of your Listbox to show your values like this:

SELECT [Name], [EmpID], [Position] FROM YourTable WHERE [Name] LIKE '*" & Me.YourSearchBox & "*'"

You can open your form like this:

DoCmd.OpenForm "EMPRECFRM", , , "EmpID=" & Me.YourListbox.Columns(1)

Assumimg that your form has a field named "EmpID", this will open the form to the record that is associated with the EmpID of the selected row in the listbox.

Author Comment

ID: 34905739
i already did this, but everytime i press enter or press the search button, the listbox is blank
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

LVL 85
ID: 34906443
If you've constructed your SQL correctly, then the only reason your listbox would be blank is if the SQL returned no records.

Can you post the actual code you're using to fill the listbox?
LVL 11

Expert Comment

ID: 34906486
I employ a process similar to what you are describing for a Student Mgt application...

We have thousands of records and need to quickly find students.
I allow a quick search requiring a minimum of 3 characters before it allows you to search (search button is hidden and after 3 characters it becomes visible)

I allow a flexable search from the one field

•      You start typing and the first characters are used to search the last name
•      If the user enters a space, anything after the space is used to search the first name
•      OR if the user enters numeric information the form knows to check the Student ID field once the data is 5 characters in length

If this is what you are looking for... be prepared to write code.. it gets involved

You basically create a form with a text box for the user to enter the search criteria
•      CBF to monitor each character as it is entered
o      Do I have 3 characters
¿      YES show the search button
o      Is the data numeric
¿      YES, Is it 5 characters long
¿      Yes... Determine if we have a matching Student ID
¿      Yes... Open the Student Form)

A button to do the search
•      CBF to analyze the data
o      Is there a space in the data
¿      YES I know I have a first name somewhere in the data
¿      NO  I only have Last name info to match
¿      If there is a space Split the data into it’s pieces to be used in the criteria of a subforms data source
¿      Note If the first character is a space I am only searching on the first name

I BUILD the where clause for the subform based upon the this criteria, set the subforms record source make the subform visible.

The subform has buttons on each record to select the record for review
The button opens a form that is filtered to the single student selected.

NOT a process for the faint of heart

If this is what you want to do…  the first step is to outline the process first
You can’t get anywhere in Disney World without a map.
LVL 11

Expert Comment

ID: 34906500
When you push the button, have you inspected the SQL to confirm the resulting SQL

Author Comment

ID: 34906506
yes, this is exactly i need man, can you help me?
LVL 11

Expert Comment

ID: 34906523
Are you issuing a requery of the listbox?
Applying a filter
or Setting its Data source?

Author Comment

ID: 34906617
requery of list box and applying filter.......
LVL 11

Expert Comment

ID: 34906620
Start with a CLEAR outline of the process as shown above
Don't gloss over anything...
What do you want to accept
What is the structure of the data
Is the name in 1 field (Name) or 2 (FirstName, LastName)

The quick process is
Main Form
Has a text box for search data entry, a button to initiate the search.  I USE a subform instead of a list box...  (personal preference as I feel it allows me more flexability, tools and control)

The subform has the BASIC information that allows me to select the right record. (we have DOZENS of "Amit Patel's"  you may have a dozen John Smith's)
 A button to open the desired form

In My Case My "Student Form" has a record source that pulls 1 and only one record.  I do not like giving my users the ability to scroll or refilter the student form once they have a student (Yes, I am a control FREAK)

The record source of this form uses a function to GRAB the Student ID out of memory,  GetStudentID()

When I push the button on the grid subform that is on the desired record I set a global var with the Student ID     glngStudentID = Me.StudentID

When the form opens, the query opens the desired record.

Just like building a house...  Start with the Blueprint, Design the components and pull it together...

LVL 11

Expert Comment

ID: 34906648
What does your DataSource/SQL/Query/Filter for the list box look like...

LVL 85
ID: 34907150
<What does your DataSource/SQL/Query/Filter for the list box look like...>

Which is back to exactly the question I asked originally, and the author has never supplied.

RgGray3: Are you aware of the basic protocols of EE? You've been a member here for quite some time, but not overly until recently it seems. If you jump into a question where other Experts are posting, and you then begin to ask (basically) the same questions, or give the same suggestions, it's common to recognize the prior postings of those Experts. Otherwise, it just looks like you're grubbing for points. There's no policy that says you can't do that, but it's generally considered "bad form".

If you have something unique and different to offer, then by all means please do so (your first posting is a good example of that). But please be aware of the efforts of other Experts.

I'll leave this one to you and the author.



Author Comment

ID: 34913281
that's what i'm asking for a help because i can't figure out what code i need to put on the listbox after search, and what code i need to put in the search button to filter and give me only the names or empid that is similar to what i typed.

i tried to do like this on my search form but its giving me error message:

Option Compare Database
Dim strCallingForm As String

Private Sub cmdSearch_Click() '<- Search button
    Dim strSQL As String
    strSQL = "SELECT empID, Name FROM [002 Resources - All] WHERE Name LIKE '*" & Me.txtSearchName.Value & "*"
    Me.Listbox1.RowSource = strSQL
End Sub

Private Sub cmdSelect_Click()
    Dim rowSelected As Integer
    rowSelected = Me.Listbox1.ItemsSelected.Item(0)
    MsgBox Me.Listbox1.Column(0, rowSelected) & " " & Me.Listbox1.Column(3, rowSelected)
End Sub

Private Sub Form_Open(Cancel As Integer)
    strCallingForm = Me.OpenArgs
End Sub

Private Sub Listbox1_DblClick(Cancel As Integer)
    Dim rowSelected As Integer
    Dim EMPID As Integer
    rowSelected = Me.Listbox1.ItemsSelected(0)
    EMPID = Me.Listbox1.Column(0, rowSelected)
    'MsgBox Me.Listbox1.Column(0, rowSelected) & " " & Me.Listbox1.Column(1, rowSelected)
    gotofoundrecord (EMPID)              

End Sub

Private Sub gotoFoundRecord(EMPID As Integer)
    Dim strSearchName As String

    If strCallingForm = "EMP_Master_FRM" Then
        Forms!EMP_Master_FRM.Recordset.FindFirst "ID=" & Empid
        If Forms!EMP_Master_FRM.Recordset.NoMatch Then
            MsgBox "No Match"
        End If
    End If

End Sub

LVL 11

Expert Comment

ID: 34916510
OK  so it looks like you have all of your controls and managment in place and all you need is the logic to switch between Name and ID#

That is done in the On Change event of the text box
It will allow you to test the value with each keystroke

See the basic logic in the code snippet
1) Test for length and IsNumeric
2) Determine if you have a matching record

If you find a match short circut the process and gotofoundrecord (CInt(Me.txtSearchName))

You may want to have an else in the event that the value is numeric and no match is found...  Msgbox "No employee found with that ID"
If Len(Me.txtSearchName) = 4 And IsNumeric(Me.txtSearchName) Then
    If DCount("*", "002 Resources - All", "[EMPID] = " & Me.txtSearchName) Then

Open in new window


Author Closing Comment

ID: 34931472
although they are arguing, it partially solve my problem

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

597 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