如何使用多个复选框来过滤表单？ [英] How do you use multiple checkboxes to filter a form?

问题描述

嘿所有，


假设我有一个包含10个不同复选框（名为Check1，Check2，...）的表单，这些复选框基于数字1-10。用户将选择不同的复选框，然后单击一个按钮（名为Update），该按钮将根据查询打开拆分表单（名为FormUpdate）。需要将此表单过滤到检查的数字等于名为Number的控制字段的相同编号的位置。


例如，用户检查数字2,3，是否有可能过滤表格，只有列数字的记录2,3和6才能过滤。正在展示？


谢谢，我已经浏览了互联网和我的VBA书籍，似乎无法找到任何对我有用的信息。


-Ben

解决方案

这是一个子程序，它将根据单击按钮时选中的复选框呈现查询。这只是一种方法。


复选框被指定为默认值和默认值。在属性中。根据是否选中该框，控制值为true或false。 复选框的控件类型如果你有其他的复选框与你为表格描述的记录无关，那么你将不得不修改代码：

展开 | 选择 | Wrap | 行号

我花了一分钟时间继续并提出一些解释。表arrChkbox表只有2个字段。 1）Rec_ID（作为数字1,2,3等），和2）TheInfo。 （文本），它只代表您与每条记录相关的一些数据（您可能有更多）。对于每个复选框，我打开了它的属性表并设置了默认值。 =它代表的Rec_ID。这就是全部。


我选择了命令按钮'''Onclick"事件并使用代码生成器编写我的代码。您还必须在程序选项基础1之上加上因为在这种情况下我想建立一个第一个索引为1而不是0的数组。我使用了表格Control Collection。找到For Each中的复选框。环。如果该值（与默认值不同）为真（已选中），则我将其默认值放入Narr中。数组，所以当我完成后，我会暂时只存储检查的项目（他们的Rec_ID'）。然后我确切地知道在我的查询的WHERE子句中会有多少参数（在这种情况下我只使用Rec_ID =这个OR，OR等等）。


支持一秒钟，我最初宣布我的Narr数组没有指定它的尺寸（因为我不知道会提前检查多少个盒子）这使我能够扩展它，如果我找到一个被检查的盒子。这就是Redim。宣言。我使用RedimPreserve因此，当我为已检查的下一个框添加一个维度时，它不会删除我已放入数组的内容。 Redim本身会破坏数组中已有的任何现有数据。


接下来，我检查是否有任何方框被检查过（如果我> 0，因为我增加了i，当我找到第一个复选标记时上面的循环，所以如果Narr数组是空的，我可以取消按钮上的操作（这个例程的一小部分仍然需要你编码它;现在你只是收到一条消息，但不会形成任何疑问）。


因此，如果数组不为空，则代码循环通过它，不管最后一个条目，并为查询字符串组合标准（RecsChose），跳出循环到附加结束片段（数组中的最后一项）。然后查询的第一部分（选择......等）被卡在前面......这就是它。

注意我并没有把所有的我应该有的声明（原谅我）所以要清理它。我相信其他一些野牛仔还有另外一种方法可以做到这一点。但最后，只需更改查询定义以包含您需要的字段，以反映您打算在子表单，splitform等中显示的数据。


我假设在我的代码末尾你会选择sqlStr （这是最终的非戏剧性结果）并将其变成WhereClause。对于Docmd OpenForm操作，或类似的东西来呈现您需要的结果。 - 那么，祝你有美好的一天？


PS


如果你要在Docmd中使用sqlStr作为WhereClause。 OpenForm，我想你必须中断那个字符串的第一部分（看看那个）。

哇，我觉得我在你的帖子里学到的东西比我有更多在任何其他职位。谢谢！


我在代码中遇到以下问题：

展开 | 选择 | Wrap | 行号

Hey all,

Say I have a form with 10 different checkboxes (named Check1, Check2,...) that are based on the numbers 1-10. The user will select different checkboxes and then click a button (named Update) that will open a split form (named FormUpdate) based on a query. This form needs to be filtered to where the numbers that were checked equal the same numbers of the Control field named "Number".

For instance, the user checks numbers 2, 3, and 6. Is it possible to filter the form so only the records 2, 3, and 6 of the Column "Number" are showing?

Thanks, I''ve scrounged the internet and my VBA books and can''t seem to find any information that is working for me.

-Ben

解决方案

Here''s a subroutine that will render a query based on which checkboxes are checked when the button is clicked. This is just one way do do this.

The checkboxes are assigned the Record IDs as "Default Values" in properties. The control value is either true or false depending on whether or not the box is checked. The Control Type for a "checkbox" is 106. If you have other checkboxes that do not relate to the records you descibed for the form then you''ll have to modify the code:

Expand|Select|Wrap|Line Numbers

I took a minute to go ahead and throw in some explanation. The table "arrChkbox" only has 2 fields. 1) Rec_ID (as a number 1,2,3,etc.) , and 2) "TheInfo" (text) which just represents some data you would have associated with each record (you probably have more). For each Checkbox I opened it properties sheet and set the "Default Value" = to the Rec_ID that it represents. That''s all.

I selected the command button''s "Onclick" event and used the code builder to write my code. You also have to put above the procedure "Option Base 1" because in this case I wanted to set up an array whose 1st index would be 1 instead of 0. I used the forms "Control Collection" to find the checkboxes in the "For Each" loop. If the value (which is different from the Default Value) was true (checked) then I put its Default Value into the "Narr" array, so that when I was finished I would have temporarily stored only the items checked (their Rec_ID''s). Then I know exactly how many parameters there would be in the WHERE clause of my query (in this case I just use Rec_ID = this OR that, OR etc., etc.).

Backing up for a second, I originally declared my "Narr" array without specifying its dimension (because I wouldn''t know how many boxes would be checked in advance) which gave me the ability to expand it if I found a box that was checked. That''s the "Redim" declaration. I used Redim "Preserve" so that as I added a dimension for the next box that was checked it would not erase what I had already put into the array. Redim by itself destroys any existing data already in the array.

Next I checked to see if any box had been checked (if i > 0 because I incremented "i" in the loop above when I found the 1st checkmark) , so that If the Narr array was empty I could cancel out the action on the button (that little piece of the routine still needs for you to code it; right now you just get a message , but no query would be formed).

So, if the array is not empty the code loops through it, shy of the last entry, and compounds the criteria (RecsChose) for your query string, jumping out of the loop to attach the ending piece (last item in the array). Then the 1st Piece of the query (Select ...etc) is stuck on the front and ... ...That''s about it.
Notice I didn''t make all the declarations as I should have (forgive me) so clean it up. And I''m sure some other wild cowboy has got another way to do this. But finally, just change the Query definition to include the fields you need to reflect the data you intend to show in your subform, splitform, whatever.

I assume at the end of my code you would take the "sqlStr" (which is the final un-dramatic result) and make it into the "WhereClause" for a Docmd OpenForm operation, or something like that to present the results you need. -- So, Have a nice Day?

P.S.

If you''re gonna use the sqlStr for the WhereClause in a Docmd.OpenForm, I think you have to break off the first part of that string (look that up).

Wow, I thinked I''ve learned more about VBA in your post than I have in any other post. Thank you!

I am having an issue with the following line in the code:

Expand|Select|Wrap|Line Numbers

这篇关于如何使用多个复选框来过滤表单？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！