Build the 'where' sql statement following search form selection (1 Viewer)

[Chaga]

Still not working, should I change the list box to multiple select?

 

[jdraw]

Can you post a copy of the database (zip format) with enough records to highlight the issue?

 

[cheekybuddha]

Still not working

What happens (or doesn't happen)?

Remember we can't see your screen! Please describe the steps you take and what you observe.

should I change the list box to multiple select?

Probably not.

 

[Chaga]

What happens (or doesn't happen)?

Remember we can't see your screen! Please describe the steps you take and what you observe.


Probably not.

I have attached the db with some sample data.
Contact list will autoload, just manually open frmSearch, and give it a try to see.

 

[cheekybuddha]

My version of Access is too old to open your forms, so I'll leave it to @jdraw and others to see if they can spot what's wrong

 

[Chaga]

Can you post a copy of the database (zip format) with enough records to highlight the issue?

I posted the db in another reply above

 

[MajP]

There is a lot of code here you may want to leverage to greatly simplify what you are trying to do.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[cheekybuddha]

Building the SQL is no longer the problem!

Just removing the filter from the calling form doesn't seem to work.

 

[KitaYama]

test this.

    Dim SelectedID As Integer
    Dim index As Integer
    If Not CurrentProject.AllForms("Contact List").IsLoaded Then Exit Sub
  
    With lbSearchResults
        If .ItemsSelected.Count > 0 Then
            For index = 0 To .ListCount - 1
                If .Selected(index) Then
                    SelectedID = .Column(0, index)
                End If
            Next
            Forms![Contact List].Filter = "ID = " & SelectedID
            Forms![Contact List].FilterOn = True
        Else
            Forms![Contact List].FilterOn = False
        End If
    End With

 

[Chaga]

test this.

    Dim SelectedID As Integer
    Dim index As Integer
    If Not CurrentProject.AllForms("Contact List").IsLoaded Then Exit Sub

    With lbSearchResults
        If .ItemsSelected.Count > 0 Then
            For index = 0 To .ListCount - 1
                If .Selected(index) Then
                    SelectedID = .Column(0, index)
                End If
            Next
            Forms![Contact List].Filter = "ID = " & SelectedID
            Forms![Contact List].FilterOn = True
        Else
            Forms![Contact List].FilterOn = False
        End If
    End With

Still not filtering.
Am guessing because no selection is being made.

 

[Chaga]

Still not filtering.
Am guessing because no selection is being made.

I made the list to allow multiple selection, and when selecting more than one record, it is returning only one. (The last one to be specific)

 

[KitaYama]

I didn't see you mentioning multi selecting.
Check this

Dim SelectedID As String
    Dim index As Integer
    If Not CurrentProject.AllForms("Contact List").IsLoaded Then Exit Sub
    
    With lbSearchResults
        If .ItemsSelected.Count > 0 Then
            For index = 0 To .ListCount - 1
                If .Selected(index) Then
                    SelectedID = SelectedID & .Column(0, index) & ","
                End If
            Next
            SelectedID = Left(SelectedID, Len(SelectedID) - 1)
            Forms![Contact List].Filter = "ID In (" & SelectedID & ")"
            Forms![Contact List].FilterOn = True
        Else
            Forms![Contact List].FilterOn = False
        End If
    End With

 

[Chaga]

I didn't see you mentioning multi selecting.
Check this

Dim SelectedID As String
    Dim index As Integer
    If Not CurrentProject.AllForms("Contact List").IsLoaded Then Exit Sub
   
    With lbSearchResults
        If .ItemsSelected.Count > 0 Then
            For index = 0 To .ListCount - 1
                If .Selected(index) Then
                    SelectedID = SelectedID & .Column(0, index) & ","
                End If
            Next
            SelectedID = Left(SelectedID, Len(SelectedID) - 1)
            Forms![Contact List].Filter = "ID In (" & SelectedID & ")"
            Forms![Contact List].FilterOn = True
        Else
            Forms![Contact List].FilterOn = False
        End If
    End With

This is working perfectly as long as items are selected.
but... is it possible to have the filter based on the current list result regardless of selected values?
That listbox is not intended to be clicked, just display the search result, and I want to use that same result as a filter for the other form. (it's gonna always be the whole list whether selected or not)
Did I make sense?

 

[cheekybuddha]

Did I make sense?

Ah OK, that's a bit different!

Just to be sure: you want the original form to be filtered with the results from the filter form (more than one if more than one is returned).

If no results returned in the filter form, then don't filter the calling form

?

 

[KitaYama]

Check this

    Dim SelectedID As String
    Dim index As Integer
    If Not CurrentProject.AllForms("Contact List").IsLoaded Then Exit Sub
    
    With lbSearchResults
        For index = 0 To .ListCount - 1
            If .ItemData(index) Then
                SelectedID = SelectedID & .Column(0, index) & ","
            End If
        Next
        If SelectedID = "" Then
            Forms![Contact List].FilterOn = False
        Else
            SelectedID = Left(SelectedID, Len(SelectedID) - 1)
            Forms![Contact List].Filter = "ID In (" & SelectedID & ")"
            Forms![Contact List].FilterOn = True
        End If
    End With

 

[Chaga]

Ah OK, that's a bit different!

Just to be sure: you want the original form to be filtered with the results from the filter form (more than one if more than one is returned).

If no results returned in the filter form, then don't filter the calling form

?

My form "Contact List" opens up on db launch and has all contacts in continuous form.
I will add a button that will open the search form (we worked on), and after doing the search, take the result from the listbox, and use it as filter for "contact list" to display the same records as per the search result.
Contact list has much more info that the listbox can display, so users will select any record they want to edit from it (instead of the listbox)

 

[Chaga]

Check this

    Dim SelectedID As String
    Dim index As Integer
    If Not CurrentProject.AllForms("Contact List").IsLoaded Then Exit Sub
  
    With lbSearchResults
        For index = 0 To .ListCount - 1
            If .ItemData(index) Then
                SelectedID = SelectedID & .Column(0, index) & ","
            End If
        Next
        If SelectedID = "" Then
            Forms![Contact List].FilterOn = False
        Else
            SelectedID = Left(SelectedID, Len(SelectedID) - 1)
            Forms![Contact List].Filter = "ID In (" & SelectedID & ")"
            Forms![Contact List].FilterOn = True
        End If
    End With

YES YES YES!
Thank a million mate!

Forms![Contact List].Filter = "ID In (" & SelectedID & ")"

That was the line I needed.

 

[cheekybuddha]

 

[KitaYama]

Use this.
This one is shorter and easier to understand.

    Dim SelectedID As String
    Dim index As Integer
    If Not CurrentProject.AllForms("Contact List").IsLoaded Then Exit Sub
    
    With lbSearchResults
        For index = 0 To .ListCount - 1
            SelectedID = SelectedID & .Column(0, index) & ","
        Next
        If SelectedID = "" Then
            Forms![Contact List].FilterOn = False
        Else
            SelectedID = Left(SelectedID, Len(SelectedID) - 1)
            Forms![Contact List].Filter = "ID In (" & SelectedID & ")"
            Forms![Contact List].FilterOn = True
        End If
    End With

 

[KitaYama]

I just wanted to add this.

I personally never filter a form. I change the record source
In your case instead of

Forms![Contact List].Filter = "ID In (" & SelectedID & ")"

I would use

Forms![Contact List].RecordSource = " SELECT * FROM [Contacts Extended] WHERE ID In (" & SelectedID & ")"

And never use space in object names.