Need help on Search Form using textbox and multiselect listbox

[melissagreen]

Hi all. I have created a multi field search form that have 2 textbox and 2 multiselect listbox(extended). How to make the search form query correctly? Below are the details. Thanks

Form
frmSearchForm

2 textbox and 2 multiselect listbox
Textbox 1 > txtFirstName
Textbox 2 > txtLastName
Listbox 1 > lboSports
Listbox 2 > lboSchool

Query
Query > qrySearchForm

Table
tblStudent with field FirstName, LastName, Sports, School

Inside my qrySearchForm, in the field FirstName and LastName criteria.
I've input this code

LIKE "*" & [Forms]![frmSearchForm]![txtFirstName] & "*"

same goes for LastName

LIKE "*" & [Forms]![frmSearchForm]![txtLastName] & "*"

I also have a button that run the query qrySearchForm

With this.. how am I going to make the query run successfully with multiselect listbox ? I understand that there are a lot of examples of the vba code for multiselect but that is only for multiselect alone and not like my search form that combine textbox and multiselect listbox

I would appreciate if you can guide me on how to achieve this result correctly. I'm a totally beginner. e.g What is the code..where to put the code. Thanks a million guys

 

[Gasman]

I would have thought you would need to build the criteria from the selected items.?

This post http://access.mvps.org/access/forms/frm0007.htm seems to agree?

Located from https://www.google.co.uk/search?q=a...rome.0.57j60.20448j0&sourceid=chrome&ie=UTF-8

 

[melissagreen]

Hi Gasman..Yah I saw this page before though. The thing is.. this code is for a search form that consist only multiselect listbox. Now I have textbox as well. How am I going to incorporate all in?
Thanks

 

[Gasman]

You would have to write code to build the select statement. It all depends on what gets selected. You might not have any first or last name, just listbox items, so would need to code for all eventualities.

So you would check your textboxes and build the sql where statement, then check the listitems selected and extend the string with whatever is selected.

I expect what you are looking for is something along the lines of

Select * from tablename where firstname = "Paul" OR lastname = "Wilson" OR fieldlist = "Bread" OR fieldlist = "Flour"

where fieldlist is where you are looking for the listbox items. I have no idea what your listbox contains.

If I was building such a complicated string I would create the query in design view and then replace the parameters with the field values.

Effectively you need to combine your code for the text boxes and that code for the listbox. That code shows you how to get all the items and build the criteria.

 

[shoji]

Hi Melissa,

Before I get to your question, I wonder how this search form works. For example, are the text boxes for Firsname and Lastname to identify a particular individual or to find how many, say, "Jane"s are in such-and-such sports? If it is the former, have you considered creating the index query for all the students? With this index, identifying a student will be much easier. With Access combobox with this index as the row source, you start typing a name and it will immediately pick all the names that fit whatever you typed.
Anyway, this is one thought I wanted to mention.

Secondly, I see a field for "Sports", but does this field include always one sport name or multiple sport names, like "tennis, field hockey, swimming"? If the latter, the query would be considerably more complicated. (The canonical database rule says you should create a separate table for this type of fields, but that is a different story...) Is it OR search or AND search?

Now, you will need pretty complex codes to achieve what you want to do. The following codes are not complete, but if this looks too intimidating to you, you should probably look for a different way to accomplish your goal.

You can create a "Search" button on the form and create a Click Event and write the following codes. The search result will pop up as a query list.

Private Sub cmdSearch_Click()
Dim strSQL As String
Dim strWHERE As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

    strWHERE =""
    If Nz(txtFirstName) <> "" Then
      strWHERE = strWHERE & " AND Instr(1, [FirstName], '" & txtFirstName & "') > 0"
    End If

    If Nz(txtLastName) <> "" Then
      strWHERE = strWHERE & " AND Instr(1, [LastName], '" & txtLastName & "') > 0"
    End If

    strSports = GetList(lboSports, "Sports")
    If strSports <> "" Then
      strWHERE = strWHERE & " AND (" & strSports & ")"
    End If

    strSchool = GetList(lboSchool, "School")
    If strSchool <> "" Then
      strWHERE = strWHERE & " AND (" & strSchool & ")"
    End If

    If strWHERE <> "" Then
        strWHERE = Mid(strWHERE, 6)
    End If

    strSQL = "SELECT School, LastName, FirstName, Sports FROM tblStudent"
    If strWHERE <> "" Then
      strSQL = strSQL & " WHERE " & strWHERE & " ORDER BY School, LastName;"
    End If

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qrySearchForm”)
    qdf.SQL = strSQL

    DoCmd.OpenQuery "qrySearchForm"

