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

The Combo-box with the Database?

P: 27
I'm using the access database and in my form i have a combo-box for which i need to make a particular field's data appear in its drop-down list..
Say..

I have the "vendorname" field in the table "vendors". Now i need to add all the contents of this field to the combo-box by name say "VenName".

How should i do this! Pls explain?????????????
May 1 '07 #1
Share this Question
Share on Google+
8 Replies


P: 47
I'm using the access database and in my form i have a combo-box for which i need to make a particular field's data appear in its drop-down list..
Say..

I have the "vendorname" field in the table "vendors". Now i need to add all the contents of this field to the combo-box by name say "VenName".

How should i do this! Pls explain?????????????
Ok Frnd
it is not so hard. to Populate Data in a Combo.
I am giving U a simple pieces of code to demonstrate how to populate data in combo/list box.
Expand|Select|Wrap|Line Numbers
  1. Dim rsTemp As New ADODB.Recordset
  2.  Dim conTemp As New ADODB.Connection
  3.  
  4.  If rsTemp.State = adStateOpen Then rsTemp.Close
  5.  rsTemp.Open "Select vendorname from vendors ", conTemp, adOpenDynamic, adLockOptimistic
  6.  while rstemp.eof<>true
  7.     combo1.additem rstemp.fields("vendorname").values
  8.     rstemp.movenext
  9.  wend
  10. if combo1.listcount>0 then combo1.listindex=0
  11.  
N.B : I have written this pieces of code in this editor rather than VB IDE. So Some Spelling may be Wrong.Plz Check it carefully.But the moralness concept is same.
May 1 '07 #2

Expert 5K+
P: 8,434
If you have a data control on your form, it might be simpler to add the "Microsoft Data Bound List Control" to your project and use the DBCombo control. This allows you to specify the field which provides the dropdown list.
May 1 '07 #3

P: 27
yeah i actually got this! thnx, but now

I actually i have a vendorid for every vendor in the database! So in my, say purchases form- when the user selects a particular vendor from the combo-box, i want the respected vendorid to appear in the textbox by name say, cvenid.text.

I tried quite a few but as being a newbie to vb i couldn't able get!
So your kind help is needed!

Also i jus wanna know if the way im thinking is preferred in enterprise applications or not? if yu feel anyother ways of doing in a more customized way pls do tell!
May 2 '07 #4

P: 27
pls do help!!!!!
May 2 '07 #5

Expert 5K+
P: 8,434
Sounds as though the simplest thing then might be to use a regular combo box and at startup, create a database connection, read all the vendors and load them into the combobox. (Which is probably what you already said.:))

In the Click or Change event for the Combobox, use another database connection to retrieve the vendor's details and display the ID.

I think you're on the right track.
May 2 '07 #6

P: 27
thanx for ur advice and as u said, i tried out something like this! Do check this for the error im getting!

Expand|Select|Wrap|Line Numbers
  1. Sub CVenName_Click()
  2. Dim dvenam As Variant
  3. Dim dvenid As Variant
  4. Dim idv As New ADODB.Recordset
  5. Dim idb As New ADODB.Connection
  6.  
  7. dvenam = CVenName.Text
  8. Set idv = New ADODB.Recordset
  9. Set idb = New ADODB.Connection
  10.  
  11. idb.CursorLocation = adUseClient
  12. idb.Open "PROVIDER=Microsoft.jet.OLeDB.3.51;Data Source=" & App.Path & "\GasDb.mdb;"
  13. idv.Open "SELECT VendorId as dvenid from vendors where VendorName='" & dvenam & "'", idb, adOpenDynamic, adLockOptimistic
  14. CVenId.Text = dvenid
  15.  
  16. End Sub
May 2 '07 #7

Expert 5K+
P: 8,434
thanx for ur advice and as u said, i tried out something like this! Do check this for the error im getting!

Expand|Select|Wrap|Line Numbers
  1. Sub CVenName_Click()
  2. Dim dvenam As Variant
  3. Dim dvenid As Variant
  4. Dim idv As New ADODB.Recordset
  5. Dim idb As New ADODB.Connection
  6.  
  7. dvenam = CVenName.Text
  8. Set idv = New ADODB.Recordset
  9. Set idb = New ADODB.Connection
  10.  
  11. idb.CursorLocation = adUseClient
  12. idb.Open "PROVIDER=Microsoft.jet.OLeDB.3.51;Data Source=" & App.Path & "\GasDb.mdb;"
  13. idv.Open "SELECT VendorId as dvenid from vendors where VendorName='" & dvenam & "'", idb, adOpenDynamic, adLockOptimistic
  14. CVenId.Text = dvenid
  15.  
  16. End Sub
You didn't way what error you are getting. But I think I see a misconception. You are saying "Select VendorId as dvenid" and then it looks as though you expect the field value to magically appear in the variable of the same name (dvenid). Unless I'm behind the times, there is no connection between the two. All the "AS" clause does is change the field name returned in the query. If you want the field value in a variable, you have to move it there. So your second-last line probably should be something like...
CVenId.Text = idv!dvenid
or
CVenId.Text = idv("dvenid")
In fact, there's probably not much point in renaming the VendorId field at all.
May 2 '07 #8

P: 27
yeah u were absolutely correct, i made an idiotic expectation there!

thanx for answering my silly things there!!!!!!!!
May 2 '07 #9

Post your reply

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