Sql table to Combo Box...

[anbusds]

Hi All,
I Would Like to Look up SQL server 2005 table as Tbl1 to my access combo box as combo 1
Total 2 columns in the SQL table as Item & Qty
Please Post any sample Code...

Thanks in Advance!!!

 

[Galaxiom]

Code is not required. Simply link the table via ODBC using the entry on the External Data ribbon and use it as a RowSource just as you would any other table.

 

[anbusds]

Hi,
I tried this code the combobox just became empty,
Anything need to add in?

Dim conn As New ADODB.Connection
Dim objMyRecordset As New ADODB.Recordset

Dim strSQL As String

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider = SQLOLEDB; Data Source=SDSACCOUNT; Initial Catalog= SSPLCO; user ID=sa; Password=sa;"
conn.Open
'Do your query

strSQL = "Select ITEMNO,qty FROM ICITEM;"

Set objMyRecordset.ActiveConnection = conn
objMyRecordset.Open strSQL
Me.Combo4.RowSourceType = "Table/Query"

Thanks!!!

 

[Galaxiom]

Oh I didn't realise it was you again.

Like I told you the first time you need this line:

Set Me.Combo4.Recordset = objMyRecordset

This line needs to go after you open the recordset, not before.

 

[anbusds]

Hi,
could you please paste the code for me? i tried also not able to look up...
Thanks!!!

 

[Galaxiom]

Just add the line I posted to the end of your procedure code.

If it still comes up blank then:

Presumably the recordset is being created or you would get an error when you run the code (unless you have disabled errors being raised with On Error Resume Next).

If you are not getting any errors but still have an empty combo you can check the recordset has records by putting a Break Point after the open command.

When it Breaks, view the recordset properties in the VBA Locals Window. Look at its record count property. You can also check its Fields Collection for values in the records.

(Turn the Locals window on under the View Menu. Click to the left of the code line to insert a Break Point.)

However why you don't simply link the table via ODBC and use the Access Wizard to make the combo? Whenever you need to get any new values simply Requery the combo.