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 38

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.
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

762 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