Solved

Design technique question

Posted on 2015-01-25
10
22 Views
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).
untitled.bmp
0
Comment
Question by:bfuchs
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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...

JeffCoachman
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
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)
Employees
EmployeesSchedules
EmployeesNotes
EmployeesOrientations

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..

Thanks,
Ben
Untitled.png
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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 _______________________
...etc


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

Lets see what other experts contribute...

Jeffcoachman
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
@ 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
    Else
        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.

Thanks,
Ben
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
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.
0
 
LVL 3

Accepted Solution

by:
bfuchs earned 0 total points
Comment Utility
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

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This is an explanation of a simple data model to help parse a JSON feed
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now