Search/Filter ListBox (1 Viewer)

[georg7]

Hi there, I want to filter my listbox via a search.

Private Sub Befehl100_Click()
xx = InputBox("Name eingeben")
Me!Liste85.RowSource = "SELECT tbl_Mitarbeiter.ID, tbl_Mitarbeiter.Nachname, tbl_Mitarbeiter.Vorname, tbl_Gruppe.GrpName, tbl_Mitarbeiter.ID FROM tbl_Gruppe INNER JOIN tbl_Mitarbeiter ON tbl_Gruppe.ID = tbl_Mitarbeiter.GrpID WHERE tbl_Mitarbeiter.Nachname = " & xx & ";"
End Sub

The Code 'works' but every time a write a Name in it I have to enter a Parameter. When I wirte the Name as Parameter it works perfect.

Somebody know how to delete that Parameter 'error'?

 

[bob fitz]

I don't think you have any errors. The "parameter box" is actually an input box that you open in your code
xx = InputBox("Name eingeben")

you assign the name to the variable xx which is then used to filter the comb/listbox by changing its' RowSource

How else would you want provide the name to filter by?

Why do you want to filter it?

 

[georg7]

First my Inputbox Pops up I write something to search and after that a Parameter box Pops up so I'll have to write the Name i want to search two times in boxes.
I want to filter the listbox after the Inputbox Pop up without the Parameter box
The listbox is to select Person.

Attach files: 2.png is my Input box 3.png is the Parameter box

hope doing well after your Hospital residence !!!

 

[arnelgp]

I made a sample a year ago I think.
see if you can tailor it.

click on the listbox and start typing.
double-click the item in listbox to select.

 

[bob fitz]

First my Inputbox Pops up I write something to search and after that a Parameter box Pops up so I'll have to write the Name i want to search two times in boxes.
I want to filter the listbox after the Inputbox Pop up without the Parameter box
The listbox is to select Person.

Attach files: 2.png is my Input box 3.png is the Parameter box

hope doing well after your Hospital residence !!!

Why not filter it on the Double click event. That way you don't have to type the name at all

 

[Gasman]

Or you could use MajP's excellent class.?

Select as you type ListBox

Hi All, I have a listbox (lstJobs) on a form (frmJobStatusList) which contains a large list of jobs. Users can double click on the listbox to open a job form which contains details about the job. Currently when the listbox is selected people can type a character which takes them to the first...

www.access-programmers.co.uk

 

[bob fitz]

The listbox is to select Person.

You can select a name by clicking it. What do you want to do with the selected name

 

[georg7]

The listbox has over 500 data sets so it's hard to find a Name. :/ To a search button would be nice

 

[bob fitz]

hope doing well after your Hospital residence !!!

Yes much better now. Thank you for your kind wishes.

 

[bob fitz]

The listbox has over 500 data sets so it's hard to find a Name. :/ To a search button would be nice

But surely a combo box would find a name as you type it

 

[georg7]

Even if I create a Combo box and want to select one of them a Parameter box Pops up...

Private Sub Kombinationsfeld101_Click()
 If Len(Me.Kombinationsfeld101 & "") > 0 Then
        Me!Liste85.RowSource = "SELECT tbl_Mitarbeiter.ID, tbl_Mitarbeiter.Nachname, tbl_Mitarbeiter.Vorname, tbl_Gruppe.GrpName, tbl_Mitarbeiter.ID FROM tbl_Gruppe INNER JOIN tbl_Mitarbeiter ON tbl_Gruppe.ID = tbl_Mitarbeiter.GrpID WHERE Nachname = " & Me.Kombinationsfeld101 & " ORDER BY tbl_Mitarbeiter.Nachname;"
    Else
        Me!Liste85.RowSource = "SELECT tbl_Mitarbeiter.ID, tbl_Mitarbeiter.Nachname, tbl_Mitarbeiter.Vorname, tbl_Gruppe.GrpName, tbl_Mitarbeiter.ID FROM tbl_Gruppe INNER JOIN tbl_Mitarbeiter ORDER BY tbl_Mitarbeiter.Nachname;"
    End If
    Me!Liste85.Requery
End Sub

 

[bob fitz]

