Buttons to filter form (1 Viewer)

Noel96334

New member
Local time
Today, 21:58
Joined
Jun 12, 2011
Messages
12
Hi,

I have a db, addressbook. In my form (frmAddressbook)
i have a listbox (lstNames) with the first- and lastnames. There
are also buttons to filter the listbox by category (Family, friends,
colleagues).
Sofar everything's working fine.
Now I will make 27 buttons (A-Z & all) to filter the form
by the first character of the Lastname.
I don't need the combination of both filters (category buttons
AND alphabeth).

If I do it the same way like I did for the category buttons,
the alphabeth filters works fine but then the category filters
don't works anymore.

Is there anyone who can help me with this ?
 

Attachments

  • Database1.accdb
    832 KB · Views: 63

GaP42

Active member
Local time
Tomorrow, 07:58
Joined
Apr 27, 2020
Messages
282
An approach I use for multiple filters is to use a filterform proc which is called from the afterupdate of each of my filter controls. In your case with the onclick command of a button.
The filterform procedure builds the string strWhere, which takes the input of each control, and appends the applicable criteria to the strWhere.

As a guide:
Code:
Private Sub cboStatus_AfterUpdate()

Call FilterForm

End Sub

And the filterform procedure is like:
Code:
Private Sub FilterForm()

Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    
Dim dtStart As Date
Dim strWhere As String

If Nz(Me.cboTxStatus, "") <> "" Then
    Select Case cboTxStatus
        Case "ALL"
            strWhere = strWhere    ' no need to include in filter
        Case "No Status"
            strWhere = strWhere & "([TxStatusID] = 0 ) AND "
        Case "Has Status"
            strWhere = strWhere & "([TxStatusID] > 0 ) AND "
    End Select
    End If

    If Nz(Me.cboStatus, "") <> "" Then
    Select Case cboPmtStatus
        Case "All"
            strWhere = strWhere ' no need to include in filter
        Case "Paid"
            strWhere = strWhere & "([IsPd] = True) AND "
        Case "Unpaid"
            strWhere = strWhere & "([IsPd] = False) AND "
    End Select
    End If

' where clause criteria compiled - clean up and
    'Apply filter
    If strWhere <> "" Then
        strWhere = Left(strWhere, Len(strWhere) - 5) 'Remove the extra AND
        Me.Filter = strWhere
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If

You will need to set it up with all your buttons - if this is the way you wnat to go, but a combo of the alphabet may be a more condensed way to get the input on the form.
 

Noel96334

New member
Local time
Today, 21:58
Joined
Jun 12, 2011
Messages
12
Hi,

I have a db, addressbook. In my form (frmAddressbook)
i have a listbox (lstNames) with the first- and lastnames. There
are also buttons to filter the listbox by category (Family, friends,
colleagues).
Sofar everything's working fine.
Now I will make 27 buttons (A-Z & all) to filter the form
by the first character of the Lastname.
I don't need the combination of both filters (category buttons
AND alphabeth).

If I do it the same way like I did for the category buttons,
the alphabeth filters works fine but then the category filters
don't works anymore.

Is there anyone who can help me with this ?
Thanks for the reply, but I'm not so strong with vba.
It's already giving me a lot of research. If you can help me with
one button, I can do the rest. I think my problem is to setup the
query the way, so that both filters can works independenthly of each other.
Greetings.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:58
Joined
Sep 21, 2011
Messages
13,890
Why not have a combo or listbox if you need more than one alpha character?
Much better than that many buttons I would have thought?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:58
Joined
May 21, 2018
Messages
8,418
Since All your btns are nicely named "btnLetter" (btnA, btnB) this can be done with minimum code
Howerver you do not want to do this with a parameterized query, it would be too tedious.

Code:
Public Function FilterByLetter()
  Dim ltr As String
  Dim fltr As String
  Dim strSql As String

  'Your buttons are all of the form 'btnA'
  ltr = Right(ActiveControl.Name, 1)
  strSql = "SELECT AddressID, LastName, FirstName, CategoryID FROM tblAddresses "
  If Me.frameName = 1 Then
    strSql = strSql & " where LastName like '" & ltr & "*'"
  Else
    strSql = strSql & " where FirstName like '" & ltr & "*'"
  End If
  strSql = strSql & " ORDER BY LastName, FirstName"
   Me.lstNames.RowSource = strSql
   Me.lstNames.Requery
End Function

Now simply select all of these buttons in design view. Go to the on click property and type: =FilterByLetter(). I also added an option to select by first name.

The problem is that I have changed the rowsource when you pick a letter. I had to add code to the other events to go back to the original query.
 

Attachments

  • Database1_buttons.accdb
    832 KB · Views: 76
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 20:58
Joined
Sep 21, 2011
Messages
13,890
I cannot see the difference? :(

Code:
If Me.frameName = 1 Then
    strSql = strSql & " where LastName like '" & ltr & "*'"
  Else
    strSql = strSql & " where LastName like '" & ltr & "*'"
  End If
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:58
Joined
May 21, 2018
Messages
8,418
FYI to add the other buttons. Copy the existing button and paste it. Make sure to name it properly and it will automatically work. No additional code is needed.
The trick to make each button work with one function is described here.

Tools to make complex form filters very simple are described here
This makes @GaP42 complex code very simple and streamlined
A current example of that is seen here where I saved the OP 150 lines of code and removed the complex parameterized sql query.

FYI, You could have used a Option Group for your letters instead of individual buttons or a combobox/listbox. With the combobox/listbox you could have maintained the technique you were using.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:58
Joined
May 21, 2018
Messages
8,418
I cannot see the difference?
Thanks. It was correct in the upload.
 

Users who are viewing this thread

Top Bottom