This problem is driving me crazy.:mad:

Hello there,
i am trying to create a search form for records in my access database. The search form will contain text boxes and a multi select list box. The user can enter their search criteria eg. surname, reg num, etc. in the text boxes. The multi select list box allows the user to select multiple counties which they have the option of including in the search. The user should be able to select or omit the criteria as they desire.


I have messed around with the code for the last days but i am unable to intergrate the user selected criteria from a multi select list box.

.
I have been able to fix to the basics like first name and surname search but i cant manage the user selection from the multi select list box of counties.


This in my attempt it's bad i know.

If Len(Me.List0 & vbNullString) Then
For Each Itm In ctl.ItemsSelected
strSQLWhere = "WHERE [county] Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
strSQLWhere = "WHERE [county] = " & Chr$(39) & Me.List0 & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin
End If


Listbox info
The list box is a selection of counties its called list0 its a look up values in my counties table.

Name = List0
Row Source = SELECT [tblCounties].[CountyCode], [tblCounties].[County] FROM tblCounties;
Multi select = simple

I really like the subform the results are displayed in do you think i can make it open in a report format also?

Below is my code minus the failed list box intergration attempt to avoid confusion.

Many thaks once again all the way from Ireland. Kind regards

James

Private Sub cmdPlease_Click()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String


strJoin = " AND "
strSQLHead = "SELECT * FROM tblMemberDetails "

If Len(Me.txtSurname & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = "WHERE [surname] Like " & Chr$(39) & "*" & Me.txtSurname & "*" & Chr$(39)
Else
strSQLWhere = "WHERE [surname] = " & Chr$(39) & Me.txtSurname & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin
End If

If Len(Me.txtFirstName & vbNullString) Then
If (Me.chkLike) Then
strSQLWhere = "WHERE [FirstName] Like " & Chr$(39) & "*" & Me.txtFirstName & "*" & Chr$(39)
Else
strSQLWhere = "WHERE [FirstName] = " & Chr$(39) & Me.txtFirstName & Chr$(39)
End If

strSQLWhere = strSQLWhere & strJoin
End If

If Len(strSQLWhere) Then
strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (Len(strJoin) - 1))
End If

strSQLOrderBy = "ORDER BY "
Select Case Me.fraOrderBy
Case 1
strSQLOrderBy = strSQLOrderBy & "[surname]"
Case 2
strSQLOrderBy = strSQLOrderBy & "[firstName]"
Case 3
strSQLOrderBy = strSQLOrderBy & "[regNumber]"
End Select

strSQL = strSQLHead & strSQLWhere & strSQLOrderBy

I did a search here at AWF in the forum: Sample Databases.

Check out this:
Search Form Example (http://access-programmers.co.uk/forums/showthread.php?t=99353&highlight=listbox)

Great example thanks so much :)

Seems to do all i need ,major thanks once again.

Fingers crossed i can apply it to my database.

Thanks again for the heads up.

I owe you a beer.

Kind regards

Great example thanks so much :)

Seems to do all i need ,major thanks once again.

Fingers crossed i can apply it to my database.

Thanks again for the heads up.

I owe you a beer.

Kind regards

You're welcome!

Glad I could assist.

Major Thanks!!!

Your information really helped me understand what was going on. This problem was driving me nuts. Thank you so much for your time and knowledge.

I am just finished this now but one small thing left to slove


I managed to create a Search form to meet my specification exactly but i have one error left to sort out.

The search feature works great it updates the subform of search results perfectly for all criteria. However i have added a option to printPreview a report based on the selected criteria which worked at first, my latest problem came to light after I added a Order By clause to my function which allows me to order results in the search results subform by Surname,FirstName and reg number.
The subform results part works fine i can order by Surname etc.

My Problem is if i hit the button to preview the report it get an error message

Run Time error 3075

