Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Design technique question

Posted on 2015-01-25
Medium Priority
Last Modified: 2016-06-23
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).
Question by:bfuchs
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40569999
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...

LVL 48

Expert Comment

by:Dale Fye
ID: 40570380
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.
LVL 39

Expert Comment

ID: 40571190
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.
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 40571359
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..

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40571544
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...

LVL 48

Expert Comment

by:Dale Fye
ID: 40571708
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.

Author Comment

ID: 40571944
@ 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.


Author Comment

ID: 40571957
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.

Accepted Solution

bfuchs earned 0 total points
ID: 40573935
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


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Starting up a Project

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question