additem issue with populating a combobox

Hot Topic

vb in excel - on data modified?
Swap Visual Studio Team Edition for Software Developers for Software Tester Edition
(Vitual IP Problem ) Web Farming using NLB in windows server 2003 Enterprise Edition
How can I change the font of some of the text in a cell? (Excel)
The official welcome post
Problem with VBA script
.NEt and industrial automation
Form Show Problem
Open a Word Document
Working with Outlook Public Folders from Excel

Microsoft ISV

Find & Replace Macro - Reading from an external database..
Generating a Printable Document based on Styles.
custom toolbar error
VBA for Powerpoint
VBA for Powerpoint
Problems with DAO and SQL Server
Hi, Outlook new Mail item
working with 2 workbooks
Read data from excel through VB.NET
Form Requery

additem issue with populating a combobox

Hello, I have been working at this for days and am no better off. I could really use some help.
I need to populate a combobox with data from a column in an SQL table. I have put together the code below but am having an error at this line:

invBegNum.additem rs.Fields.Item("RMDNUMWK").Value

Error message:

"Object doesn't support this property or method"

I'm really a newb but am trying hard to get this.  When I write this line of code to populate the invBegNum combobox, intellisence does not list .additem as a method. I believe that this is the root of my problem. So how do you populate the combobox from a SQL source with VBA 6.3 if the additem method is not available

 Here is the code. Please help.

visual basic code:

Sub getInvData()
Dim conn As ADODB.Connection
Dim connString As String

conString = "Provider=sqloledb;Data Source=localhost;Initial Catalog=BBTN;User Id=scott;Password=bigdaddy"

Set conn = New ADODB.Connection
conn.ConnectionString = conString
conn.Open connString

Dim rs As ADODB.RecordSet
Dim sSQL As String

Set rs = New ADODB.RecordSet
sSQL = "select RMDNUMWK from SV00564 order by RMDNUMWK"
rs.Open sSQL, conn

Do Until rs.EOF
invBegNum.additem rs.Fields.Item("RMDNUMWK").Value
rs.MoveNext
Loop
rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing
    
End Sub



Answer this question

additem issue with populating a combobox

  • Shoven

    try this

    Dim stDB As String, stConn As String, stSQL As String 
  Dim xlCalc As XlCalculation 
  Dim vaData As Variant 
  Dim k As Long 
   
   'In order to increase the performance.
  With Application 
    xlCalc = .Calculation 
    .Calculation = xlCalculationManual 
    .EnableEvents = False 
    .ScreenUpdating = False 
  End With 
   
   'Instantiate the Connectionobject.
  Set cnt = New ADODB.Connection 
   
   'Path to and the name of the database.
  stDB = ThisWorkbook.Path & "\" & "Test.mdb" 
   
   'Create the connectionstring.
  stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
  "Data Source=" & stDB & ";" 
   
   'Create the SQL-statement.
  stSQL = "SELECT * FROM tblData" 
   
  With cnt 
    .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
    .Open stConn 'Open connection.
     'Instantiate the Recordsetobject and execute the SQL-state.
    Set rst = .Execute(stSQL) 
  End With 
   
  With rst 
    Set .ActiveConnection = Nothing 'Disconnect the recordset.
    k = .Fields.Count 
     'Populate the array with the whole recordset.
    vaData = .GetRows 
  End With 
   
   'Close the connection.
  cnt.Close 
   
   'Manipulate the Combobox's properties and show the form.
  With frmData 
    With .ComboBox1 
      .Clear 
      .BoundColumn = k 
      .List = Application.Transpose(vaData) 
      .ListIndex = -1 
    End With 
    .Show vbModeless 
  End With 
   
   'Restore the settings.
  With Application 
    .Calculation = xlCalc 
    .EnableEvents = True 
    .ScreenUpdating = True 
  End With 
   
   'Release objects from memory.
  Set rst = Nothing 
  Set cnt = Nothing 
End Sub

     I know it's Access as oposed to SQL Server, but the concept is the same.



  • Amanda Song

    I'm not sure if this is the same type of resolution that Will just posted, but here's what I got back from our support engineer...

    ====

    Private Sub UserForm_Initialize()

    Set cnMSSQL = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    sConnect = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;pwd=password;Initial Catalog=test;Data Source=localhost"
    cnMSSQL.Open sConnect
    sSQL = "select ColumeName from ColumeList"
    rs.Open sSQL, cnMSSQL, 1, 1

    Do Until rs.EOF
    ComboBox1.AddItem rs("ColumeName").Value
    rs.MoveNext
    Loop

    End Sub
    =====

    -brenda (ISV Buddy Team)



  • additem issue with populating a combobox
Answer this question