Syntax error (missing operator) in a query expression '''ORDER BY [regNumber]',

My code is below any advice would be hugely apperiacted.


Option Compare Database

Option Explicit



Private Sub btnPreviewReport_Click()



Dim strSQLOrderBy As String

Dim stDocName As String



stDocName = "rptsearchresults1"

DoCmd.OpenReport stDocName, acPreview, WhereCondition:=BuildFilter & strSQLOrderBy



End Sub



Private Sub btnClear_Click()

Dim intIndex As Integer



' Clear all search items

Me.txtFirstName = ""

Me.txtSurname = ""

Me.txtRegNumber = ""



' De-select each item in County List (multiselect list)

For intIndex = 0 To Me.lstCountyCode.ListCount - 1

Me.lstCountyCode.Selected(intIndex) = False

Next



End Sub



Private Sub btnSearch_Click()



' Update the record source

'Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter



' Update the record source

If BuildFilter = "" Then

Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter

Else

Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew WHERE " & BuildFilter

End If



'Requery the subform

Me.sbfrmSearchResults1.Requery





End Sub





Private Function BuildFilter() As Variant

Dim varWhere As Variant

Dim CountyCode As Variant

Dim varItem As Variant

Dim intIndex As Integer



Dim strSQLOrderBy As String



varWhere = Null ' Main filter

CountyCode = Null ' Subfilter used for countyCode



' Check for LIKE First Name

If Me.txtFirstName > "" Then

varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "

End If



' Check for LIKE Last Name

If Me.txtSurname > "" Then

varWhere = varWhere & "[surname] LIKE """ & Me.txtSurname & "*"" AND "

End If

' Check for reg Number

If Me.txtRegNumber > "" Then

varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """ And "

End If





' Check for CountyCode in multiselect list

For Each varItem In Me.lstCountyCode.ItemsSelected

CountyCode = CountyCode & " [tblMemberDetails_CountyCode] = """ & _

Me.lstCountyCode.ItemData(varItem) & """ OR "



Next



'Test to see if we have subfilter for CountyCode...

If IsNull(CountyCode) Then

' do nothing

Else

' strip off last "OR" in the filter

If Right(CountyCode, 4) = " OR " Then

CountyCode = Left(CountyCode, Len(CountyCode) - 4)

End If



'Add some parentheses around the subfilter

varWhere = varWhere & "( " & CountyCode & " )"

End If



'Check if there is a filter to return...

If IsNull(varWhere) Then

varWhere = "''"

Else



' strip off last "AND" in the filter

If Right(varWhere, 5) = " AND " Then

varWhere = Left(varWhere, Len(varWhere) - 5)

End If



End If



strSQLOrderBy = "ORDER BY "

Select Case Me.fraOrderBy

Case 1

strSQLOrderBy = strSQLOrderBy & "[surname]"

Case 2

strSQLOrderBy = strSQLOrderBy & "[firstName]"

Case 3

strSQLOrderBy = strSQLOrderBy & "[regNumber]"

End Select



BuildFilter = varWhere & strSQLOrderBy



End Function


Many Thanks!
Have a Nice weekend
JAMES

this is a debug info from immediate window

'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' OR( [tblMemberDetails_CountyCode] = "c" ) ORDER BY [surname]
DER BY [surname]

Hi Guys and thanks for all the input.

I decided to lose the order by feature so that makes more straight forward.
However i have encountered a new problem.

I successfully integrated a multi select listbox for users to select and search for counties.

On the same page however I would like to integrate a similar multiselect box for nationality. I would like the user to be able to search for nationality with county or individually.

After inserting my nationality list box and adding the code. i can now only search for either
nationality or county and cant perform a combined search.

I think it may be something to do striping off last "OR".

Any help would be greatly appreciated


Many thanks for the lifeline. JAMES

Private Sub btnSearch_Click()

' Update the record source
'Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter

' Update the record source
If BuildFilter = "" Then
Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
Else
Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew WHERE " & BuildFilter
End If

'Requery the subform
Me.sbfrmSearchResults1.Requery
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim CountyCode As Variant
Dim varItem As Variant
Dim intIndex As Integer
Dim NationalityCode As Variant
Dim strSQLOrderBy As String

varWhere = Null ' Main filter
CountyCode = Null ' Subfilter used for colors
NationalityCode = Null
' Check for LIKE First Name

If Me.txtFirstName > "" Then
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
End If

' Check for LIKE Last Name
If Me.txtSurname > "" Then
varWhere = varWhere & "[surname] LIKE """ & Me.txtSurname & "*"" AND "
End If

If Me.txtRegNumber > "" Then
varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """ And "
End If

' Check for Colors in multiselect list
For Each varItem In Me.lstCountyCode.ItemsSelected
CountyCode = CountyCode & " [tblMemberDetails_CountyCode] = """ & _
Me.lstCountyCode.ItemData(varItem) & """ OR "

Next

'Test to see if we have subfilter for colors...
If IsNull(CountyCode) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(CountyCode, 4) = " OR " Then
CountyCode = Left(CountyCode, Len(CountyCode) - 4)
End If

'Add some parentheses around the subfilter
varWhere = varWhere & "( " & CountyCode & " )"
End If

'NationalityCode

' Check for Nationality in multiselect list
For Each varItem In Me.lstNationality.ItemsSelected
NationalityCode = NationalityCode & " [tblmemberdetails.NationalityCode] = """ & _
Me.lstNationality.ItemData(varItem) & """ OR "

Next

'Test to see if we have subfilter for colors...
If IsNull(NationalityCode) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(NationalityCode, 4) = " OR " Then
NationalityCode = Left(NationalityCode, Len(NationalityCode) - 4)
End If

'Add some parentheses around the subfilter
varWhere = varWhere & "( " & NationalityCode & " )"
End If

'Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = "''"
Else

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If

End If
BuildFilter = varWhere
End Function

Hi
thanks for the responce.

I figured is out it was finally with some advice from my friend.

qualCode a numeric value in the table (the data type) so, i don't want quotes around the value.

Thanks so much for all the assistance.

All the best and thanks again.

Regards

JAMES.

Hi James -
I'm currently try to do the same thing with a search form I have created but am having no luck figuring out the code. If you still happen to have the code can you please post what you ended up using?

Thanks!
ZG