By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,325 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,325 IT Pros & Developers. It's quick & easy.

Populate Access 2000 combo box with SQL data using VBA

P: 1
Using MSAccess 2000. I have the frm_start combo box unbound and cmbWCoilNo Row Source Type set to Value List. The following code populates the variable strCombo with some values but gives me an error of: "The setting for this property is too long" Any ways around this, or any other possible ideas ? Have also tried .AddItem but this does not seem to work in Access 2000. I am trying to populate the combo box with 600 Primary Key values of 4 digits each. 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
Sep 13 '06 #1
Share this Question
Share on Google+
1 Reply

PEB
Expert 100+
P: 1,418
PEB
Hi,

Why do not try to connect the combo box directly to your source table instaed to put the values one by one cycling on the recordset?

Have a nice day!

:)
Sep 14 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.