Search within list box

[Vulcan1500]

I have a form with a listbox. This listbox shows all records of a database. With help of this forum I'm able to sort the listbox ascending and decending by clicking the individual fields in the header. Now I have put 6 controls(textboxes) in the footer of the form with a button to find records. My question is now how to handle the strWhere so that at opening all data and after search part of the data is shown

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtYearSurvey) Then
strWhere = strWhere & "([YearSurvey]=" & Me.txtYearSurvey & ") AND "
End If
If Not IsNull(Me.txtYearExecution) Then
strWhere = strWhere & "([YearExecution]=" & Me.txtYearExecution & ") AND "
End If
If Not IsNull(Me.txtProjectNo) Then
strWhere = strWhere & "([ProjectNo]=""" & Me.txtProjectNo & """) AND "
End If
If Not IsNull(Me.txtProjectName) Then
strWhere = strWhere & "([ProjectName]Like ""*" & Me.txtProjectName & "*"") AND "
End If
If Not IsNull(Me.txtArea) Then
strWhere = strWhere & "([Area]=""" & Me.txtArea & """) AND "
End If
If Not IsNull(Me.txtCountry) Then
strWhere = strWhere & "([Country]=""" & Me.txtCountry & """) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.lstTSHDAdvancedSearch.RowSource = strWhere
Me.lstTSHDAdvancedSearch.Requery
End If

End Sub

 

[ShaneMan]

See if either one of these links shed light on what you are trying to do:
http://www.mvps.org/access/forms/frm0007.htm
http://www.mvps.org/access/forms/frm0028.htm

For me, I use SQL to limit listboxes. By looking at your code I can't tell if that is the way your trying to go or not. If you are then, you have built only part of the SQL statement, the WHERE part. You will need to built the SELECT and FROM parts also, at the beginning of your SQL statement and then concatenate the three together. I hope that I'm making sense. Any way, I hope something I have said here will help.

Shane

 

[Vulcan1500]

Search within listbox

Thanks for your reaction Shane. I do not see if one of the 2 links you send helps me with my problem. Maybe I was not clear enough in describing the problem. In my form Search I have a listbox showing 6 fields of each record. At this moment the list is about 1500 records deep. To decrease the number of records I have 6 textboxes, one for each field, and after having filled at least one of these textboxes and clicking the Search button I want to see in the listbox only the records that respond to this search. Then I want to click one of these records and after clicking the Details button I want to see all the fields of this specific record.
Although the file is <100kB I'm not able to attach it to this message. If necessary I can send it directly to you. Hope this gives a better view on my problem.

 

[ShaneMan]

Thanks for your reaction Shane. I do not see if one of the 2 links you send helps me with my problem. Maybe I was not clear enough in describing the problem. In my form Search I have a listbox showing 6 fields of each record. At this moment the list is about 1500 records deep. To decrease the number of records I have 6 textboxes, one for each field, and after having filled at least one of these textboxes and clicking the Search button I want to see in the listbox only the records that respond to this search. Then I want to click one of these records and after clicking the Details button I want to see all the fields of this specific record.
Although the file is <100kB I'm not able to attach it to this message. If necessary I can send it directly to you. Hope this gives a better view on my problem.

I was not sure about the links I provided thats why I suggested the SQL statement. It's the only way that I have every used to filter lstBoxes. You appear to have the WHERE part constructed you just need to build the SELECT and FROM part of the SQL. After you finish that part it should filter your lstBox. Once the lstBox is filtered you will probably need to use something like this link: http://www.mvps.org/access/forms/frm0005.htm
to accomplish setting the current form to the selection within the lstBox.

HTH,
Shane