End Sub

Private Function GetList(lstBox As ListBox, strTargetField As String) As String
' This function will collect all the selected items from the listbox and make
' the WHERE clause.

Dim varItem As Variant
Dim strTemp As String

    strTemp = ""
    For Each varItem In lstBox.ItemsSelected
         strTemp = strTemp & " AND Instr(1, [" & strTargetField & "], '" & _
             ctl.ItemData(varItem) & "') > 0"
    Next varItem
    If strTemp <> "" Then
        strTemp = Mid(strTemp, 6)
    End If

    GetList = strTemp

End Function

 

[Gasman]

Have a look at the code for this search form

http://allenbrowne.com/ser-62.html

I know it does not use a listbox, just combo boxes, but again should show you how to build and execute the query statement.

I think the way you are trying to do it with hardcoded parameters in your query will not work, that method is for more simple criteria which remains the same.?

I may be proved wrong by the experts, but if I am given a way of doing it that works, then I'd be happy with that way. making it more efficient can come later as I get more experienced.

 

[shoji]

Hi Gasman,

Your points are well taken. If I were Melissa, I would reconsider the search style. But I would like to know what this search is trying to do first.

As I asked in my previous message, "Sports" field may be a problem if it contains more than one sport. If possible, I would re-design tables and create 2 new tables, tblSport and tblStudentSport:

tblSport
SportID
SportName

tblStudentSport
StudentID
SportID

They should be linked to tblStudent. However, it may be too late to redesign tblStudent.

Anyway, Melissa, good luck and I will be happy to follow up if you want.

Shoji

 

[melissagreen]

Hi Guys...let me elaborate further..

I have 2 additional tables and 1 more form.

tblSports
Badminton
Baseball
Bowling
Canoeing
Rugby
Soccer
Tennis

tblSchool
All Blacks School
All Star School
Liverpool Sports School
International Sports School
Saint Jose Sports School

frmStudentForm
FirstName > textbox
LastName > textbox
Sports > combobox > Row Source > tblSports
School > combobox > Row Source > tblSchool

When I input the details in frmStudentForm, data will go to tblStudent

Example of tblStudent
tblStudent
ID FirstName LastName Sports School
1 Joe Myung Soccer Liverpool Sports School
2 Surveep Latroski Rugby All Blacks School
3 Marianne Hendricks Badminton Saint Jose Sports School
4 Mary Kay Tennis All Star School
5 Harine Myung Soccer All Star School
6 Joe Moloco Badminton All Star School

Form
frmSearchForm

2 textbox and 2 multiselect listbox
Textbox 1 > txtFirstName
Textbox 2 > txtLastName
Listbox 1 > lboSports
Listbox 2 > lboSchool

So my search will be like.
If I were to key in Jo in Firstname, the results will be as follows:
1 Joe Myung Soccer Liverpool Sports School
6 Joe Moloco Badminton All Star School

If I were to key in Myung in Lastname, the results will be as follows:
1 Joe Myung Soccer Liverpool Sports School
5 Harine Myung Soccer All Star School

If I were to choose in Soccer and Tennis in Sports, the results will be as follows:
1 Joe Myung Soccer Liverpool Sports School
4 Mary Kay Tennis All Star School
5 Harine Myung Soccer All Star School

If I were to choose in Soccer and Tennis in Sports and All Star School the results will be as follows:
4 Mary Kay Tennis All Star School
5 Harine Myung Soccer All Star School

Hope you guys can help me with the codes. please...
i really dont what to do now.. :banghead:

 

[shoji]

Hi Melissa,

This is much better.

The codes I suggest below are basically the same as the first, but the difference is that sports and schools filters are OR search, therefore the GetList function is modified accordingly.

You create a button "Search" and call it "cmdSearch". From the property sheet of the button, choose "On Click" and select "Event Procedure" which will take you to the module part of the form. Write the codes below.
What it does is to get all the search inputs and make an appropriate SQL statement. Replace the query, qrySearchForm, with the result of the SQL statement and open the query. If the result is displayed in a form, you have to open the form instead.

I would be happy to explain further if you need it. I have not tested the codes, so don't be surprized if you find bugs.

Shoji

Private Sub cmdSearch_Click()
Dim strSQL As String
Dim strWHERE As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

    strWHERE =""
    If Nz(txtFirstName) <> "" Then
      strWHERE = strWHERE & " AND Instr(1, [FirstName], '" & txtFirstName & "') > 0"
    End If

    If Nz(txtLastName) <> "" Then
      strWHERE = strWHERE & " AND Instr(1, [LastName], '" & txtLastName & "') > 0"
    End If

    strSports = GetList(lboSports, "Sports")
    If strSports <> "" Then
      strWHERE = strWHERE & " AND (" & strSports & ")"
    End If

    strSchool = GetList(lboSchool, "School")
    If strSchool <> "" Then
      strWHERE = strWHERE & " AND (" & strSchool & ")"
    End If

    If strWHERE <> "" Then
        strWHERE = Mid(strWHERE, 6)
    End If

    strSQL = "SELECT School, LastName, FirstName, Sports FROM tblStudent"
    If strWHERE <> "" Then
      strSQL = strSQL & " WHERE " & strWHERE & " ORDER BY School, LastName;"
    End If

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qrySearchForm”)
    qdf.SQL = strSQL

    DoCmd.OpenQuery "qrySearchForm"

End Sub

Private Function GetList(lstBox As ListBox, strTargetField As String) As String
' This function will collect all the selected items from the listbox and make
' the WHERE clause.

Dim varItem As Variant
Dim strTemp As String

    strTemp = ""
    For Each varItem In lstBox.ItemsSelected
         strTemp = strTemp & " OR Instr(1, [" & strTargetField & "], '" & _
             ctl.ItemData(varItem) & "') > 0"
    Next varItem
    If strTemp <> "" Then
        strTemp = Mid(strTemp, 5)
    End If

    GetList = strTemp

End Function

 

[melissagreen]

Hi Shoji..Thanks for the code.. but now I've got an error msg.

Run-time error '424'
Object required

Pointing to this lines of codes

strTemp = strTemp & " OR Instr(1, [" & strTargetField & "], '" & _
             ctl.ItemData(varItem) & "') > 0"

What seems to cause the issue.. I'm unable to figure out the problem. Thanks again for helping

 

[shoji]

Oops. "ctl" should be replaced with "lstBox".

    strTemp = strTemp & " OR Instr(1, [" & strTargetField & "], '" & _
             [COLOR="Red"]lstBox[/COLOR].ItemData(varItem) & "') > 0"

 

[melissagreen]

Well now there isn't any error but after trying out ....

1) choosing the multiselect eg.
a)choosing 2 sports..no result...
b)choosing 2 school..no results

2) there are results if i were to key in firstname or lastname though...

what do you think cause this empty results?

 

[shoji]

What was I thinking?!! When you elaborated the list box, I should have changed the code for the function. Here is the adjusted code:

Private Function GetList(lstBox As ListBox, strTargetField As String) As String
' This function will collect all the selected items from the listbox and make
' the WHERE clause.

Dim varItem As Variant
Dim strTemp As String

    strTemp = ""
    For Each varItem In lstBox.ItemsSelected
         strTemp = strTemp & " OR [" & strTargetField & "] = '" & _
               lstBox.ItemData(varItem) & "'"
    Next varItem
    If strTemp <> "" Then
        strTemp = Mid(strTemp, 5)
    End If

    GetList = strTemp

End Function

Let me know how this goes.

BTW, any sport's name or school name does not contain an apostrophe ('), right? If it does, we will need one more adjustment.

Shoji

 

[shoji]

One more thing: Would you insert a line like:

    strSports = GetList(lboSports, "Sports")
[COLOR="Red"]
    MsgBox strSports
[/COLOR]

    If strSports <> "" Then
      strWHERE = strWHERE & " AND (" & strSports & ")"
    End If

