Link to home
Get AccessLog in
Avatar of Czher

asked on

search form

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



Avatar of Czher


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.

Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of Czher


i already did this, but everytime i press enter or press the search button, the listbox is blank
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?
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.
When you push the button, have you inspected the SQL to confirm the resulting SQL
Avatar of Czher


yes, this is exactly i need man, can you help me?
Are you issuing a requery of the listbox?
Applying a filter
or Setting its Data source?
Avatar of Czher


requery of list box and applying filter.......
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...

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

<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.


Avatar of Czher


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

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

Avatar of Czher


although they are arguing, it partially solve my problem