Even if I create a Combo box and want to select one of them a Parameter box Pops up...

Private Sub Kombinationsfeld101_Click()
If Len(Me.Kombinationsfeld101 & "") > 0 Then
        Me!Liste85.RowSource = "SELECT tbl_Mitarbeiter.ID, tbl_Mitarbeiter.Nachname, tbl_Mitarbeiter.Vorname, tbl_Gruppe.GrpName, tbl_Mitarbeiter.ID FROM tbl_Gruppe INNER JOIN tbl_Mitarbeiter ON tbl_Gruppe.ID = tbl_Mitarbeiter.GrpID WHERE Nachname = " & Me.Kombinationsfeld101 & " ORDER BY tbl_Mitarbeiter.Nachname;"
    Else
        Me!Liste85.RowSource = "SELECT tbl_Mitarbeiter.ID, tbl_Mitarbeiter.Nachname, tbl_Mitarbeiter.Vorname, tbl_Gruppe.GrpName, tbl_Mitarbeiter.ID FROM tbl_Gruppe INNER JOIN tbl_Mitarbeiter ORDER BY tbl_Mitarbeiter.Nachname;"
    End If
    Me!Liste85.Requery
End Sub

The point I was trying to make was that with a combo box you don't need a button or code.
As you type the name in the combo box it auto fills

 

[Gasman]

Even if I create a Combo box and want to select one of them a Parameter box Pops up...

Private Sub Kombinationsfeld101_Click()
If Len(Me.Kombinationsfeld101 & "") > 0 Then
        Me!Liste85.RowSource = "SELECT tbl_Mitarbeiter.ID, tbl_Mitarbeiter.Nachname, tbl_Mitarbeiter.Vorname, tbl_Gruppe.GrpName, tbl_Mitarbeiter.ID FROM tbl_Gruppe INNER JOIN tbl_Mitarbeiter ON tbl_Gruppe.ID = tbl_Mitarbeiter.GrpID WHERE Nachname = " & Me.Kombinationsfeld101 & " ORDER BY tbl_Mitarbeiter.Nachname;"
    Else
        Me!Liste85.RowSource = "SELECT tbl_Mitarbeiter.ID, tbl_Mitarbeiter.Nachname, tbl_Mitarbeiter.Vorname, tbl_Gruppe.GrpName, tbl_Mitarbeiter.ID FROM tbl_Gruppe INNER JOIN tbl_Mitarbeiter ORDER BY tbl_Mitarbeiter.Nachname;"
    End If
    Me!Liste85.Requery
End Sub

If a parameter box pops up, you have a typing error? and Access cannot find the field specified?
As Bob mentions, if you have a Combo, it automatically adjusts as you type?
I've used one with over 2000 records without any issues?

 

[bob fitz]

I've used one with over 2000 records without any issues?

Yes and I've used one with more than 5000 name and address records over a wired network without any problems

 

[Isaac]

@georg7
What happens if you change your code to this:

Private Sub Befehl100_Click()
Dim xx as String
xx = InputBox("Name eingeben")
Me!Liste85.RowSource = "SELECT tbl_Mitarbeiter.ID, tbl_Mitarbeiter.Nachname, tbl_Mitarbeiter.Vorname, tbl_Gruppe.GrpName, tbl_Mitarbeiter.ID FROM tbl_Gruppe INNER JOIN tbl_Mitarbeiter ON tbl_Gruppe.ID = tbl_Mitarbeiter.GrpID WHERE tbl_Mitarbeiter.Nachname = " & xx & ";"
End Sub

or

Private Sub Befehl100_Click()
Dim xx as String
xx = InputBox("Name eingeben")
Me!Liste85.RowSource = "SELECT tbl_Mitarbeiter.ID, tbl_Mitarbeiter.Nachname, tbl_Mitarbeiter.Vorname, tbl_Gruppe.GrpName, tbl_Mitarbeiter.ID FROM tbl_Gruppe INNER JOIN tbl_Mitarbeiter ON tbl_Gruppe.ID = tbl_Mitarbeiter.GrpID WHERE tbl_Mitarbeiter.Nachname = '" & xx & "'"
End Sub

Copying/pasting either of those directly does it make it work?