Display fields from an Access database table in a dropdown combo box

[33216CLC]

How can I get fields from an access database to be displayed in a drop down combo box?

Thanks,

Cooleen [sig][/sig]

 

[swilliams]

There are a variety of ways to do this:

1. From a data control (not preferred or recommended)
Place a data control on your form and set the databasename and recordsource properties to access your database.
Next, place a combo box on your form and set the datasource property to the data control (from a drop down list).
Next, choose the field you want displayed in the combo box by setting the datafield property.

2. From a recordset in code (recommended)
place a combo box on your form and then copy and paste the following code into the general declarations section of your form:

Private Sub Fill_Combo(cboBox As ComboBox, ByVal sTable As String, ByVal sDisplayField As String)

Dim db As Database
Dim rs As Recordset

Set db = Workspaces(0).OpenDatabase("c:\aa\newdata.mdb", False, False)
Set rs = db.OpenRecordset("SELECT DISTINCT " & sDisplayField & " FROM " & sTable & " ORDER BY " & sDisplayField, dbOpenSnapshot)
cboBox.Clear
Do Until rs.EOF
cboBox.AddItem rs(sDisplayField)
rs.MoveNext
Loop
rs.Close
Set db = Nothing

End Sub

And then call the subroutine by having the line:

Fill_Combo Combo1, "TableName", "FieldName"

anywhere in your code - ie put this onload of the form, onclick of a button,....

Simon [sig][/sig]

 

[33216CLC]

Thanks Simon,

I'll try your suggestion.

Cooleen [sig][/sig]

 

[33216CLC]

Hi Simon,

I tried your suggestion using the code below:

Private Sub fillCombo(cbobox As SSDBCombo, ByVal sTable As String, ByVal sField As String)
Dim db As Database
rs As Recordset

Set db = Workspaces(0).OpenDatabase(MyDbPath, False, False, "; pwd=data&quot

Set rs = db.OpenRecordset("select distinct" & sField & " from " & sTable & " order by " & _
sField, dbOpenSnapshot)

While Not rs.EOF
cbobox.AddItem rs(sField)
rs.MoveNext
Wend
rs.Close
Set db = Nothing

End Sub

Private Sub cmdopen_Click()

Call fillCombo(SSDBCombo2, "Fields", "Name&quot

End Sub


When I click the OPEN button, I get a type mismatch error

I double checked everthing and as far as I can see, Everything is declared properly. Do you see any any reason why I would get this error message?

Thanks,

Cooleen
[sig][/sig]

 

[swilliams]

In the sub fillCombo, the first argument should be

cboBox as ComboBox

You are just specifying that the type of the thing being passed is a combo box, and then in the call to the sub you are passing the correct combo box to the routine.

If you make the following change it should work.

Simon
PS. check that in the openrecordset call, you have a space after distinct, inside the quotes, otherwise you will get other errors. [sig][/sig]

 

[33216CLC]

Hi Simon, here is the code with my changes:

Private Sub fillCombo(cbobox As ComboBox, ByVal sTable As String, ByVal sField As String)
Dim db As Database
Dim rs As Recordset

Set db = Workspaces(0).OpenDatabase(MyDbPath, False, False, "; pwd=data&quot

Set rs = db.OpenRecordset("SELECT DISTINCT " & sField & " from " & sTable & " order by " & sField, dbOpenSnapshot)

While Not rs.EOF
cbobox.AddItem rs(sField)
rs.MoveNext
Wend
rs.Close
Set db = Nothing

End Sub

Private Sub cmdopen_Click()

Call fillCombo(Combo1, "fields", "Name&quot

End sub

The Code in bold is invoking the error.
[sig][/sig]

 

[33216CLC]

I'm sorry, Simon, I forgot to state that the error invoked is still a type mismatch error.

Thanks,

Cooleen [sig][/sig]

 

[33216CLC]

Hi Simon,

I found that I was getting that error because one of the necessary *.tlb files were not selected as a reference in my project. The progam is now accessing the database.

Thank you,

Cooleen [sig][/sig]

 

[swilliams]

Cool - good to here it.

Simon [sig]<p>Simon<br>[/sig]

 

[chrissine]

Cooleen,

I was watching this discussion as I have a simillar problem and ran into the same error. In my application I am populating a spreadsheet, though. Could you tell me what references you were missing? Maybe that was my problem too.

Thanks,

Chris [sig][/sig]

 

[33216CLC]

Chris,

Here are the references that I was missing:

msado20.tlb (Microsoft ActiveX Data Objects 2.0 Library)
msderun.dll (Microsoft Data Environment Instance 1.0)

If these are not the ones you need, perhaps you should skim through the sample programs(that come with VB) for an application that is similar to yours. Open it in VB and compare the references that it uses with your references.

Wish you the best,

Cooleen [sig][/sig]

 

[chrissine]

Thanks Cooleen,

I ended up going about it in an entirely different way. But thanks for the info.

Chris

 

[33216CLC]

No problem, Chris.

Cooleen

 

[HebieBug]

Cooleen,
Am working on something very similar to the problem that you had last year. If you could help me out with just one line of code
Set dv = Workspaces(0)
It highlights the Workspaces and comes up with the error message
Compiler error:
Sub or function not defined
Using VB 6.0 have looked through the Object library with no reference equalling workspaces
Did you have any problems with this line or do you know the answer.
Kind regards
Henry

 

[33216CLC]

Hi Henry,

Try the following;

Set dv = DBEngine.Workspaces(0)

Hope this helps,

Cooleen

 

[HebieBug]

Hi there Cooleen,
Have just come back from Freaking out in India for the last two weeks. Can't find the copy of the VB form that I had altered based on this thread.
If you still have a copy of something fitting this thread could you send it to me
Henrymaher@yahoo.co.uk
Thanx
Henry

 

[33216CLC]

I'll check.