-
populating the combobox
hi,
does the combobox in vb work the same as in access.
i mean in vb 6.0 i have a combobox ;i want to retrieve all values in a field from database using ado programming into combobox, based on the value i select in the combobox i want the corresponding values in the textbox.
i know how to connect to database using ado programming and retrieve data from databse, but i am unable to populate combobox with data from database only one value is displayed in it.
it's very urgent plz help me
-
Re: populating the combobox
Quote:
Originally Posted by bsarika
hi,
does the combobox in vb work the same as in access.
i mean in vb 6.0 i have a combobox ;i want to retrieve all values in a field from database using ado programming into combobox, based on the value i select in the combobox i want the corresponding values in the textbox.
i know how to connect to database using ado programming and retrieve data from databse, but i am unable to populate combobox with data from database only one value is displayed in it.
it's very urgent plz help me
Once you have connected to your database, i assume an Access database and using the ADODB recordset
VB Code:
Private Sub Form_Load()
Do Until rs.EOF
List1.AddItem rs!FieldName
rs.MoveNext
Loop
End Sub
What this does is goes through each record in the recordset and adds it to the list box.
Hope this helps.
:)
-
Re: populating the combobox
thanks but it displays only true value in the listbox.
Private Sub Form_Load()
Set con = New Connection
Set rs = New Recordset
Set com = New Command
rs.CursorLocation = adUseServer
rs.LockType = adLockOptimistic
rs.CursorType = adOpenDynamic
With con
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=E:\swetha\finance.mdb;Persist Security Info=False"
.Open
End With
rs.Open "select * from ibs", con
Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set List1.DataSource = rs
Text1.DataField = "No"
Text2.DataField = "Datewriten"
List1.DataField = "description"
rs.MoveFirst
Do Until rs.EOF
List1.AddItem (List1.DataField = "description")
rs.MoveNext
Loop
end sub
-
Re: populating the combobox
Welcome to the forums.
I moved the thread from the Codebank. The codebank is only for posting code samples, not questions.
-
Re: populating the combobox
Change this section of code:
VB Code:
List1.DataField = "description"
rs.MoveFirst
Do Until rs.EOF
List1.AddItem (List1.DataField = "description")
rs.MoveNext
Loop
to what flukey2005 suggested, ie:
VB Code:
Do Until rs.EOF
List1.AddItem rs!description
rs.MoveNext
Loop
-
Re: populating the combobox
I'm attempting something virtually identical except wanting to populate a ListBox not a Combo and i keep getting the error 'Invalid use of New Keyword"
Im using vb6 / Access with ListBox / ADODC 6.0
Maybe I'm missing setting something crucial in the Properties? I've never had any success with ADO. Always same error. :-(
-
Re: populating the combobox
Quote:
Originally Posted by D43x
I'm attempting something virtually identical except wanting to populate a ListBox not a Combo and i keep getting the error 'Invalid use of New Keyword"
Im using vb6 / Access with ListBox / ADODC 6.0
Maybe I'm missing setting something crucial in the Properties? I've never had any success with ADO. Always same error. :-(
Post all of your connection code.
-
Re: populating the combobox
VB Code:
Private Sub Form_Load()
Set con = New Connection
Set rs = New Recordset
Set com = New Command
rs.CursorLocation = adUseServer
rs.LockType = adLockOptimistic
rs.CursorType = adOpenDynamic
With con
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\db1.mdb;Persist Security Info=False"
.Open
End With
rs.Open "select * from Table1", con
Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set Text3.DataSource = rs
Set List1.DataSource = rs
Text1.DataField = "Field1"
Text2.DataField = "Field2"
Text2.DataField = "Field3"
Do Until rs.EOF
List1.AddItem rs!Description
rs.MoveNext
Loop
End Sub
-
Re: populating the combobox
Ah, I see the problem. Try this connectino code instead. You don't have to use the same connection variables, but the syntax needs to be the same
VB Code:
Dim con As ADODB.Connection
Dim ConnString As String
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\db1.mdb;" & _
"Persist Security Info=False"
Set con = New ADODB.Connection
con.ConnectionString = ConnString
con.Open ConnString
You have to declare them before you use them. Using New on a variable that has not yet been declared will result in the error that you received.
-
Re: populating the combobox
Here's how it looks now, but have a new error (below)...
VB Code:
Private Sub Form_Load()
Dim con As ADODB.Connection
Dim ConnString As String
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\db1.mdb;" & _
"Persist Security Info=False"
Set con = New ADODB.Connection
con.ConnectionString = ConnString
con.Open ConnString
rs.Open "select * from Table1", con 'DEBUG - THE ERROR'S HERE
Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set Text3.DataSource = rs
Set List1.DataSource = rs
Text1.DataField = "Field1"
Text2.DataField = "Field2"
Text2.DataField = "Field3"
Do Until rs.EOF
List1.AddItem rs!Description
rs.MoveNext
Loop
End Sub
Error:
Object required.
PS. Why is there "con" and "conn"? 2 separate variables?
-
Re: populating the combobox
Quote:
Originally Posted by D43x
Here's how it looks now, but have a new error (below)...
VB Code:
Private Sub Form_Load()
Dim con As ADODB.Connection
Dim ConnString As String
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\db1.mdb;" & _
"Persist Security Info=False"
Set con = New ADODB.Connection
con.ConnectionString = ConnString
con.Open ConnString
rs.Open "select * from Table1", con 'DEBUG - THE ERROR'S HERE
Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set Text3.DataSource = rs
Set List1.DataSource = rs
Text1.DataField = "Field1"
Text2.DataField = "Field2"
Text2.DataField = "Field3"
Do Until rs.EOF
List1.AddItem rs!Description
rs.MoveNext
Loop
End Sub
Error:
Object required.
I'm guessing that the object that is being required is rs. I seen no place where rs is being declared.
Quote:
Originally Posted by D43x
PS. Why is there "con" and "conn"? 2 separate variables?
Where is conn?
-
Re: populating the combobox
You forgot:
Dim rs As ADODB.RecordSet
-
Re: populating the combobox
Hack - "conn" part of connString var.
Klauke - thanks you solved one error, but now the same line give this error...
"Runtime 91 - Object variable or With block variable not set".
Is the object variable the name of the table inside the db or a field name?
-
Re: populating the combobox
Did you placed the ADODC1 control on the form? I think that is the problem.
-
Re: populating the combobox
Radjesh Klauke is correct that you missed out the declaration of rs, but you also need to set the object (before the .Open line), eg:
Set rs = New ADODB.RecordSet
-
Re: populating the combobox
Could there be something else missing (or in the wrong order) as placing the "set rs." before the ".Open" still gives an error.
I've moved the lines about that often I don't know which error relates to what but there are 2 errors. The first relates to the data source not being found and highlights the following line:
The second error relates to Object variable not being set.
Here's my code as it stands. Can someone place it in the correct order. Thanks.
VB Code:
Private Sub Form_Load()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset 'object
Dim ConnString As String
Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set Text3.DataSource = rs
Set List1.DataSource = rs
Set con = New ADODB.Connection
con.ConnectionString = ConnString
con.Open ConnString
With con
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\db1.mdb;" & _
"Persist Security Info=False"
End With
rs.Open "select * from Table1", con
Text1.DataField = "Field1"
Text2.DataField = "Field2"
Text2.DataField = "Field3"
Do Until rs.EOF
List1.AddItem rs!Description
rs.MoveNext
Loop
End Sub
-
Re: populating the combobox
i think the best solution in this ons is to post a piece of your database and coding in a zip-file. only if you want to ofcourse.
I'm not really sure what you are trying to accomplish and what really is going wrong.
Seems you also forgot to close the rs at the end of the code.
rs.Close
Set rs = Nothing
Using "On Error Goto" is also very important in Database Software developement. You should also consider that.
-
Re: populating the combobox
Here m8. Made you a quick example with comments. You see a Combobox and a Listbox loading data when starting the Form. :D
Special: ProgressBar while loading the Combobox... :cool:
Hope you can do something with it.
I will also make a tutorial with a search-function. Stay tuned.
-
Re: populating the combobox
Ok.. you've completey destroyed the order of pretty much everything!!
You cannot set something to use a recordset ("Set ... = rs") until after the recordset has been opened, which can only be done (as mentioned in my last post) after you have Set it. It also cannot be opened until the connection is opened, which cannot be done until after you have told it where to connect to.
Here's an updated version of your code:
Code:
Private Sub Form_Load()
'declare database object variables
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset 'object
Dim ConnString As String
'initialise connection
Set con = New ADODB.Connection
'open the connection
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\db1.mdb;" & _
"Persist Security Info=False"
con.Open ConnString
'initialise recordset (this could be done with 'Set con = ' above if you want)
Set rs = New ADODB.Recordset
'open recordset (using the connection)
rs.Open "select * from Table1", con
'use recordset
Set Text1.DataSource = rs
Set Text2.DataSource = rs
Set Text3.DataSource = rs
'Set List1.DataSource = rs 'as you are filling it yourself, why do this?
Text1.DataField = "Field1"
Text2.DataField = "Field2"
Text3.DataField = "Field3" 'I presume this was supposed to be 3 ;)
Do Until rs.EOF
List1.AddItem rs!Description
rs.MoveNext
Loop
End Sub
-
Re: populating the combobox
Thanks guys (Radjesh and Si) - I'll let you know how I get on. :) Looking forward to the tutorial and the Find thing...
"'as you are filling it yourself, why do this?"
...well Si the idea is to have the List box populated with one field from the db, and when the field item is selected I wanted the fields associated with that record to populate the other textboxes. Perhaps I'm approaching this the wrong way, but until I get this first bit working...
Thanks again.
-
Re: populating the combobox
In that case your solution can be done much quicker ans easier. Your approach is not bad by the way. I'll make you an example/tutorial next week. It will be the same, but then with textboxes according the associated fields.
-
Re: populating the combobox
I just couldn't let it... hehehe :p
Here you go m8. With searchoption for Listbox and Combobox
-
Re: populating the combobox
Thank you for nice program there I did look for the same solution for long time
But what I was looking for is a bit more than this nice program.
You must have seen sage program, if you need to fine a customer you start to type in a combobox, as soon as you start typing, a listbox opens up and shows all the names starting with the first letter you typed in not whole records in you database.
In other word it will sort the records of specified filed by what you enter to the combobox and show it in a listbox. Now I need to know that is it possible to ad this to your program if so, how?
Thank you.
-
Re: populating the combobox
That's known as auto-complete, and has come up several times before.. I think that this thread has good solutions.
-
Re: populating the combobox
Thank you si i got what i wanted :)
-
Re: populating the combobox
Quote:
Originally Posted by Radjesh Klauke
I just couldn't let it... hehehe :p
Here you go m8. With searchoption for Listbox and Combobox
Hi Radjesh Klauke
There is a problem with your program which I could not go round it.
If you have a duplicated name in your database it only load the first on from database and ignores the rest.
Do you have a solution for it?
-
Re: populating the combobox
Simply remove the word "DISTINCT" from the SQL statement, as it specifies to not show duplicates.
-
Re: populating the combobox
Hi si
Thank you for the suggestion and it loads all the records now but if you chose any of the duplicated records in the list or the combobox the text boxes will only show the records for the first duplicated name in the database.
Any idea?
-
Re: populating the combobox
Quote:
Originally Posted by afshin_tt
Hi si
Thank you for the suggestion and it loads all the records now but if you chose any of the duplicated records in the list or the combobox the text boxes will only show the records for the first duplicated name in the database.
Any idea?
Yes, this is correct as this is what it thinks you want it to do, so this makes sense.
If this first name is a duplicate then it will hit on the first instance it finds.
If this is not what you want, then you will have expand your criteria to include something that is unique about the individual record that you are after.
-
1 Attachment(s)
Re: populating the combobox
In this case how do I view a table (in a datagrid or any other component that you think it is better to use) and double click on a record and have the text boxes felled with more info about that specific record.
-
Re: populating the combobox
Just like Hack described for a combobox, you need to have a unique field (something that is guaranteed to be different for every row).
If the AccountNumber field is unique you can use that, otherwise you should add another field to the table which is unique - the easiest way to to make it AutoNumber (in Access) or Identity (most other database systems).
-
Re: populating the combobox
Hi si the problem is that some times members forget their account number and the only way to find the member is to use the name "that’s what the members do not forget I hope".
I was wandered if you could help me with post number 30.
thank you.
-
Re: populating the combobox
That doesn't matter I'm afraid - you still need something unique about the record.
You can search by the name, but to select a record to edit you need to have unique info (preferably a single field that is unique) to be able to get the correct row.
It seems to me that the example may not be the best starting position for you - it may be better to use the ADO Tutorial and Further Steps links in my signature, as they are a bit clearer in my opinion.
-
Re: populating the combobox
hi si
I had to use a unique field to find records its now working thanks.
-
Re: populating the combobox
Hi guys :wave:
I'm trying to do the same thing with three comboboxes on a form in my project. On the first one, the values are pulled from a lookup table that only has one field with the values I need in it. This one works fine. The other two need to pull data from tables with numeric ID fields and text name fields. I have both of these set up identically:
Code:
rstIssue.Open [tblACDCallIssue], cnn, adOpenForwardOnly, adLockReadOnly, adCmdTable
With Issue
Do While Not rstIssue.EOF
rstIssue.MoveFirst
.AddItem rstIssue.Fields("Issue")
'.ItemData() = rstLEC.Fields("IssueID")
rstIssue.MoveNext
Loop
rstIssue.Close
Set rstIssue = Nothing
My problem is, both of these fields either a) insist on populating the ID field instead of the text field, or b) refuse to populate anything at all, depending on what I do with the RowSource properties on the form design.
Any ideas?
-
Re: populating the combobox [Resolved]
-
Re: populating the combobox
-
Re: populating the combobox
Quote:
Originally Posted by Hack
What did you do?
Well, the one thing that screams at me the loudest is that I was a numbskull and forgot to initialize the recordset. :blush: Other than that, here's the code that I actually got to work:
VB Code:
Set rstIssue = New ADODB.Recordset
rstIssue.Open "tblACDCallIssue", cnn, adOpenForwardOnly, adLockReadOnly, adCmdTable
With Issue
rstIssue.MoveFirst
Do While Not rstIssue.EOF
.AddItem rstIssue.Fields("Issue")
rstIssue.MoveNext
Loop
End With
rstIssue.Close
Set rstIssue = Nothing
Wow... for once, I might actually be able to help somebody else! :bigyello: