Solved Filter Combobox on Continious Subform (1 Viewer)

silentwolf

Active member
Local time
Today, 03:52
Joined
Jun 12, 2009
Messages
545
Hi guys,

I am trying to filter a continious subform with a textbox and search button.

Following situation:
Query qryKontakteSortiert
Code:
SELECT tbl_Kontakte.Kont_ID, IIf([Kont_Firma]<>"",[Kont_Firma],[Kont_Vorname] & " " & [Kont_Nachname]) AS KundeName
FROM tbl_Kontakte
ORDER BY IIf([Kont_Firma]<>"",[Kont_Firma],[Kont_Vorname] & " " & [Kont_Nachname]);

As you can see the Contacts are listed either in the Company (Firma) or if they are private People then with FirstName(Kont_Vorname) and Lastname(Kont_Nachname)

The RowSource of the combobox on the subform is "qryKontakteSortiert"

Because it is hard to know how those Contacts are starting with I am trying to find a way to filter with a textbox and a button the combobox.

Code:
Private Sub btnSearch_Click()
    Dim strSQL
            
    strSQL = "SELECT * FROM qryKontakteSortiert WHERE [KundeName]  LIKE '*" & Me!txtSearch.Value & "*'"
    
    Me!cboContacts.RowSource = strSQL
End Sub

Now that works kind of as all comboboxes getting the RowSource and that is of course not really that great.

I could have a routine to clear the strSQ and fill it again but I am not sure it that is even a good way of doing it.. I mean the whole "filter"

Maybe someone can let me know how you would tackle this situation?

As I need or like to implement the same kind of filter for an Item Subform it would be great to have a good way of doing this.

Hope this is understandable what I am trying to do and would be appreciated if someone has a tip for me.

Many thanks

Albert
 

Ranman256

Well-known member
Local time
Today, 06:52
Joined
Apr 9, 2015
Messages
4,337
no need to change the query, just filter:
Code:
sub btnFind_click()
sWhere = "1=1"
if not isnull(cboState) then sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(cboGender) then    sWhere = sWhere & " and [Gender]='" & cboGender & "'"

'then filer
if sWhere = "1=1" then
   me.filterOn = false
else
   me.filter = sWhere
   me.filterOn = true
endif
 

silentwolf

Active member
Local time
Today, 03:52
Joined
Jun 12, 2009
Messages
545
Hi,

thanks for your reply!
I will let you know if I get it to work :)

Cheers
 

silentwolf

Active member
Local time
Today, 03:52
Joined
Jun 12, 2009
Messages
545
Sorry,

but here I get not the result I want it throws an error or a Parameter
Attached the main Form with the subform I like to filter and its controls.

cboContacts I like to filter with what is in the txtSearch so I only get the results I need.

Code:
Private Sub btnSearch_Click()
    Dim swhere As String
    
    swhere = "1=1"
    
    If Not IsNull(txtSearch) Then swhere = swhere & " and [cboContacts]='" & txtSearch & "'"    
    
    If swhere = "1=1" Then
        Me.FilterOn = False
    Else
        Me.Filter = swhere
        Me.FilterOn = True
    
    End If
    
End Sub

Sorry
 

Attachments

  • Form1.jpg
    Form1.jpg
    76.2 KB · Views: 193

Ranman256

Well-known member
Local time
Today, 06:52
Joined
Apr 9, 2015
Messages
4,337
I use this method all the time, is the cbo box in the subform or the master form?
the path will be different for master form:
and [cboContacts]='" & forms!fMasterFrm!txtSearch & "'"

is the combo bound column correct?
 

silentwolf

Active member
Local time
Today, 03:52
Joined
Jun 12, 2009
Messages
545
Hi,
thanks for helping!

The cboContacts is in the subform
SubformName =sfm_KontaktAuftraege

The picture I providet is the main form frm_Auftraege and the controls are in the subform sfm_KontaktAuftraege.

I would need to filter each Dataset in the continious form to be able to limit the cboContacts with txtSearch and the btnSearch.
 

Hello1

Registered User.
Local time
Today, 12:52
Joined
May 17, 2015
Messages
271
How about the event "On Key Down" like the space bar? When the user has the focus on the cbo you could say something like, if he presses the space bar open a continuous pop-up form with all the contacts where you can search for the desired one. Once he found it and selected it on the pop-up form he either does a double click on that record or just clicks on a button "Confirm", you close the pop-up form and write the ID he selected on the pop-up form to the cbo. That's something I use
 

silentwolf

Active member
Local time
Today, 03:52
Joined
Jun 12, 2009
Messages
545
that would also be possible yes but how you populate then the combobox from the other form?

Never done or needet it so not sure.

I just thought it would be nice to just limit the possible entries on the combobox so he could just stay in the same form rather then open another one.

Because he would know what name has to be included in the Contact.
So if he knows that the contact has "Huber" in it he would find or the combobox would display "Eveline Huber" Or "Nice Huber Store" and so on..
And he could stay in the same form.

But I am open for all )

Thanks
 

Hello1

Registered User.
Local time
Today, 12:52
Joined
May 17, 2015
Messages
271
To be honest the idea of having a search field and a button for every record doesn't really look the best to me.

Let me try to explain what I meant before, in more detail.

First of all I would start by creating a new form, lets call it "popKontakte". (I forgot to mention that this form would be a pop-up form. Here I wont explain about it, if needed google it)
The record source of the form would be your first query: qryKontakteSortiert

Now for the design of the form, I will put in the attachment something. On the form I will use English I'm not that good at writing German yet 😁
Now once the user opens the pop-up form the form would display all of the customers (sorry I just realised I used customers instead "contacts" but everything stays the same), he would need to enter a part of the name in the "txtSearch", press Enter and then we would run the next code (so on AfterUpdate):
Code:
Private Sub txtSearch_AfterUpdate()
    If Not IsNull(Me.txtSearch) Then
        Me.Filter = "KundeName Like '*" & Me.txtSearch & "*'"
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
End Sub

So once the user filtered the customers all he needs to do is select one of them and either do a double click or click on the "OK" button (second picture I uploaded). For all of that we would use the next code:
Code:
Private Sub cmdOK_Click()
    Form_DblClick (0)
End Sub

Private Sub Form_DblClick(Cancel As Integer)
    FormControl = Me.Kont_ID
    DoCmd.Close
End Sub

'This code is if the user decides to cancle the action and presses the ESC key you have to make sure that the Key Preview is set to Yes (will put a picture)
If KeyCode = 27 Then    'Check if user pressed the ESC button
      DoCmd.Close
End If

Now all what's left for us to do is put some code under the cboContacts (to make it call the new for we created) of your subform (here we also use the after update event):
Code:
If KeyCode = 32 Then    'Check if the user has pressed the space bar key. Here also make sure Key Preview is set to Yes
        Public FormControl As Control
     
        Set FormControl = Me.cboContacts
        DoCmd.OpenForm "popKontakte"
    End If
 

Attachments

  • KeyPreview.PNG
    KeyPreview.PNG
    28.1 KB · Views: 197
  • popCustomerSelect.PNG
    popCustomerSelect.PNG
    12.5 KB · Views: 193
  • popCustomer.PNG
    popCustomer.PNG
    10.9 KB · Views: 185
Last edited:

silentwolf

Active member
Local time
Today, 03:52
Joined
Jun 12, 2009
Messages
545
Hi,

sorry did take a bit to get back to you just been on the phone for a while gg

Ok I understand.
Many thanks for your help but like to thank everyone of course for each input!

Cheers!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:52
Joined
Feb 19, 2002
Messages
42,970
You need to change the query to get the company name to account for null values.

SELECT tbl_Kontakte.Kont_ID, IIf([Kont_Firma] & ""<>"",[Kont_Firma],[Kont_Vorname] & " " & [Kont_Nachname]) AS KundeName
FROM tbl_Kontakte
ORDER BY IIf([Kont_Firma]<>"",[Kont_Firma],[Kont_Vorname] & " " & [Kont_Nachname]);

Just FYI. Access' choice to set the AllowZeroLengthString property to Yes is really poor. I always change it to no. I prefer to always work with null rather than sometimes null and sometimes ZLS.
 
Last edited:

