Solved filter \ sort in continuous form (1 Viewer)

[TipsyWolf]

hello everyone here

do u have any form filter \ sort templates ?
i have a continuous form and i would like filter some one the fields by drop down list (combo-box).
looking for a simple solution in google as well.

 

[Gasman]

Have you tried this?

 

[theDBguy]

Or this one...

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk

 

[TipsyWolf]

Or this one...

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk

looks awesome, but i can't figure it out how to implement in my current db. i copied 3 modules, copied vba code and nothing works, cause i prob. should change something there.

i'll look youtube some step-by-step solutions.

 

[Gasman]

You need to set the names correctly in the form load?

 

[TipsyWolf]

You need to set the names correctly in the form load?

probably, but i dont get where i should change it
i have 3 modules and couldn't find where to change form name so i can change it so it loads correct form.
anyway, it might be just not my level. im new to access.

 

[Gasman]

looks awesome, but i can't figure it out how to implement in my current db. i copied 3 modules, copied vba code and nothing works, cause i prob. should change something there.

i'll look youtube some step-by-step solutions.

As no-one had created a video for this particular code, that would be a waste of time.
Allen Browne has a lot of snippets, and one is a search form.

Microsoft Access help, tutorials, examples

Free help and tutorials for Microsoft Access. Tips for beginners and database developers. Downloadable examples and utilities. Flaws, bugs, traps, code, design, queries, forms, reports, version and upgrade information.

allenbrowne.com

 

[TipsyWolf]

As no-one had created a video for this particular code, that would be a waste of time.
Allen Browne has a lot of snippets, and one is a search form.

Microsoft Access help, tutorials, examples

Free help and tutorials for Microsoft Access. Tips for beginners and database developers. Downloadable examples and utilities. Flaws, bugs, traps, code, design, queries, forms, reports, version and upgrade information.

allenbrowne.com

thanks for advice, already reading it

 

[TipsyWolf]

btw, Ive been watching Richard Rost a lot lately and i found decent tool to filter by him, but he restricted design view in his template's form

 

[Gasman]

I would probably start off simple.
In looking for something for you, I actually have a version like that in one of my DBs. Must have come from MajP.

This is code that I use in one of my forms, before I found his code.

Three combos that hold what I want to filter on.

Private Sub cmdFilter_Click()
Dim strFilter As String

If Me.cboCrew <> "" Then
    strFilter = "Crew = " & Me.cboCrew.Column(0)
End If
If Me.cboShip <> "" Then
    strFilter = strFilter & " AND Ship = " & Me.cboShip.Column(0)
End If
If Me.cboDate <> "" Then
    strFilter = strFilter & " AND [Date] = " & Me.cboDate.Column(0)
End If

If Left(strFilter, 4) = " AND" Then
    strFilter = Mid(strFilter, 5)
End If
Me.sfrmLinks.Form.Filter = strFilter
Me.sfrmLinks.Form.FilterOn = True
End Sub

This is for filter only

 

[TipsyWolf]

THis is code that I use in one of my forms, before I found his code

okay, im trying to implement it , but something i must be doing wrong.

1st, i created an unbound combo box.
set that i want this combo box pulls data from location field in my location table.
in sql mode i have

SELECT Tlocations.LocationID, Tlocations.Location
FROM Tlocations
ORDER BY Tlocations.[LocationID];

so i tell combo box to drop down a list of locations that used in my db.

then in the same form i go to "view code" and i have

Private Sub cmdFilter_Click()
Dim strFilter As String

If Me.cbolocation <> "" Then
    strFilter = "location = " & Me.cbolocation.Column(0)
End If

If Left(strFilter, 4) = " AND" Then
    strFilter = Mid(strFilter, 5)
End If
Me.sfrmLinks.Form.Filter = strFilter
Me.sfrmLinks.Form.FilterOn = True
End Sub

after i change combo box value it doesnt update anything.

 

[isladogs]

I have several articles on filtering and sorting continuous forms. You could start by looking at this summary article with multiple links to other articles and videos

You Can REALLY Do All That With Forms?

This article provides a summary of various topics that I covered in my presentation to the Access DevCon conference on 19 April 2024. It also provides links to various articles, videos and example apps to accompany each topic.

www.isladogs.co.uk

 

[Gasman]

Well you need to press the filter button to run the code?
Plus I cannot believe you have a subform control the same name as mine?
This tells me you do not understand the code, and are just copying and hoping for the best, which is never going to work.

You are testing for AND at the start of the filter strong, but you are not even using that word?
That was so I could use any filter choice except the first one.

 

[TipsyWolf]

you do not understand the code

yes, i dont. i never hide it. thats why its hard for me to do anything in access. but i love access. sad drama.

Well you need to press the filter button to run the code?

no, i was thinking about dropdown list.

and are just copying and hoping for the best

i thought its vba commands, thats why i didn't change them. changed only cbolocation and that is it. yeah. hard for me. but life isn't that easy. so im keep observing what i can do.
thank you away. i'll try to change some names in your code. maybe it will work for me.

 

[TipsyWolf]

I have several articles on filtering and sorting continuous forms. You could start by looking at this summary article with multiple links to other articles and videos

You Can REALLY Do All That With Forms?

This article provides a summary of various topics that I covered in my presentation to the Access DevCon conference on 19 April 2024. It also provides links to various articles, videos and example apps to accompany each topic.

www.isladogs.co.uk

thanks for your suggestion. already looking at it

 

[Gasman]

OK. Do you have a subform as I did, or is it just a mainform.
If you want it as you select a value in the combo, that code should be in the after update event of the combo. I used a button as I could use any or all of 3 filter choices, and only wanted to filter after those selections, not as I was selecting them.

 

[June7]

What is form RecordSource? Does it have a field named Location? What data type is Location?

Most likely value of Me.cbolocation.Column(0) is LocationID. The .Column(0) is not necessary.

Advise not to use exact same name for multiple fields.

 

[TipsyWolf]

Do you have a subform as I did, or is it just a mainform.

just mainform

that code should be in the after update event of the combo

i have

Option Compare Database
Option Explicit

Private Sub cbolocation_AfterUpdate()
Dim strFilter As String

If Me.cbolocation <> "" Then
    strFilter = "location = " & Me.cbolocation.Column(0)
End If

End Sub

Private Sub Form_Load()
  DoCmd.Maximize
End Sub

so far

what does strFilter mean here. is it object's name or vba function ?

 

[TipsyWolf]

What is form RecordSource?

in sql view its

SELECT Tmain.RiskID, Tmain.RiskCode, Tmain.riskTitle, Tmain.isActive, Tmain.RiskdueDate, Tmain.RiskDate, Tmain.location, Tmain.locationPricies, Tmain.risktype, Tmain.riskcolor, Tmain.reporter, Tmain.picafter, Tmain.picbefore
FROM Tmain;

Does it have a field named Location?

yes

What data type is Location?

short text

 

[Gasman]

It is a string variable that holds the filter statement.
So now you need to set the filter, then set the filter on.
Me.Filter = strFilter
Me.FilterOn = True