Printable View
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 recordsetQuote:
Originally Posted by bsarika
What this does is goes through each record in the recordset and adds it to the list box.VB Code:
Hope this helps.
:)
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
Welcome to the forums.
I moved the thread from the Codebank. The codebank is only for posting code samples, not questions.
Change this section of code:to what flukey2005 suggested, ie:VB Code:
VB Code:
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"
VB Code:
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.Quote:
Originally Posted by D43x
VB Code:
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 sameYou 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.VB Code:
Here's how it looks now, but have a new error (below)...
VB Code:
Error:
Object required.
PS. Why is there "con" and "conn"? 2 separate variables?
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
Where is conn?Quote:
Originally Posted by D43x
You forgot:
Dim rs As ADODB.RecordSet
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?
Did you placed the ADODC1 control on the form? I think that is the problem.
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
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:
VB Code:
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:
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.
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.
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
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.
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.
I just couldn't let it... hehehe :p
Here you go m8. With searchoption for Listbox and 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.
That's known as auto-complete, and has come up several times before.. I think that this thread has good solutions.
Thank you si i got what i wanted :)
Hi Radjesh KlaukeQuote:
Originally Posted by 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?
Simply remove the word "DISTINCT" from the SQL statement, as it specifies to not show duplicates.
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.Quote:
Originally Posted by afshin_tt
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.
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.
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).
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.
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.
hi si
I had to use a unique field to find records its now working thanks.
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:
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.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
Any ideas?
I got it fixed. :D yay!!
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:Quote:
Originally Posted by Hack
VB Code:
Wow... for once, I might actually be able to help somebody else! :bigyello: