Solved

search form

Posted on 2011-02-16
15
280 Views
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.
example:

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.

or
if i type the EMPID

textbox : 7554

Listbox

john paul           7554

this is what will show in list box.

I hope that you can help me to solve this problem

Thanks.

Czher

 
0
Comment
Question by:Czher
  • 6
  • 6
  • 3
15 Comments
 

Author Comment

by:Czher
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.

Thanks.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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.
0
 

Author Comment

by:Czher
ID: 34905739
i already did this, but everytime i press enter or press the search button, the listbox is blank
0
 
LVL 84
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?
0
 
LVL 11

Expert Comment

by:RgGray3
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.
0
 
LVL 11

Expert Comment

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

Author Comment

by:Czher
ID: 34906506
yes, this is exactly i need man, can you help me?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 11

Expert Comment

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

Author Comment

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

Expert Comment

by:RgGray3
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...

0
 
LVL 11

Expert Comment

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


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

Scott

0
 

Author Comment

by:Czher
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
    Me.Listbox1.Requery
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




0
 
LVL 11

Expert Comment

by:RgGray3
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

0
 

Author Closing Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

11 Experts available now in Live!

Get 1:1 Help Now