所以,我_12303011_茫然 . 我正在构建一个访问数据库 .
我有一个有18个组合框的表格 . 使用SQL SELECT语句通过vba设置组合框行源(请参阅下面的SetSourcetbl子句) . 这个想法是让用户根据他们选择的任何字段(组合框)记录"filter"条记录 . 更新组合框后,SQL WHERE语句被标记到表单上每个组合框的行源查询的末尾,因此每个框都更新为仅显示符合用户选择的"filters"的预定义查询(qryListAll)中的记录至今 .
基本上,用户使用这些组合框来构建一个特定的SQL语句,稍后将用于填充"results"表单 .
每个字段允许零长度字符串 . 我已经在其中一个组合框中构建了一个结果,其中列表中只剩下一个项目,只有零长度字符串(或"single space"条目)留在组合框列表中,组合框变为红色 .
我的问题是,在一个框中选择了一个项目并且其他框的所有行源都已使用新的SQL字符串更新后,检查每个框的.ListCount = 1需要很长时间,因为组合框必须在检查.ListCount属性之前完全填充(并且每个框在其列表中有大约10到1000个项目) .
有没有办法(比如记录集's .RecordCount property) to know if there is more than one item in the combobox'的列表没有等待整个列表填充?我尝试将记录集设置为用于每个行的行源的相同SQL字符串并使用.RecordCount属性或.Move属性(检查是否存在第二个/第三个项目),但是将记录集设置为每个SQL字符串所花费的时间与等待到目前为止应用的组合框's .ListCount to load. Here is my code. Note that SQLCriteria is a global string (bad, bad, I know) that records which 1223019 have been applied thus far, and FilterCounter is a global long to see whether the SQL string needs to be prefixes with a 1223020 or an 1223021 (basically checking if the current filter is the first one that'一样长:
其中一个组合框的更新事件(它们都基本相同):
Private Sub SelectOEMName_AfterUpdate()
UpdateBrowseField Me.SelectOEMName
Me!SelectFamilyName.SetFocus
AutofillComboBoxes
End Sub
以及调用的相应函数:
Private Sub AutofillComboBoxes()
Dim Ctl As Control
For Each Ctl In Me.Controls
If Ctl.ControlType = acComboBox Then
If Ctl.ListCount = 1 Then
If Ctl.ItemData(0) = "" Or Ctl.ItemData(0) = " " Or _
IsNull(Ctl.ItemData(0)) Then
Ctl.BackColor = REDTINT
Else
Ctl = Ctl.ItemData(0)
End If
End If
Ctl.RowSource = ""
End If
Next Ctl
SetSourcetbl
End Sub
UpdateBrowseField子(在公共模块中):
Public Sub UpdateBrowseField(Ctl As Control)
Dim FieldName As String
FieldName = Replace(Ctl.Name, "Select", "")
If Ctl <> "" And Ctl <> " " And IsNull(Ctl) = False Then
If FilterCounter = 0 Then
SQLCriteria = " WHERE " & FieldName & " = '" & Ctl.Value & "'"
Else
SQLCriteria = SQLCriteria & " AND " & FieldName & " = '" & Ctl.Value & "'"
End If
SetSourcetbl
FilterCounter = FilterCounter + 1
Ctl.BackColor = BLUETINT
Ctl.BackTint = 1
If Ctl.Enabled = True Then Ctl.SetFocus
End If
End Sub
SetSourcetbl Sub(在公共模块中):
Public Sub SetSourcetbl()
Dim CurrentForm As Form
For Each CurrentForm In Application.Forms
If CurrentForm.Visible And (CurrentForm.Name = "frmChooseReport" Or CurrentForm.Name = "frmBrowse") Then Exit For
Next CurrentForm
If CurrentForm.Name = "frmChooseReport" Or CurrentForm.Name = "frmBrowse" Then
With CurrentForm.Controls
!SelectOEMName.RowSource = "SELECT DISTINCT OEMName FROM (SELECT * FROM qryListAll" & SQLCriteria & ")"
!SelectFamilyName.RowSource = "SELECT DISTINCT FamilyName FROM (SELECT * FROM qryListAll" & SQLCriteria & ")"
!SelectModSubmod.RowSource = "SELECT DISTINCT ModSubmod FROM (SELECT * FROM qryListAll" & SQLCriteria & ")"
!SelectFrameName.RowSource = "SELECT DISTINCT FrameName FROM (SELECT * FROM qryListAll" & SQLCriteria & ")"
' etc. etc. for all 18 comboboxes
End With
End If
End Sub
先感谢您!
1 回答
要尝试两件事:
1)只需使用Me.ComboBox.Column(0,1)进行检查 .
2)使用记录集对象并前进一次,然后检查rst.EOF
但我认为最好的选择是将所有组合框保持不受限制,直到前一个组合框被填满 .
这样你一次只能加载一个 . 如果它们是顺序依赖的,那么你可以假设一旦返回0行,那么所有后续的组合框将具有零行 .