Filter query by unbound boxes - with a twist (1 Viewer)

Maclain

Registered User.
Local time
Today, 04:51
Joined
Sep 30, 2008
Messages
109
Hi all,

I've created a form, frmTechniqueAllocate that displays data from the query qryTechniqueMasterFilter

On the form I have 4 unbound text boxes, the idea behind these is to allow the user to progressively filter the results to find what they need:

Unbound text boxes:

CustomerFilter
DescriptionFilter
Drg-PattFilter
JobNoFilter

The idea is the user can input into any of the above boxes and the form filters the results accordingly.

However, the one caveat to this is the Drg-PattFilter, which I need to look across two separate fields in the query.

I'm struggling with the criteria to get this to work correctly, I'm using the len function to deal with nul fields.

So in the first row of criteria I have the following across fields:

TechCustomer
TechDescription
TechDrawingNo

Jonno

Code:
Like IIf(Len([Forms]![FrmTechniqueAllocate]![CustomerFilter])=0,"*","*" & [Forms]![FrmTechniqueAllocate]![CustomerFilter] & "*")
Like IIf(Len([Forms]![FrmTechniqueAllocate]![DescriptionFilter])=0,"*","*" & [Forms]![FrmTechniqueAllocate]![DescriptionFilter] & "*")
Like IIf(Len([Forms]![FrmTechniqueAllocate]![Drg-PattFilter])=0,"*","*" & [Forms]![FrmTechniqueAllocate]![Drg-PattFilter] & "*")
Like IIf(Len([Forms]![FrmTechniqueAllocate]![JobNoFilter])=0,"*","*" & [Forms]![FrmTechniqueAllocate]![JobNoFilter] & "*")
then in the second criteria box for TechPatternNo I have

Code:
Like IIf(Len([Forms]![FrmTechniqueAllocate]![Drg-PattFilter])=0,"*","*" & [Forms]![FrmTechniqueAllocate]![Drg-PattFilter] & "*")
At the moment this isn't functioning as expected, I thought I had it working, but now only the fields TechDrawingNo and TechPatternNo will filter.


Any advice / help appreciated.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 04:51
Joined
Jan 14, 2017
Messages
18,186
Slightly off topic but the standard method of doing progressive filters is to use cascading comboboxes so you are guiding the user at each step

Plenty of examples elsewhere in the forum if you do a search.
Each of the combo row sources would of course each use the filter value from the previous combo
 

Maclain

Registered User.
Local time
Today, 04:51
Joined
Sep 30, 2008
Messages
109
Hi Ridders,

I'm unable to guide the user through a combo selection as the search process can be complex, requires some vague searches and there are thousands of records. We also have the option to find a similar item and append the information to the new item..
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 04:51
Joined
Sep 12, 2006
Messages
15,614
Another way is to add a generic filter button, and use code to "filter by selection". Then users can do what they want without any particular programming being required.

Generally click in a control with the value the want to use, and then click a button.
You can repeat this for further controls to "zoom" in the detail you want.

this will filter by selection (using a button click event)

Code:
Private Sub btnFilterSelection_Click()
On Error GoTo Err_btnFilterSelection_Click

    Screen.PreviousControl.SetFocus
    RunCommand acCmdFilterBySelection

Exit_btnFilterSelection_Click:
    Exit Sub

Err_btnFilterSelection_Click:
    MsgBox err.description
    Resume Exit_btnFilterSelection_Click
    
End Sub


this will clear the filter

Code:
Private Sub btnClearFilter_Click()
    FilterOn = False
End Sub
 

isladogs

MVP / VIP
Local time
Today, 04:51
Joined
Jan 14, 2017
Messages
18,186
Hi Ridders,

I'm unable to guide the user through a combo selection as the search process can be complex, requires some vague searches and there are thousands of records. We also have the option to find a similar item and append the information to the new item..

Then you may find Dave's suggestion useful.

For info, attached is an example of what I meant. This guides users to a selected single UK postcode from an initial list of around 2.6 million (far too many to be manageable for a single combo)

I would still use combos for as many steps as you can.
The code for each is updated using code based on the previous selection
 

Attachments

  • PostcodeBuilder.PNG
    PostcodeBuilder.PNG
    85.3 KB · Views: 105

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:51
Joined
May 7, 2009
Messages
19,169
you dont need to test the length
of the textbox if there is or
there is no value:

Like "*" & Trim([Forms]![FrmTechniqueA]![CustomerFilter] & "") & "*"
Like "*" & Trim([Forms]![FrmTechniqueA]![DescriptionFilter] & "") & "*"
Like "*" & Trim([Forms]![FrmTechniqueA]![Drg-PattFilter] & "") & "*"
Like "*" & Trim([Forms]![FrmTechniqueA]![JobNoFilter] & "") & "*"

you must have a Command Button there in the
form to commence the filtering (eg: cmdSearch).


Private Sub cmdSearch_Click()
Me.Requery
End Sub
 

Maclain

Registered User.
Local time
Today, 04:51
Joined
Sep 30, 2008
Messages
109
Thanks for the responses all.

I'm still trying to get this together using the query criteria.

Using the suggested criteria from arnelgp everything works except the pattern number filter.
 

Users who are viewing this thread

Top Bottom