Build the 'where' sql statement following search form selection

Still not working, should I change the list box to multiple select?
 
Can you post a copy of the database (zip format) with enough records to highlight the issue?
 
cheekybuddha said:
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.
Click to expand...
I have attached the db with some sample data.
Contact list will autoload, just manually open frmSearch, and give it a try to see.
 

Attachments

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
 
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
 
Building the SQL is no longer the problem!

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

Code: 
    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
 

Attachments

KitaYama said:
test this.

Code: 
    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
Click to expand...
Still not filtering.
Am guessing because no selection is being made.
 
Chaga said:
Still not filtering.
Am guessing because no selection is being made.
Click to expand...
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)
 
I didn't see you mentioning multi selecting.
Check this

Code: 
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
 

Attachments

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

Code: 
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
Click to expand...
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? :P
 
Chaga said:
Did I make sense? :p
Click to expand...
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

?
 
Check this
Code: 
    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
 

Attachments

cheekybuddha said:
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

?
Click to expand...
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)
 
KitaYama said:
Check this
Code: 
    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
Click to expand...
YES YES YES!
Thank a million mate!

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

That was the line I needed.
 
Use this.
This one is shorter and easier to understand.

Code: 
    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
 
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.
 
You must log in or register to reply here.

Similar threads

B
Combo Box Search Issue
2
Replies
34
Views
999
MajP
MajP
O
Solved Unbound Form, Two Subforms: Checkbox Selection for Product-Hotel Association
Replies
7
Views
581
Odeh Naber
O
H
Solved Textbox "format" dropdown not working in properties in form design view
Replies
12
Views
320
GPGeorge
GPGeorge
R
Search form works w/32bit not w/64bit Access
Replies
4
Views
398
arnelgp
arnelgp
V
DLookup on a SQL statement
Replies
6
Views
257
VSolano
V

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom