的MS Access：利用从用户输入主窗体和子窗体的标准的形式过滤器/搜索记录 [英] MS Access: Filter/Search records in a form using main form and subform criteria from user input

问题描述

我目前工作的这是相当复杂的，至少对于我的能力水平的数据库。本质上，它是项目，结构和联系人数据库。在这些结构也有各具独特的属性的子结构。

I'm currently working on a database which is rather complex, at least for my ability level. Essentially it is a database of projects, structures and contacts. Within these structures there are sub structures each with unique attributes.

项目，结构和联系方式连接在一起的一个主控制形式有一个选项卡式窗格。而这些窗格内是具有多个子表单的形式。我试图写一个搜索功能，我可以每个窗体，将允许用户选择多个条件来筛选结果的地方。然而，一些标准或在形式和其它标准从多个子表单，并得出最糟糕的是，在子窗体某些字段的多个条目。我明白，这听起来或许不是很健全的数据库设计，但我被告知要遵循特定的结构和布局对此我没有足够的技术工作，通过。

Projects, structures and contacts are joined together in one main control form with a tabbed pane. And within these panes are the forms which have multiple subforms. I am trying to write a search function which I can place on each form that will allow the user to select multiple criteria to filter results. However, some of the criteria or on the forms and other criteria are drawn from multiple subforms and to top it off some fields in the subforms have multiple entries. I understand that this may not sound like very sound database design, but I've been told to adhere to a specific structure and layout which I am not skilled enough to work through.

我一直在寻找通过艾伦·布朗的有关搜索条件的网站，但只有一个单一的形式。我试图拼凑子查询教程页面为好，但无济于事。从本质上讲，我想知道，如果它是在所有可能的筛选信息说..结构，这是主要的形式和类型，然后例如..范围，如长度存在于子窗体和对象接近的数字它存在于另一个子表单，并有它使得主形式只显示满足从子表单和主要形式条件的组合的记录。

I've been looking through allen browne's website regarding search criteria, but that is only one a single form. I've attempted to piece together the Subquery tutorial page as well, but to no avail. Essentially, I'm wondering if it is at all possible to filter information say.. the type of structure which is on the main form and then for example.. a range of numbers such as length which exists in a subform and an object proximity which exists in another subform, and have it so that the main form only displays records which fulfill that combination of criteria from the subforms and main form.

我的道歉，如果这是模糊的，如果它会帮助，我可以张贴的总体框架，我为我的数据库迄今。谢谢你的任何可以提供帮助。

My apologies if this is vague, and if it'll help I can post up the general framework I have for my database thus far. Thank you for any assistance you can provide.

编辑 - 添加了屏幕截图的详细信息 我不能发表图片，因为我代表低于10，但希望我能包括链接

EDIT--- Added screenshots for more information I can't post images since my rep is below 10 but hopefully I can include links

http://img829.imageshack.us/img829/1594/99258898.png   http://img40.imageshack.us/img40/2186/27578829.png

这是很难看到这里，但组合框中有些是多值列表。与第一图像向右上的组合框，是基于类型的从左上角组合框，它具有不同的属性的选择切换。理想情况下，我想能够通过初级形式和窗体进行搜索。并导致记录只有满足所有用户输入的数据。现在，我使用的过滤器，为每个标准已经尝试过，但所有这确实是不符合过滤条件的子窗体的空白出来的信息。

It's difficult to see here, but within the combo boxes some are multi-valued lists. And the combo box on the first image to the right, is switched based on the selection of Type from the upper left combo box, which has different attributes. Ideally I'd like to be able to search through primary forms and subforms. and result in records which only fulfill all the user inputted data. Right now I've tried using filters, one for each criteria, but all that does is blank out information in the subforms which doesn't correspond to the filter criteria.

推荐答案

你有任何的截图你可能张贴？

Do you have any screenshots you could possibly post?

这听起来好像是一个较大的设计问题，可能主要是在你的UI，但可能在你的表结构为好。我的一个自己的原则我尝试跟进是限制单屏/形式有多少事情做，或表演。你的描述听起来很混乱给我，我觉得普通用户可以很容易地被它淹没。

This sounds to me like a larger design issue, perhaps mostly in your UI but possibly in your table structure as well. One my own principles I try to follow is to limit how many things a single screen/form does or shows. What your describing sounds quite confusing to me and I think the average user could easily be overwhelmed by it.

通常子表单是主要形式的结果。也就是说，它们包含子记录，并在主要形式链接到记录通过子记录的外键，主表的主键。这并不是说有没有其他的方法来设计的形式，但这个被认为是正常或标准的设计。你的描述表明过滤的主要形式是决定通过在子窗体设置过滤器。我想我想不出任何问题，那些可能需要通过一些如混乱，笨拙的，因为这解决。我离开的可能性开放，我还没有遇到过什么困难或复杂，你要处理的。

Normally subforms are the result of a main form. That is, they contain child records and are linked to records in the main form via the child records foreign key and the main forms primary key. It isn't that there aren't other ways to design your forms but this is considered "normal" or "standard" design. Your description indicates that filtering the main form is determine by filters set on the subforms. I guess I can't think of any problem that could possibly need solving through something as confusing and clumsy as this. I leave the possibility open that I haven't encountered something as difficult or complex as you're dealing with.

我想你可能有一些困难得到更好的帮助这里的问题是，真的更适合一个论坛，如UtterAccess.com。您可以考虑张贴在这样的网站数据库的副本，以便用户可以查看你的设计，并可能建议更加坚实和标准的做法。

I think you may have some difficulty getting better help here as your question is really better suited to a discussion forum such as UtterAccess.com. You may consider posting a copy of your database on such a site so the users can review your design and possibly recommend a more solid and standard approach.

EDIT1：
1）根据您的截图，我认为你试图做太多的事情的一种形式。我通常会拿你的每个顶级标签（工程，结构，公司，联系人），使每个这样的自己的形式。进入2007年和2010允许您使用一个标签式界面本身，我想不出任何真正的好处在于使用了选项卡式容器控件开发自己的标签式界面。

1) Based on your screenshots, I think you're trying to do too many things in one form. I would normally take each of your top tabs (Projects, Structures, Companies, Contacts) and make each of those their own form. Access 2007 and 2010 allow you to use a tabbed interface natively and I can't think of any real benefit to using the tabbed container control to develop your own tabbed interface.

2），这是该搜索通常应该在有一个列表框，数据表视图子窗体或连续窗体形式单一的主要形式进行我的意见。无论这三个你选择的不是太重要。执行搜索后，用户应该能够单击在列表框或窗体所产生的记录，选择该记录并移动到它的详细信息视图类似你已经在这里建的形式。它可能需要几个不同的搜索界​​面，让您的用户能够搜索所有的不同的方式，你希望他们。例如，您可以建立一个屏幕来搜索项目，一个以搜索结构，另一个搜索联系人，和一个第四一个搜索引擎公司。它应该是可以组合其中的一些在一个查询，并允许用户通过一个以上的条件进行搜索，但如果试图使它们能够在一次搜索太多领域，特别是如果他们正在寻找的字段是它可能会比较混乱在单独的表。例如，如果你建立公司的查询，显示所有项目，结构和在一起，并以此作为记录来源的数据表子窗体，这可以允许用户通过工程，结构，或公司进行搜索。但是，如果一个项目有一个项目将被列为10倍，在查询/形式可能会迷惑用户，如果他们想通过项目搜索10结构。如果他们不是通过结构搜索，它将使完美的意义，他们有10项为同一个项目，因为他们会看到10种不同的结构。

2) It's my opinion that searches should normally be performed in a single main form that has a single listbox, datasheet view subform, or continuous forms subform. Whichever of these three you choose is not overly important. After performing the search, the user should be able to click in the resulting records in the listbox or subform to select that record and move to it on a details view form similar to what you've already built here. It might take several different search screens to allow your users to search all of the different ways you'd like them to. For example, you might build a screen to search projects, one to search structures, another to search contacts, and a fourth one to search companies. It should be possible to combine some of these in a query and allow the user to search by more than one criteria but it can get confusing if you try to allow them to search too many fields at once, especially if the fields they are searching are in separate tables. For example, if you build a query that shows all projects, structures, and companies together and use this as the recordsource for a datasheet subform, this could allow users to search by project, structure, or company. However, if a project has 10 structures that project will be listed 10 times in the query/form which could confuse users if they are thinking about searching by project. If they are instead searching by structure, it will make perfect sense to them that there are 10 entries for the same project since they'll see 10 different structures.

我的基本建议是，要小心试图建立太多的功能集成到一个单一的形式。有一些类型的任务，或组数据是确实需要复杂的形式与多个子表单和大量的控制，但一般要尽量避免这种情况。最好是一个非常集中的任务，建立各种形式，只是简单地建立更多的形式来完成所有必要的任务。当然，你可以采取这种方法太远很好，但你真的需要尽量减少复杂性，节省自己头痛的未来。

My basic recommendation is, be careful about trying to build too many functions into a single form. There are some types of tasks or sets of data that do require complex forms with multiple subforms and lots of controls, but in general you want to try to avoid this. It's better to build each form with a very focused task and just simply build more forms to accomplish all of the necessary tasks. Of course, you can take this approach too far as well but you do really need to try to minimize complexity to save yourself from headaches in the future.

至于基于多种标准筛选，我去看看我能找到任何很好的例子或者放一起来并使其可用。它也不是那么难，但它是一个多步骤的过程来建立和经验，使我，我很高兴有一个通用的设计和作品几乎完美在每一个可能发生的情况。

As far as filtering based on multiple criteria, I'll see if I can find any good examples or maybe put one together and make it available. It isn't so difficult but it is a multi-step process to build and experience has led me to a general design that I'm happy with and that works almost flawlessly in every possible situation.

EDIT2：
下面是如何创建一个多领域的过滤器/搜索。

Here's how you can create a multi-field filter/search.

1）构建查询。包括其中需要观看由用户或由用户上搜索所有表和字段。可以包括这里字段将被搜索上，但没有被用户可见的，尽管这可能是用户感到混淆。保存查询，并给它一个合适的名字。一定要包括你的ID /主键字段，因为这些将需要在步数4
2）现在突出显示在导航窗格中查询，进入主菜单，选择创建>更多表格>数据表（我使用Access 2007年）。允许访问创建并告诉你，我们将作为一个子窗体使用一个数据表的形式。保存此并给它像fsubProjectSearch的subformProjectSearch一个合适的名称。
3）获取的设计视图刚创建的新形式。更改属性不允许添加，删除和修改，除非你真的很想让这些操作。一般情况下，我不允许在这些类型的，除非我把它精心策划出来并测试搜索表单的任何数据录入。
4）进入code为这种形式，并添加code，弹出上双击相关的详细信息表单。例如，在projectid或项目名称字段，允许用户双击，弹出项目详细信息的形式。添加双击程序的任何其他文本框，用户可能要弹出一个结构，公司或联系人，假设你选择包括一个在那些你的查询或以上。你是code看起来像DoCmd.OpenFormProjectDetails，ProjectID =＆放大器;我！ProjectID现在再次保存表单并关闭它。
5）现在，创建一个新的空白表单并添加你的第一个窗体作为子窗体。离开房间顶部添加控件进行筛选。这种形式并不需要有任何记录源集。

1) Build your query. Include all tables and fields which need to viewed by the user or searched on by the user. You can include fields here that will be searched on but not viewable by the user, although that could be confusing to the user. Save your query and give it an appropriate name. Be sure to include your id/primary key fields since those will be needed in step number 4.
2) Now Highlight your query in the navigation pane, go to the top menu and choose Create > More Forms > Datasheet (I'm using Access 2007). Allow access to create and show you a datasheet form which we will use as a subform. Save this and give it an appropriate name like subformProjectSearch of fsubProjectSearch.
3) Get the design view for the new form you just created. Change the properties to disallow additions, deletions, and edits unless you'd really like to allow any of these operations. In general, I don't allow any data entry in these kinds of search forms unless I have it well planned out and tested.
4) Go to the code for this form and add code to popup relevant detail forms on double click. For example, in the projectid or project name field, allow your user to double click to bring up the project details form. Add double click routines to any other textbox where the user might want to pop up a structure, company or contact, assuming you've chosen to include one or more of those in your query. You're code will look something like DoCmd.OpenForm "ProjectDetails", , , "ProjectID = " & Me!ProjectID Now save your form again and close it.
5) Now create a new blank form and add your first form as a subform. Leave room at the top to add controls for filtering. This form does not need to have any recordsource set.

