Populate Access combo box with SQL values using VBA 1

[vamoose]

I have been working on this since yesterday and am getting closer now with this code. This code runs through all 600 SQL records and populates the combo box just fine but only displays one combo box result at a time. Could someone please help me with whatever is missing to get all 600 records to accumulate in the combo box list ?

SQLb = "SELECT Coil FROM CoilSpec.dbo.coilspec"
Set CN = New ADODB.Connection: CN.Open "driver={SQL Server};" & _
"server=172.16.6.172;database=coilspec;uid=dtuttle;password=mpmadmin"
Set rs = CN.Execute(SQLb, , adCmdText)

If Not (rs.EOF) Then
rs.MoveFirst
While Not rs.EOF
cmbWCoilNo = rs.Fields("Coil")
rs.MoveNext
Wend
End If

rs.Close: CN.Close: Set rs = Nothing: Set CN = Nothing: Exit Sub

Thank you for your patience and assistance, I am new at this and appreciate the help.

 

[1DMF]

if you are trying to populate a combo box with the DB data as drop down selections you need to clear the form and add each item...

' this removes any current values
i = 0

Do While i < cmbWCoilNo.ListCount
    cmbWCoilNo.RemoveItem Index:=i
    i = i + 1
Loop

SQLb = "SELECT Coil FROM CoilSpec.dbo.coilspec"
Set CN = New ADODB.Connection: CN.Open "driver={SQL Server};" & _
        "server=172.16.6.172;database=coilspec;uid=dtuttle;password=mpmadmin"
    Set rs = CN.Execute(SQLb, , adCmdText)
    
    If Not (rs.EOF) Then
        rs.MoveFirst
            While Not rs.EOF
            cmbWCoilNo.AddItem Item:=Chr(34) & rs.Fields("Coil") & Chr(34)
        rs.MoveNext
        Wend
    End If
    
       rs.Close: CN.Close: Set rs = Nothing: Set CN = Nothing: Exit Sub

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[vamoose]

I have been trying to use the .AddItem but it keeps telling me: "Compile error: Method or data member not found" Any ideas ?

 

[1DMF]

is cmbWCoilNo a combo box on the form?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[vamoose]

Yes, cmbWCoilNo is the name of the combo box on the form frm_start which is the form set to open when Access is opened. Thanks

 

[1DMF]

if the code is on the form with the combo box try

Me.ControlName.AddItem

or if not on the form...

[Forms]![FormName].[ControlName].AddItem

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[vamoose]

When I start typing in the .AddItem it does not show up in the pop-up listing of available options to select from. Is this correct ? or maybe I am missing a reference ?

 

[1DMF]

are you sure that the control on the form is an UNBOUND COMBO BOX ? , what version of MSAccess are you using, this works fine for me - very odd

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[vamoose]

Combo Box cmbWCoilNo on frm_start is Unbound. Using MSAccess 2000

 

[1DMF]

maybe you can't use that method in MS Access 2000, I'm using 2003!

is there no way you could make the source a query against the table?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[1DMF]

this might help

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office03022000.asp

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[vamoose]

Using MSAccess 2000. What about this ? I have the frm_start combo box cmbWCoilNo Row Source Type set to Value List. The following code populates the variable strCombo with all 600 values but now gives me an error of: "The setting for this property is too long" Any ways around this, or any other possible ideas ? Thanks for looking.

Private Sub Form_Open(Cancel As Integer)
Dim CN As ADODB.Connection: Dim rs As ADODB.Recordset
stDocName = "frm_start": DoCmd.OpenForm stDocName, , , stLinkCriteria: DoCmd.Maximize

SQLb = "SELECT Coil FROM CoilSpec.dbo.coilspec"
Set CN = New ADODB.Connection: CN.Open "driver={SQL Server};" & _
"server=172.16.6.172;database=CoilSpec;uid=dtuttle;password=mpmadmin"
Set rs = CN.Execute(SQLb, , adCmdText)

Me.cmbWCoilNo.RowSource = ""
Dim strCombo
strCombo = ""

While Not rs.EOF
strCombo = strCombo + rs.Fields("Coil") & ";"
rs.MoveNext
Wend
Me!cmbWCoilNo.RowSource = strCombo

rs.Close: CN.Close: Set rs = Nothing: Set CN = Nothing

End Sub

 

[hwkranger]

Perhaps you can just use a PASS THROUGH query as the source of the combo instead?

I use value lists for short lists similar to what you're doing.

for longer lists, I use Pass through queries....

Short list example:

Private Sub LoadAssignedCombo()
    Dim objUser As New clsUser
    Me.cmbAssignedXID.RowSource = objUser.FetchListBoxViewRecordset("tblUser.RecievesProjects=True").GetString(, , ";", ";")
End Sub

where FetchListBoxViewRecordset is defined as:

Public Function FetchListBoxViewRecordset(Optional strWhere As String) As ADODB.Recordset
    Dim rst As New ADODB.Recordset
    rst.Open GetFetchListBoxViewSQL(strWhere), cn, adOpenKeyset, adLockOptimistic
    Set FetchListBoxViewRecordset = rst
Call Me.LogAction(10, CLASS_NAME & "Item: " & adtbiztblUser_DATA.mstrXID, , , "Fetched All Records")
End Function

Private Function GetFetchListBoxViewSQL(Optional strWhere As String) As String
    Dim strSQL As String
    strSQL = strSQL & "SELECT tblUser.XID, tblUser.Display_Name FROM " & DATA_ACCESS & vbCrLf
    If Len(strWhere) > 0 Then
        strSQL = strSQL & "Where " & strWhere & vbCrLf
    End If
    GetFetchListBoxViewSQL = strSQL
End Function

For longer lists though, I just set the row source to a PASS THROUGH query.. (which works the same as a regular query, but you're linking via ODBC or DSN-less Connection)




Randall Vollen
National City Bank Corp.

 

[vamoose]

Please help with creating a DSN-Less connection from Access 2000 to a SQL 2000 database. I create a new Query and select SQL specific and Pass-Through and then there is a blank white page to work from. Is this where I define the connection string and perform the query both. I am unfamiliar with a Pass-Through query. Any assistance is greatly appreciated. Thanks

Connection string:

Dim CN As ADODB.Connection: Dim rs As ADODB.Recordset
Set CN = New ADODB.Connection: CN.Open "driver={SQL Server};" & _
"server=172.16.6.172;database=CoilSpec;uid=dtuttle;password=mpmadmin"

This query to populate the unbound frm_start combo box cmbWCoilNo:
SQLb = "SELECT Coil FROM CoilSpec.dbo.coilspec"

 

[hwkranger]

RIGHT CLICK at the top of the "blank window" and it will have a properties setting.

Change the ODBC connect str to the format of:

ODBC;driver={SQL Server};server=OHCLESQLXXX;database=DAISYXXX;Trusted_Connection=Yes;

Fill your data in there.

In the WHITE space of the window, put your query.



Randall Vollen
National City Bank Corp.

 

[1DMF]

remember though that will only work for trusted accounts within the domain and not for people on a separate domain or external via VPN.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.