This is a test to check what is the result of GetList function. You can delete this line if the program works. If not, let me know what it shows.

 

[melissagreen]

Thanks shoji but still..

I chose 2 sports and this shows but still no result.

[Sports]='1' OR [Sports]='6'

I chose 3 sports and this shows but still no result.

[Sports]='1' OR [Sports]='6' OR [Sports]='7'

FirstName and LastName are still showing results.

Below are the full codes for reference.

Private Sub cmdSearch_Click()
Dim strSQL As String
Dim strWHERE As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

    strWHERE = ""
    If Nz(txtFirstName) <> "" Then
      strWHERE = strWHERE & " AND Instr(1, [FirstName], '" & txtFirstName & "') > 0"
    End If

    If Nz(txtLastName) <> "" Then
      strWHERE = strWHERE & " AND Instr(1, [LastName], '" & txtLastName & "') > 0"
    End If

    strSports = GetList(lboSports, "Sports")
    
    MsgBox strSports
    
    If strSports <> "" Then
      strWHERE = strWHERE & " AND (" & strSports & ")"
    End If

    strSchool = GetList(lboSchool, "School")
    
    'MsgBox strSchool
    
    If strSchool <> "" Then
      strWHERE = strWHERE & " AND (" & strSchool & ")"
    End If

    If strWHERE <> "" Then
        strWHERE = Mid(strWHERE, 6)
    End If

    strSQL = "SELECT School, LastName, FirstName, Sports FROM tblStudent"
    If strWHERE <> "" Then
      strSQL = strSQL & " WHERE " & strWHERE & " ORDER BY School, LastName;"
    End If

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qrySearchForm")
    qdf.SQL = strSQL

    DoCmd.OpenQuery "qrySearchForm"

End Sub

Private Function GetList(lstBox As ListBox, strTargetField As String) As String
' This function will collect all the selected items from the listbox and make
' the WHERE clause.

Dim varItem As Variant
Dim strTemp As String

    strTemp = ""
    For Each varItem In lstBox.ItemsSelected
         strTemp = strTemp & " OR [" & strTargetField & "] = '" & _
               lstBox.ItemData(varItem) & "'"
    Next varItem
    If strTemp <> "" Then
        strTemp = Mid(strTemp, 5)
    End If

    GetList = strTemp

End Function

 

[shoji]

Hi Melissa,

The reason why it is not working is because GetList function is picking the Sport ID number instead of Sport Name.

First, does the field "Sports" in the tblStudent contain sport names like "Baseball" rather than SportID?

Secondly, does the listbox's "Column Widths" start with 0? Like

0";1.2"; ...

My guess is you can correct this problem by changing the listbox's "Bound Column" from 1 to 2. If the Bound Column is 1, it uses the first column, i.e., SportID as the value of the listbox. So, change it to the column that has actual sport names, i.e., I guess, 2.

Similarly with School listbox.

Let's see if this solves the problem.

 

[Gasman]

Shoji,

I admire your perseverance, but if using the Sport ID, should the values be in quotes?

This is for my benefit.

TIA

 

[shoji]

Hi Gasman and Melissa,

We want the GetList function to return a string that should look like

[Sports] = 'Baseball' OR [Sports] = 'Soccer'

But the result Melissa reported is like

[Sports] = '1' OR [Sports] = '6'

This means that the listbox's value is a number, maybe SportID. This is not good because we cannot use SportID for filtering Sports field on tblStudent as it is a text field, not a number field.

If Sorts listbox's 2nd item is a sport name, you can simply change the following line in the function

         strTemp = strTemp & " OR [" & strTargetField & "] = '" & _
               lstBox.ItemData(varItem) & "'"

to

         strTemp = strTemp & " OR [" & strTargetField & "] = '" & _
               [COLOR="Red"]lstBox.Column(1, varItem)[/COLOR] & "'"

If this does not solve the problem, the information I need from you, Melissa, is

1. the field list of tblSports
2. "Sports" listbox's properties: Column Count, Column Widths, Row Source, and Bound Column.

To be continued.

Shoji

 

[melissagreen]

Hi Shoji...By changing the bound column from 1 to 2. Yes it works!! Thanks a million. Thank you so much

 

[shoji]

Finally! Great.