改道：我通常采取两种不同的方法来过滤之一。排名第一的是给用户的一个文本框，他们可以输入进去。我觉得做用他们所输入的数据在一大堆不同领域的模糊搜索，这是一个非常糟糕的方式表现明智的，尤其是当数据库增长近20000条记录。它也可能导致一些混乱，因为太多的结果可能会想出很短的通用搜索。如果他们只键入字母S和寻找它，他们可能会得到几乎所有记录。

Diversion: I usually take one of two different approaches to filtering. Number one is to give the user one textbox that they can type into. I think do fuzzy searches in a whole bunch of different fields using the data they've typed in. It's a perfectly awful approach performance wise, especially once the database grows past about 20,000 records. It can also lead to some confusion since too many results will likely come up for a short generic search. If they only type in the letter s and search for it they will likely get almost all records.

我用第二种方法可能更常见。给他们一个不同的文本框的各个领域，他们很可能要被搜索。你一个也使其复选框或组合框，如果一个适合他们正在寻找在球场上。这会不会是绑定的控件。这使用户能够得到相当具体，它也是最有可能带来他们正在寻找的确切的结果。至于code去，我不认为一个人的做法实在是比其他更简单。无论使用哪种方法，我在这里列出你真的需要检查的数据的类型，他们已经进入了你把之前在过滤语句某些领域它。例如，你不会想尝试使用他们非日期，文本输入来过滤日期字段。在第二种方法你都不可能通过验证规则，输入掩码，或通过设置文本框格式属性消除更多的这个问题。组合技也有助于被禁止输入不正确的数据。

The second approach I use is probably more common. Give them a different textbox for every field they are likely to want to search by. You an also make it a checkbox or combobox if that fits the field they are searching on. These will not be bound controls. This allows the user to get fairly specific and it also is most likely to return the exact results they are looking for. As far as the code goes, I don't think that one approach is really much simpler than the other. With either approach that I've listed here you really need to check the type of data they've entered before you put it in your filter statement for certain fields. For example, you wouldn't want to try to filter a date field using their non-date, text input. On this second approach you can possibly eliminate more of this problem by using validation rules, input masks, or by setting the textboxes format property. Combos also help to prohibit incorrect data from being entered.

还有第三种选择是一个有点混合的方法。您可以创建搜索在多个领域，但不一定对所有领域的一个文本框。例如，您可以创建一个文本框将被用于搜索公司，名字或姓氏。您可以创建许多这样的文本框，因为你需要。如何输入这些文本框被使用将完全由您在您的VBA code决定的。

Yet a third option is somewhat of a hybrid approach. You can create one text box that searches on more than one field but not necessarily on all fields. For example, you might create one textbox that will be used to search on Company, FirstName, or LastName. You can create as many of these textboxes as you need. How the input in these textboxes gets used will be totally determined by you in your VBA code.

6）现在，你已经决定要采取哪种方法，你可以继续添加控件。给他们适当的名称和标签。
7）现在是时候创建，将建立过滤语句的功能。使用的两种思路下面我列出的一个。请确保您在运行这些当用户presses您的搜索按钮或控件更新后事件。

6) Now that you've decided which approach you want to take, you can go ahead and add your controls. Give them appropriate names and labels.
7) Now it's time to create the function that will build the filtering statement. Use one of the two ideas I listed below. Make sure you run these when the user presses your search button or on the controls AfterUpdate event.

    Private Sub txtSearch_AfterUpadate()
         Call FilterSubformOption1 
    End Sub

Private Sub FilterSubformOption1()
    Dim strFilter as String
    Dim sSearch as String
    sSearch = Replace(Nz(Me.txtSearch, ""), "'", "''")
    If sSearch <> "" Then
        If IsDate(sSearch) = False Then
            strFilter = "(ProjectName LIKE '*" & sSearch & "*' OR "
            strFilter = strFilter & "StructureName LIKE '*" & sSearch & "*' OR "
            strFilter = strFilter & "CompanyName LIKE '*" & sSearch & "*')"
        Else
            strFilter = "ProjectDate = #" & CDate(sSearch) & "#"
        End If
    End If
    If strFilter <> "" Then
        Me.fsubProjectSearch.Form.Filter = strFilter
        Me.fsubProjectSearch.Form.FilterOn = True
    Else
        Me.fsubProjectSearch.Form.Filter = ""
        Me.fsubProjectSearch.Form.FilterOn = False
    End If
End Sub


Private Sub FilterSubformOption2()
    Dim strFilter as String

    If Nz(Me.txtProjectName, "") <> "" Then
        strFilter = "ProjectName LIKE '*" & Replace(Me.txtProjectName, "'", "''") & "*' AND "
    End If
    If Nz(Me.txtStructureName, "") <> "" Then
        strFilter = strFilter & "StructureName LIKE '*" & Replace(Me.txtStructureName, "'", "''") & "*' AND "
    End If
    If Nz(Me.txtCompanyName, "") <> "" Then 'Search on multiple fields from one textbox
        strFilter = strFilter & "CompanyName LIKE '*" & Replace(Me.txtCompanyName, "'", "''") & "*' AND "
    End If      
    If IsNull(Me.txtProjectDate) = False Then
        If IsDate(Me.txtProjectDate) = True Then
            strFilter = strFilter & "ProjectDate = #" & Me.txtProjectDate & "#"
        End If
    End If
    If Right(strFilter, 5) = " AND " THEN strFilter = Left(strFilter, Len(strFilter) - 5)
    If strFilter <> "" Then
        Me.fsubProjectSearch.Form.Filter = strFilter
        Me.fsubProjectSearch.Form.FilterOn = True
    Else
        Me.fsubProjectSearch.Form.Filter = ""
        Me.fsubProjectSearch.Form.FilterOn = False
    End If
End Sub 

Private Sub FilterSubformOption3()
    Dim strFilter as String

    If Nz(Me.txtProjectName, "") <> "" Then
        strFilter = "ProjectName LIKE '*" & Replace(Me.txtProjectName, "'", "''") & "*' AND "
    End If
    If Nz(Me.txtStructureName, "") <> "" Then
        strFilter = strFilter & "StructureName LIKE '*" & Replace(Me.txtStructureName, "'", "''") & "*' AND "
    End If
    If Nz(Me.txtName, "") <> "" Then 'Search on multiple fields from one textbox, the "hybrid" solution
        strFilter = strFilter & "(CompanyName LIKE '*" & Replace(Me.txtName, "'", "''") & "*' OR "
        strFilter = strFilter & "FirstName LIKE '*" & Replace(Me.txtName, "'", "''") & "*' OR "
        strFilter = strFitler & "LastName LIKE '*" & Replace(Me.txtName, "'", "''") & "*') AND "
    End If      
    If IsNull(Me.txtProjectDate) = False Then
        If IsDate(Me.txtProjectDate) = True Then
            strFilter = strFilter & "ProjectDate = #" & Me.txtProjectDate & "#"
        End If
    End If
    If Right(strFilter, 5) = " AND " THEN strFilter = Left(strFilter, Len(strFilter) - 5)
    If strFilter <> "" Then
        Me.fsubProjectSearch.Form.Filter = strFilter
        Me.fsubProjectSearch.Form.FilterOn = True
    Else
        Me.fsubProjectSearch.Form.Filter = ""
        Me.fsubProjectSearch.Form.FilterOn = False
    End If
End Sub     

这pretty的多完成了多个条件过滤解决方案，我通常使用。

And that pretty much completes the multiple criteria filtering solution I typically use.

这篇关于的MS Access：利用从用户输入主窗体和子窗体的标准的形式过滤器/搜索记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！