Fill a combobox 
Author Message
 Fill a combobox

Hi, I have a form with a combobox. I want to read a table from a database
and fill the combobox. How can I do it with vba?

Alexander



Fri, 28 Nov 2003 22:59:26 GMT  
 Fill a combobox
Hi Alexander...

You didn't mention what version of Office you're using. So assuming
you have Office 2000...you can use ADO (ActiveX Data Object) to
connect to a database. From there, you'd query the database to extract
the fields you need and place them into a RecordSet. Then use the data
in that RecordSet to populate the ComboBox.

NOTE...if you're using a prior version to Office 2000...you'll need to
use DAO (Data Access Object) or RDO (Remote Data Object). Sorry to say
I only started learning this stuff myself...so I can't help you with
previous details. Either someone else here can...or you can look up
the details from www.microsoft.com. The basics should be the same.

Here's some sample code from a Word form I have. The code is in a
command button, so that when the command button is clicked...the
ComboBox is populated with the latest first/last names of those in the
DB. NOT quite what you want...but you should get the idea from this.

You can get more details about using ADO either in the ADO newsgroup
or at http://www.microsoft.com/data/.

Hope this helps!

*************************
'Note that the first thing you need to do is reference ADO by clicking
Tools/Reference and selecting the ADO library.
*************************
Private Sub cmdFill_FromDB_Click()

'Make declarations
    Dim vConnection As New ADODB.Connection
    Dim vRecSetNum as New ADODB.Recordset
    Dim vRecordSet As New ADODB.Recordset
    Dim vRecordCount As Long
    Dim vName as String

'Connection string using Jet agains an Access DB
'Note that other *providers* are available for SQL and other
'databases
      vConnection.ConnectionString =
          "data source=d:\dataForms\test.mdb;" & _
          "Provider=Microsoft.Jet.OLEDB.4.0;"
      vConnection.Open

'This optional message box will help you verify the connection
        vConnectionState = vConnection.State
        If vConnectionState = 1 Then
            MsgBox "The connection to this database is working!"
        Else
            MsgBox "You were unable to connect."
        End If

'Creates first record set to hold COUNT from Staff DB
      vRecSetNum.Open "SELECT Count(*) FROM Staff", vConnection

'Fills vRecordCount variable with record count
      vRecordCount = vRecSetNum(0)

'Optional message box to show count
        MsgBox vRecordCount

'Fills the vRecordSet with all fields from Staff DB and
'orders the list alphabetically by last name
      vRecordSet.Open "SELECT * FROM Staff ORDER BY [LName]", _
vConnection

'Clears current list in box
      ComboBox1.Clear

'From first to last record, pulls and joins First & Last name
'and adds them  to the ComboBox
      For i = 1 To vRecordCount
        vName = vRecordSet.Fields("FName").Value & _
          " " & vRecordSet.Fields("LName").Value
        ComboBox1.AddItem vName
        vRecordSet.MoveNext
      Next

'This is just default text as the first entry (idiot proofing<g>)
      ComboBox1.Text = "<click down arrow to pick your name>"

'Cleans up the mess
      vRecordSet.Close
      vReSetNum.Close
      vConnection.Close

End Sub

So...unless I screwed something up posting this code...it should work
for you. You can test it by placing a ComboBox1 on a Word2000 doc and
adding a Command button called cmdFill_FromDB and then accessing some
Access 2000 database (be sure to adjust the path of your DB from what
mine is in the connection string.)

Good luck...

Dian Chesney
Technical Consultant
& Microsoft MVP

More tips: www.mousetrax.com
Word MVP FAQs: http://www.mvps.org/word/index.html
MS newsgroups via browser: http://communities.microsoft.com/newsgroups



Quote:
>Hi, I have a form with a combobox. I want to read a table from a database
>and fill the combobox. How can I do it with vba?

>Alexander



Sat, 29 Nov 2003 06:37:18 GMT  
 Fill a combobox
Sorry, I have a template that is a form, then I want to update a listbox
from VBA, but the listbox is in the template. It is not an userform.

Alexander.



Quote:
> Hi Alexander...

> You didn't mention what version of Office you're using. So assuming
> you have Office 2000...you can use ADO (ActiveX Data Object) to
> connect to a database. From there, you'd query the database to extract
> the fields you need and place them into a RecordSet. Then use the data
> in that RecordSet to populate the ComboBox.

> NOTE...if you're using a prior version to Office 2000...you'll need to
> use DAO (Data Access Object) or RDO (Remote Data Object). Sorry to say
> I only started learning this stuff myself...so I can't help you with
> previous details. Either someone else here can...or you can look up
> the details from www.microsoft.com. The basics should be the same.

