Design technique question

Posted on 2015-01-25
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
  • 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 47

Expert Comment

by:Dale Fye (Access MVP)
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 35

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.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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 …

810 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