Buttons to filter form (1 Viewer)

[Noel96334]

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 ?

 

[GaP42]

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:

Private Sub cboStatus_AfterUpdate()

Call FilterForm

End Sub

And the filterform procedure is like:

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]

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]

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]

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.

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.

 

[Gasman]

I cannot see the difference?

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

 

[MajP]

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.

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

Tools to make complex form filters very simple are described here

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

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.

Solved - Between Dates On Keyword Search

Hi All, I've used the code in this thread for searching my databases quite a bit and find it excellent. https://www.access-programmers.co.uk/forums/threads/dynamically-search-multiple-fields.188663/ I was wondering if anyone is familiar with it, could you tell me is it possible to add a...

www.access-programmers.co.uk

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]

I cannot see the difference?

Thanks. It was correct in the upload.