从另一个组合框,VBA填充和排序组合框
问题描述:
嗨每个人我都遇到麻烦,当另一个组合框被选中时,要排序组合框。 我认为我有正确的SQL语法,但我似乎不能让vba运行它;目前vba会返回记录集中的所有状态,而不考虑公司。从另一个组合框,VBA填充和排序组合框
Private Sub CboCountry_Click()
Set db = CurrentDb
Dim SQLStr As String
Set RsState = db.OpenRecordset("T2States", dbOpenSnapshot, dbSeeChanges)
'populates combobox with recordset, that is defined by the country input from the form
RsState.MoveFirst
Do While Not RsState.EOF
Me.CboState.RowSource = Me.CboState.RowSource & RsState("StateID") & ";" & RsState("State") & ";"
RsState.MoveNext
Loop
我认为这是正确的SQL字符串,但我有麻烦得到它的工作。
'SQLStr = "SELECT T2States.StateID, T2States.States, T2States.CountryID" & _
" FROM T2States GROUP BY T2States.StatesID" & _
" WHERE T2States.CountryID = """ & Me.CboCountry.Value & """"
任何帮助将不胜感激。
编辑#1 请参阅下面的完整代码,将SQLStr替换为Openrecordset时弹出的错误是运行时错误'3078',Microsoft Access数据库引擎无法找到输入表或查询'SQLStr'。确保它存在并且它的名称拼写正确。 应该发生的是当从CboCountry组合框中选择一个国家,它将由CountryID
排序记录加载CboState组合框请参阅下面的两个部分代码
Private Sub Form_Load()
Set db = CurrentDb
Set RsCompany = db.OpenRecordset("T1Company", dbOpenDynaset, dbSeeChanges)
Set RsCountry = db.OpenRecordset("T2Countries", dbOpenSnapshot, dbSeeChanges)
Set RsAddress = db.OpenRecordset("T1Addresses", dbOpenDynaset, dbSeeChanges)
Set RsAddressType = db.OpenRecordset("T2AddressType", dbOpenSnapshot, dbSeeChanges)
Set RsCompanyAddress = db.OpenRecordset("T3Company_Address", dbOpenDynaset, dbSeeChanges)
Me.CboCountry = Null
Me.TxtAddress1 = Null
Me.TxtAddress2 = Null
Me.TxtAddress3 = Null
Me.TxtCity = Null
Me.CboAddressType = Null
Me.CboCountry = Null
Me.CboState = Null
Me.TxtPostalCode = Null
Me.TxtCompanyID = Null
Me.TxtLegalName = Null
Me.TxtNickname = Null
Me.TxtAddressID = Null
RsCountry.MoveFirst
Do While Not RsCountry.EOF
Me.CboCountry.RowSource = Me.CboCountry.RowSource & RsCountry("CountryID") & ";" & RsCountry("Country") & ";"
RsCountry.MoveNext
Loop
RsAddressType.MoveFirst
Do While Not RsAddressType.EOF
Me.CboAddressType.RowSource = Me.CboAddressType.RowSource & RsAddressType("AddressTypeID") & ";" & RsAddressType("AddressType") & ";"
RsAddressType.MoveNext
Loop
Me.TxtLegalName.SetFocus
End Sub
Private Sub CboCountry_Click()
Set db = CurrentDb
Dim SQLStr As String
'SQLStr = "SELECT T2States.StateID, T2States.State, T2States.CountryID" & _
" FROM T2States" & _
" WHERE T2States.CountryID = """ & Me.CboCountry.Value & """"
Set RsState = db.OpenRecordset("T2States", dbOpenDynaset, dbSeeChanges)
'populates combobox with recordset, that is defined by the country input from the form
RsState.MoveFirst
Do While Not RsState.EOF
Me.CboState.RowSource = Me.CboState.RowSource & RsState("StateID") & ";" & RsState("State") & ";"
RsState.MoveNext
Loop
End Sub
答
让我们来看看
1 - 确保你与
Having T2States.States, T2States.CountryID
2 - 错误附加在它存在的,额外的“中列名:
GROUP BY T2States.StatesID
3把所有的代码,我会检查你与你想念。
问候
答
这一次竟然是在数据选项卡下的属性表速战速决,行来源类型必须从一个“值”改回“表/查询”。
有VBA可以解释这一点,但它只是一个简单的改变行来源。
混淆的原因,如果有帮助的话,稍微介绍一下背景是,我所有的组合框都是未绑定的,我用VBA记录集绑定了它们,所以行源必须是值列表 - 实质上VBA每次加载时都要写清单。
当我开始使用SQL生成记录集时,即使它在VBA中,我也必须将属性更改回Table/Query。
谢谢。
请参阅编辑#1,这些是适用的代码,在表单加载CboCountry被初始化后,一旦从组合框中选择Country,它应该加载CboState,但是我得到运行时错误。 –