使用SQL数据自动填充组合框 [英] Auto-Populating a Combobox with SQL data

问题描述

我是这个论坛的新手，所以很抱歉，如果已经有人问过我却找不到它，或者发帖人在错误的地方.

I'm new to this forum, so apologies if this has already been asked and I haven’t found it or if I’m posting in the wrong place.

我有一个已经使用多年的例程，该例程根据来自SQL的数据填充组合框.我有一个包含两个组合框的电子表格，第一个组合框可以正常工作，并直接从SQL表中给出文件夹名称的列表.第二个组合框将填充与在第一个组合框中选择的文件夹相关联的所有合同.但是，我无法自动填充代码，为了更新组合框，我必须先从下拉列表中选择一个项目，然后再保留该值.我在下面粘贴了我的代码:

I have a routine that I have been using for years which populates a combobox based on data from SQL. I have a spread sheet with two comboboxes in it, the first combobox works fine and give a list of folder names straight from a SQL table. The second combobox is populated with all of the contracts that are associated with folder selected in the first combobox. However I can’t get the code to auto-populate, in order to get the combobox to update I have to select an item from the drop down list first and then the value isn’t held. I have pasted my code below:

Private Sub CB_Company_Change()

With Application
     .Calculation = xlManual
     .EnableEvents = False
     .ScreenUpdating = False
 End With

Dim stSQL As String, sBook As String, rst As ADODB.Recordset, k As Integer, vaData As Variant
Dim objConn As ADODB.Connection
Dim ConnectionString As String
ConnectionString = "Provider=sqloledb.1;data source=sql-server;Initial catalog=sql-db;Integrated Security = SSPI;"

Set objConn = New ADODB.Connection

sBook = CB_Book.Value
CB_Company.Clear

stSQL = "EXEC('SELECT Name FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID WHERE TABLE2.NAME = ''" & sBook & "''  ORDER BY TABLE1.NAME')"

With objConn
    .CursorLocation = adUseClient
    .Open ConnectionString
    Set rst = .Execute(stSQL)
End With

With rst
    Set .ActiveConnection = Nothing
    k = .Fields.Count
    vaData = .GetRows
End With

CB_Company.List = Application.Transpose(vaData)

objConn.Close

Set rst = Nothing
Set objConn = Nothing

bClear = True

With Application
    .Calculation = xlAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub

在运行时为何可能不会自动填充的任何帮助将不胜感激.

Any help on why this might not be auto populating when run would be much appreciated.

此处要求的是第一个组合框的代码，该代码为我提供了我的图书价值:

As requested here is the code from the first combobox that gives me my book value:

Option Explicit
Public bClear As Boolean

Private Sub CB_Book_Change()

With Application
    .Calculation = xlManual
    .EnableEvents = False
    .ScreenUpdating = False
End With

Dim stSQL As String, rst As ADODB.Recordset, k As Integer, vaData As Variant
Dim objConn As ADODB.Connection
Dim ConnectionString As String
ConnectionString = "Provider=sqloledb.1;data source=SQL-SERVER;Initial catalog=SQL-DB;Integrated Security = SSPI;"
Set objConn = New ADODB.Connection

stSQL = "EXEC('SELECT NAME FROM TABLE2')"

With objConn
    .CursorLocation = adUseClient
    .Open ConnectionString
    Set rst = .Execute(stSQL)
End With

With rst
    Set .ActiveConnection = Nothing
    k = .Fields.Count
    vaData = .GetRows
End With

CB_Book.List = Application.Transpose(vaData)

objConn.Close

Set rst = Nothing
Set objConn = Nothing

bClear = True

With Application
    .Calculation = xlAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub

推荐答案

我最近遇到了这个确切的问题.正在发生的是，在更新内容时，"Change"方法在其内部经常被调用.例如:

I had this exact problem recently. What is happening is the "Change" method gets called frequently within itself while updating content. For example:

CB_Company.Clear

实际上将导致整个方法再次被调用.在某些时候，Excel会停止处理这些内容，并留下一个空白框.

will actually cause this entire method to get called again. At some point, Excel stops processing these, leaving a blank box.

您应该只在此模块的顶部添加一个全局变量:

You should simply include a global variable like at the top of this module:

Public updatingContent As Boolean

然后，在此子标题的开头包括:

Then, at the beginning of this sub include:

If updatingContent Then Exit Sub
updatingContent = True

这将防止呼叫链接.

最后，在此处理程序的末尾，请确保包括:

Last, at the end of this handler, make sure to include:

updatingContent = False

重置您的方法.

这是您所做的更改的代码.

Here's your code with those changes.

Public updatingContent As Boolean
Private Sub CB_Company_Change()

    If updatingContent Then Exit Sub
    updatingContent = True

    With Application
         .Calculation = xlManual
         .EnableEvents = False
         .ScreenUpdating = False
     End With

    Dim stSQL As String, sBook As String, rst As ADODB.Recordset, k As Integer, vaData As Variant
    Dim objConn As ADODB.Connection
    Dim ConnectionString As String
    ConnectionString = "Provider=sqloledb.1;data source=sql-server;Initial catalog=sql-db;Integrated Security = SSPI;"

    Set objConn = New ADODB.Connection

    sBook = CB_Book.value
    CB_Company.Clear

    stSQL = "EXEC('SELECT Name FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.ID WHERE TABLE2.NAME = ''" & sBook & "''  ORDER BY TABLE1.NAME')"

    With objConn
        .CursorLocation = adUseClient
        .Open ConnectionString
        Set rst = .Execute(stSQL)
    End With

    With rst
        Set .ActiveConnection = Nothing
        k = .fields.count
        vaData = .GetRows
    End With

    CB_Company.List = Application.Transpose(vaData)

    objConn.Close

    Set rst = Nothing
    Set objConn = Nothing

    bClear = True

    With Application
        .Calculation = xlAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    updatingContent = False
End Sub

这篇关于使用SQL数据自动填充组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！