The Combo-box with the Database?

[rave3085]

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?????????????

[SanjuMtr]

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.

Dim rsTemp As New ADODB.Recordset
 Dim conTemp As New ADODB.Connection
 
 If rsTemp.State = adStateOpen Then rsTemp.Close
 rsTemp.Open "Select vendorname from vendors ", conTemp, adOpenDynamic, adLockOptimistic
 while rstemp.eof<>true
    combo1.additem rstemp.fields("vendorname").values
    rstemp.movenext
 wend
if combo1.listcount>0 then combo1.listindex=0
 

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.

[Killer42]

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.

[rave3085]

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!

[rave3085]

pls do help!!!!!

[Killer42]

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.

[rave3085]

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

Sub CVenName_Click()
Dim dvenam As Variant
Dim dvenid As Variant
Dim idv As New ADODB.Recordset
Dim idb As New ADODB.Connection
 
dvenam = CVenName.Text
Set idv = New ADODB.Recordset
Set idb = New ADODB.Connection
 
idb.CursorLocation = adUseClient
idb.Open "PROVIDER=Microsoft.jet.OLeDB.3.51;Data Source=" & App.Path & "\GasDb.mdb;"
idv.Open "SELECT VendorId as dvenid from vendors where VendorName='" & dvenam & "'", idb, adOpenDynamic, adLockOptimistic
CVenId.Text = dvenid
 
End Sub

[Killer42]

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

Sub CVenName_Click()
Dim dvenam As Variant
Dim dvenid As Variant
Dim idv As New ADODB.Recordset
Dim idb As New ADODB.Connection
 
dvenam = CVenName.Text
Set idv = New ADODB.Recordset
Set idb = New ADODB.Connection
 
idb.CursorLocation = adUseClient
idb.Open "PROVIDER=Microsoft.jet.OLeDB.3.51;Data Source=" & App.Path & "\GasDb.mdb;"
idv.Open "SELECT VendorId as dvenid from vendors where VendorName='" & dvenam & "'", idb, adOpenDynamic, adLockOptimistic
CVenId.Text = dvenid
 
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.

[rave3085]

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

thanx for answering my silly things there!!!!!!!!