Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • Last Modified:

Design technique question

Hi Experts,

Attached is a form that is used in our application by one form as a popup form to filter for employees, however I am trying now to expand this filter utility to various forms in our application,

The way I am planning to use it is as follows, insert is as a hidden subform, then when users clicks on filter button, this will move to the center, expand itself to full size and become visible.

One of the reasons of designing it this way is to allow user to have this filter option open multiple times by multiple forms, all at the same time.

Now my question here is, where do I build the  dynamic sql? and how do I call it?

I could have written something like this on the main form
 if not isnull(Me.SubForm.Form!TextCriteria) then...

Open in new window

however this would require a code modification for each main form every time I update the sub form (meaning the attached filter form).
  • 4
  • 2
  • 2
  • +1
1 Solution
Jeffrey CoachmanMIS LiasonCommented:
There are a lot of unknowns here...
<filter for employees>
Filter for exact name? Partial Name? Address?, Department?, one criteria?, Multiple criteria?
So when you say "filter for employees",.. can you provide specific examples?

<insert is as a hidden subform, >
I dont see a need for this, the only reason to keep a form open is to maintain a connection to an external source...

<One of the reasons of designing it this way is to allow user to have this filter option open multiple times by multiple forms, all at the same time.>
Is this really needed? ...this complicates things more that is probably needed.

Can you give an example of why a user would have multiple forms open all filtering for employees from one form?
(And why would you need more than one "employee" form open)

You can easily use one form to filter many forms *one at a time* though...

Dale FyeCommented:
Assuming that is your filter form, you are setting yourself up for a lot of code.

I agree with Jeff that you probably don't need multiple versions of this form open at a single time, unless you are trying to retain the filter criteria so that you can go back to that same filter form and simply make modifications to it.

Opening separate instances of the same form can be problematic, particularly keeping track of which instance has the focus, and returning the filter string back to the correct calling form, and then closing the correct instance of the form.  You might want to look at this article by Allen Browne for how to properly keep track of multiple instances of the same form.
In general, allowing multiple forms to be open at the same time confuses users.  The only time I do it is when one form is dependent on another.  So, I might have a list form and clicking on an item in the list will open a form showing all the details for a single record.

Also, as I look at your filter form, it looks like it filters at different levels and implies combinations of AND and OR relational operators.  I hope your tables are properly normalized so the multiple selection options for things like cities or licenses would be looking at rows in different tables.  You couldn't even bring these back in a single query that made sense.

I would recreate the filter as several forms with more focused selection criteria so that the source you are searching is singular rather than attempting to have criteria that applies to multiple unrelated tables.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

bfuchsAuthor Commented:
Hi Experts,

Part of your questions are answered by Dale's comment:
unless you are trying to retain the filter criteria so that you can go back to that same filter form and simply make modifications to it

However let me give you a little bit of info how the program currently works, maybe that will better explain..

We have a database of the following:
(Just to name a few)

Now we have 4 forms used to filter those tables, each for each..
the way its designed is that records are displayed in continuous form format, and the form header is designated to contain all the filter options,

(I am attaching a screenshot from one of them)

Users do need to switch from form to form while maintaining all filters and data in previous form.
while each form's top section contains filter options that are unique to that form, some options are equally useful in all forms, which are the criteria of employee, as all those tables are related to employees.

The form I previously attached contains the filter option for employees and this is what can be used in all forms, for example the option to select only employees who live in certain cities and speak certain languages etc.. this can be used to filter notes, schedules and orientations of those employees.

In order to accomplish that, I am trying what mentioned in original post, to include that filter form as a sub form in the filter section of those existing forms.

My main question here is how to design that dynamic filter in a way that will best suit reusability, meaning that it should not require a change of code only in one single place, in case I need to add another filter option there..

Jeffrey CoachmanMIS LiasonCommented:
Still confused..
Still not sure what you are asking for in terms of an example of a user scenario:

For example:
User needs to:______________
User will____________________
Then the user needs to _______________________

It may be just me, ...but this all seems to me more complicated than it needs to be....

Lets see what other experts contribute...

Dale FyeCommented:
Well, if you truly want that filter form to be a subform, you could create a tab control on each form, put all of the main controls on the first tab (or put a subform on that tab as well), then configure the 2nd tab with the a subform control.  Set the source object of this control to blank until it is needed, then set it to your filter form.  With this method (rather than using a popup), you don't need to worry about managing each instance of the form, it will already be encapsulated as a subform and can be referred to as such.

When the user clicks the "Filter" button, you would simply construct your filter and apply it to the main form, or two the subform that is on the 1st tab if you choose to do it that way.  This way, the filter form would retain it's values to allow you to quickly modify the filter parameters and would avoid the hassle associated with working with multiple instances of the same form.
bfuchsAuthor Commented:
@ Dale,
the approach I mentioned is similar to yours, its also not a popup form that has to be open with multiple instances, instead its a tiny control on the forms header section that is by design invisible and gets "popped up" with the following code.
Private Sub Toggle146_AfterUpdate()
    If Me.Toggle146 = True Then
        Me.FormHeader.Height = "12000"
        With Me.EmpMoreFilterFrm
        .Left = "1667"
        .Top = "700"
        .Width = "15000"
        .Height = 11500
        .Visible = True
        End With
        With Me.EmpMoreFilterFrm
        .Left = "12.2917"
        .Top = "0.5417"
        .Width = "1680"
        .Height = "0.2083"
        .Visible = False
        End With
        Me.FormHeader.Height = "2.6708"
    End If
End Sub

Open in new window

When the user clicks the "Filter" button, you would simply construct your filter and apply it to the main form
this is the part I am struggling with..
As mentioned, I don't want to have all the constructing code on the main form, as this would require maintaining the code in multiple places, rather would prefer to have sort of a function that returns everything that is selected in that sub form (the sql part of it), so I can call that function from anywhere in the application.

bfuchsAuthor Commented:
I actually skipped an important point, that the sub form only contains part of the filter options, while the rest resides in the main form, and therefore it would not be possible to construct the entire sql string from a click on a command button in the sub form.
bfuchsAuthor Commented:
Hi Experts,
Think I got it to work as follows.
Public Function GetSqlFromEmpMoreFilter(Optional sTbl As String = "") As String
    Dim s As String
    Dim frm As Form
    Set frm = Screen.ActiveForm!EmpMoreFilterFrm.Form
    If sTbl <> "" Then sTbl = sTbl & "."
    If Not IsNull(frm.EmployeesLanguage) Then
        s = s & " AND " & sTbl & "EmployeeID IN (SELECT EmployeeID FROM EmployeesLanguages WHERE Language = '" & frm.EmployeesLanguage & "')"
    End If
    If Len(s) > 0 Then
        GetSqlFromEmpMoreFilter = s
    End If
End Function

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now