> Here's some sample code from a Word form I have. The code is in a
> command button, so that when the command button is clicked...the
> ComboBox is populated with the latest first/last names of those in the
> DB. NOT quite what you want...but you should get the idea from this.

> You can get more details about using ADO either in the ADO newsgroup
> or at http://www.microsoft.com/data/.

> Hope this helps!

> *************************
> 'Note that the first thing you need to do is reference ADO by clicking
> Tools/Reference and selecting the ADO library.
> *************************
> Private Sub cmdFill_FromDB_Click()

> 'Make declarations
>     Dim vConnection As New ADODB.Connection
>     Dim vRecSetNum as New ADODB.Recordset
>     Dim vRecordSet As New ADODB.Recordset
>     Dim vRecordCount As Long
>     Dim vName as String

> 'Connection string using Jet agains an Access DB
> 'Note that other *providers* are available for SQL and other
> 'databases
>       vConnection.ConnectionString =
>           "data source=d:\dataForms\test.mdb;" & _
>           "Provider=Microsoft.Jet.OLEDB.4.0;"
>       vConnection.Open

> 'This optional message box will help you verify the connection
>         vConnectionState = vConnection.State
>         If vConnectionState = 1 Then
>             MsgBox "The connection to this database is working!"
>         Else
>             MsgBox "You were unable to connect."
>         End If

> 'Creates first record set to hold COUNT from Staff DB
>       vRecSetNum.Open "SELECT Count(*) FROM Staff", vConnection

> 'Fills vRecordCount variable with record count
>       vRecordCount = vRecSetNum(0)

> 'Optional message box to show count
>         MsgBox vRecordCount

> 'Fills the vRecordSet with all fields from Staff DB and
> 'orders the list alphabetically by last name
>       vRecordSet.Open "SELECT * FROM Staff ORDER BY [LName]", _
> vConnection

> 'Clears current list in box
>       ComboBox1.Clear

> 'From first to last record, pulls and joins First & Last name
> 'and adds them  to the ComboBox
>       For i = 1 To vRecordCount
>         vName = vRecordSet.Fields("FName").Value & _
>           " " & vRecordSet.Fields("LName").Value
>         ComboBox1.AddItem vName
>         vRecordSet.MoveNext
>       Next

> 'This is just default text as the first entry (idiot proofing<g>)
>       ComboBox1.Text = "<click down arrow to pick your name>"

> 'Cleans up the mess
>       vRecordSet.Close
>       vReSetNum.Close
>       vConnection.Close

> End Sub

> So...unless I screwed something up posting this code...it should work
> for you. You can test it by placing a ComboBox1 on a Word2000 doc and
> adding a Command button called cmdFill_FromDB and then accessing some
> Access 2000 database (be sure to adjust the path of your DB from what
> mine is in the connection string.)

> Good luck...

> Dian Chesney
> Technical Consultant
> & Microsoft MVP

> More tips: www.mousetrax.com
> Word MVP FAQs: http://www.mvps.org/word/index.html
> MS newsgroups via browser: http://communities.microsoft.com/newsgroups



> >Hi, I have a form with a combobox. I want to read a table from a database
> >and fill the combobox. How can I do it with vba?

> >Alexander



Sat, 29 Nov 2003 14:50:32 GMT  
 Fill a combobox
Then this code will work even BETTER for you because THAT is what I
had when I created this code...except for the fact that I use a
ComboBox versus a ListBox and I use a Command button to activate the
connection and box population.

So just convert the ComboBox add details to ListBox add...Press F1
when in the VBE (VB Editor) to get help on the exact syntax for the
ListBox1 code (believe it's just like ComboBox). Then just add this
stuff into the templates document initiation action so the list is
populated when a new doc is created from the dot.

Dian Chesney
Technical Consultant
& Microsoft MVP

More tips: www.mousetrax.com
Word MVP FAQs: http://www.mvps.org/word/index.html
MS newsgroups via browser: http://communities.microsoft.com/newsgroups



Quote:
>Sorry, I have a template that is a form, then I want to update a listbox
>from vba, but the listbox is in the template. It is not an userform.

>Alexander.



Sat, 29 Nov 2003 23:51:02 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Programatically fill a combobox A2K ??

2. Filling a comboBox with ODBC-data

3. Filling a ComboBox with data from DB

4. Fill List/Combobox

5. Best way to fill a ComboBox from SQL Table

6. Fill a ComboBox

7. Stored procedure filling a combobox

8. need to fill a combobox from a table

9. need to fill a combobox from a table

10. Filling a combobox with decimal numbers

11. How to fill a combobox VB4

12. Fill a combobox

 

 
Powered by phpBB® Forum Software