silentwolf

Active member
Local time
Today, 03:52
Joined
Jun 12, 2009
Messages
545
Hi,
thanks Pat I will change it!

Thanks for pointing it out!

P.S.

I do get an error on that code

Code:
Private Sub Form_DblClick(Cancel As Integer)
    FormControl = Me.Kont_ID
    DoCmd.Close
End Sub

I need to have a varible for it ..
So it is an object varible but how to I need to set it?

Embarressed :oops:
 

silentwolf

Active member
Local time
Today, 03:52
Joined
Jun 12, 2009
Messages
545
Got it.

Code:
Private Sub Form_DblClick(Cancel As Integer)
    Forms![frm_Auftraege]![sfm_KontaktAuftraege].Form![cboContacts] = Me.Kont_ID
    DoCmd.Close
End Sub

Fantastic!

Haven't done that kind of referencing till now.. but got it working :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:52
Joined
Feb 19, 2002
Messages
42,970
PS, You are not actually using partial strings from the combo, so don't use LIKE. You are searching based on the ID field, not the text field. Using LIKE is just going to impede the query engine's ability to optimize the query.
 

silentwolf

Active member
Local time
Today, 03:52
Joined
Jun 12, 2009
Messages
545
Sorry,

PS, You are not actually using partial strings from the combo, so don't use LIKE. You are searching based on the ID field, not the text field. Using LIKE is just going to impede the query engine's ability to optimize the query.
Pat not sure where you mean and why should I not need a LIKE ?

Maybe I just can't see it anymore after beein behind the screen for so long but I am a bit confused o_O
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:52
Joined
Feb 19, 2002
Messages
42,970
Private Sub txtSearch_AfterUpdate()
If Not IsNull(Me.txtSearch) Then
Me.Filter = "KundeName Like '*" & Me.txtSearch & "*'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub
Since you referenced the RowSource query and the first column of the RowSource was the recordID, I assumed that Me!txtSearch.Value
was referring to the combo. I always prefix my combos with cbo but I have no idea what you do. If Me!txtSearch.Value is NOT referring to the combo, then ignore the comment. Looking again, I see why you were having a problem. Once you combine the two different strings correctly into a single list, the problem becomes that you shouldn't be using "KunderName" for the filter, you should be using

Me.Filter = "Kont_ID = " & me.cboSearch (or whatever you named your combo)

because the combo returns the ID of the record you want so you would not filter on the name. In fact, once you combine two fields into one, it makes no sense to filter on one name.
 

Hello1

Registered User.
Local time
Today, 12:52
Joined
May 17, 2015
Messages
271
Since you referenced the RowSource query and the first column of the RowSource was the recordID, I assumed that Me!txtSearch.Value
was referring to the combo. I always prefix my combos with cbo but I have no idea what you do. If Me!txtSearch.Value is NOT referring to the combo, then ignore the comment. Looking again, I see why you were having a problem. Once you combine the two different strings correctly into a single list, the problem becomes that you shouldn't be using "KunderName" for the filter, you should be using

Me.Filter = "Kont_ID = " & me.cboSearch (or whatever you named your combo)

because the combo returns the ID of the record you want so you would not filter on the name. In fact, once you combine two fields into one, it makes no sense to filter on one name.
Hi Pat,
The part of code you quoted I wrote in my example. It has nothing to do with the cbo, I'm applying the filter to the forms record source, which is the query qryKontakteSortiert

silentwolf, did you get it to work from the example I posted or are there more difficulties you are facing?
Regarding the variable FormControl, it's a public one. You can keep it where you keep your other public variables.
 

silentwolf

Active member
Local time
Today, 03:52
Joined
Jun 12, 2009
Messages
545
@Pat Hartman,
I name my comboboxes also with a prefix cbo... )
But thanks for pointing it out and also your code snippet !

Always good to hear from experienced users!

@Hello1,
Yes indeed I did work it out and it all workes as a charm )
Many thanks to you as it was always something I was wondering how to find Data when it is a little more complex or more then a few.

So now I have a good way of doing it and makes it easy to find Data!

Cheers to all!

Much appreciated!!
 

Users who are viewing this thread

Top Bottom