我有一个带有多选择列表框的连续表单，称为statusListBox，然后将所有选定的值从框中传递给带有替换函数的SQL查询（“ FelterQuery”），更改查询的SQL代码。这效果很好并更新了查询的文本，并且在打开查询时可以看到更改，但是我的连续表格不会更新。我怀疑问题是在表格已经加载后，并且没有适当的参数（即 formsname！formName！control ），
我使用 me.requery 更新表单，该表单与 filterQuery sql代码中的参数非常有用，但在这种情况下不可能。
我是新手，我很困惑。
这是sub：
首先，我从文本框中制作一个字符串，
StatusListValues = ""
For Each StatusItem In StatusLIst.ItemsSelected
StatusListValues = StatusListValues & "'" & StatusLIst.ItemData(StatusItem) & "'" & ","
Next StatusItem
StatusListValues = Left(StatusListValues, Len(StatusListValues) - 1)
Debug.Print StatusListValues
然后我将当前的SQL代码保存为字符串，将所选值的先前字符串替换为当前字符串，然后使当前字符串
“上一个”
SQLstring = CurrentDb().QueryDefs("FilterQuery").SQL
SQLstring = Replace(SQLstring, StatusListValuesPrevious, StatusListValues)
CurrentDb.QueryDefs("FilterQuery").SQL = SQLstring
StatusListValuesPrevious = StatusListValues
I have a continuous form with a multiselect listbox, called StatusListBox, and I pass all the selected values from the box to an SQL query ("FilterQuery") with a Replace function, changing the SQL code of the query. This works well and updates the text of the query and I can see the changes when I open it, but my continuous form doesn't get updated. I suspect the problem is that I change the text of the query's SQL code after the form has already loaded and not with proper parameters (i.e. Forms!FormName!Control)
I use Me.Requery to update the form, which works great with textfields which are parameters in the FilterQuery SQL code, but not in this case.
I'm new to Access and I'm very puzzled.
Here's the sub:
first I make a string with all the values from the textbox
StatusListValues = ""
For Each StatusItem In StatusLIst.ItemsSelected
StatusListValues = StatusListValues & "'" & StatusLIst.ItemData(StatusItem) & "'" & ","
Next StatusItem
StatusListValues = Left(StatusListValues, Len(StatusListValues) - 1)
Debug.Print StatusListValues
then I save the current SQL code as a string, replace the previous string of selected values with current string, then make current string the
"previous" one
SQLstring = CurrentDb().QueryDefs("FilterQuery").SQL
SQLstring = Replace(SQLstring, StatusListValuesPrevious, StatusListValues)
CurrentDb.QueryDefs("FilterQuery").SQL = SQLstring
StatusListValuesPrevious = StatusListValues
发布评论
评论（1）
您应该避免编辑现有查询的SQL字符串。相反，您应该根据所选项目过滤表单。
创建一个辅助函数，该函数返回所选状态（如果有），如果
'a'，'b'，'c'等。 >列表框的事件，获取所选项目（如果有），然后过滤表单，或者如果没有选择，则清除过滤器。
You should avoid editing the SQL string of an existing query. Instead, you should filter the form based on the selected items.
Create a helper function that returns the selected statuses (if any) if the form of
'a','b','c'etc.Then, on the
AfterUpdate()event of the ListBox, get the selected items (if any) and either filter the form, or clear the filter if nothing has been selected.