There is no error in your code. Just you have not added the Microsoft Activex Data Object 2.x Library to your project. Just add this reference it will work fine.
Printable View
There is no error in your code. Just you have not added the Microsoft Activex Data Object 2.x Library to your project. Just add this reference it will work fine.
Actually, Yes I have. I have added the refrence for the 2.6 lib and it still does not work. This is driving me nuts.
Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]
ok I changed it as per your suggestion and I still get:
Compile error:
wrong number of arguments or invalid property assignment.
I am using the ADO 2.6 Library, is there any known problems with this Lib? Should I use the 2.5 or 2.5 Multi-dimensional?
No matter what, adding my data to the combo box seems to be a failure. Is there a better way to do this maybe? Here is my full code as it stands now:
option Explicit
Dim con as ADODB.Connection
Dim rs as ADODB.Connection
private Sub Form_Load()
set con = new ADODB.Connection
set rs = new ADODB.Connection
' I am establishing the connection
con.Open "Provider = Microsoft.Jet.OLEDB;" & _
"Data Source = C:\Nwind.mdb;" & _
"Persist Security Info = false"
' I am setting my querry
rs.Open "SELECT * FROM Customers", con, adOpenStatic, adLockOptimistic
' I am populating a combo box
Do While rs.EOF = false
Combo1.AddItem = rs![Company Name]
rs.MoveNext
Loop
End Sub
I am using a simple for called form1 and I am using a single combobox called Combo1. What can I do to simplify this process?
My ultamate goal is to load the whole Customers table into a record set and have a series of combo boxes take the traget feild and display them. When I set out to do this I thought this to be a no sweat problem. I can get this to work with a list box, but not with my **** combo box. What else could we possibly to here?
Thank you all in advance for any solutions you could bring to this problem.
Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]
Found a few mistakes
option Explicit
Dim con as ADODB.Connection
Dim rs as ADODB.Recordset
private Sub Form_Load()
set con = new ADODB.Connection
set rs = new ADODB.Recordset
' I am establishing the connection
con.Open "Provider =Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = E:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
"Persist Security Info = false"
' I am setting my querry
rs.Open "SELECT * FROM Customers", con, adOpenStatic, adLockOptimistic
' I am populating a combo box
Do While rs.EOF = false
Combo1.AddItem rs!CompanyName
rs.MoveNext
Loop
End Sub
You defined the recordset as a connection
You did not fill in provider properly
You may need to use Jet 3.51 if you do not have Jet4.0 installed
You need to use the full path of the database in you are not in it's directory
This is the location of the db on my machine, change it to suit yours
Companyname has no space in it.
I didn't know you were using nwind otherwise I would have picked up on that.
David Paulson
http://forums.codeguru.com/
I have checked a few things:
I am using Jet 4.0+ (installed with office XP)
I am using ADO 2.5 Lib (2.6 on my laptop)
c:\Nwind.mdb is where I have my Access file located (This is a test partition on my 2k Box).
Now that you have pointed out that I am using a recordset as a connection instead of a recordset I say "Doh!" I have been so focused on my last part of the sub that I completely forgot the basics.
I use the code you provided and I now get this error:
Runtime Error -2147467259 (80004005)
Could not find installable ISAM
I have looked through my MSDN CD's and could not find anything helpful. What would cause this error? This is one I have never seen.
Well anyways, change is good, no? Thank you very much for you much needed feeback.
Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]
I just tried it again. A new project with a combo box. made reference to ado 2.5 (That all I have). Pasted the code snippet that I posted. Works as expected.
David Paulson
http://forums.codeguru.com/
Try putting the ado data control on a form and build the connection with it (just point and click to build it). It will let you test this connection, to see if it will work. You then can copy and paste the connection string to use in your code.
David Paulson
http://forums.codeguru.com/
It works like a charm after I re-installed SP5 for VS6. Thank you for all your help. Just one final question and I will have everything I need to create a few demo programs using ADO. This has been such a great learning expereince.
I want to add a 2nd combo box and populate another field to it. Lets say the "Contact Name". I want the 1st combo box which will have the "Company Name" be linked to the 2nd combo box. When you select a company from the 1st box I want the 2nd combo box to display only the Contact Names that pertain to the 1st combo box. Since I have loaded all the records into memory space I was thinking that I could use the Filter property. Any ideas how this might work or any resources you could point me too about linking two combo boxes with ADO?
Thank you in advance.
Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]
Well, since there is only one Contact name for each company name, you do not need a combo box, a text box is sufficient. Since there is only one company name (not several with the same name), you need to 'FIND' the record so, in order to acheive this, you can place the following code in the combo click event. I should mention that some company names include an apostophe. In order to make it work in the Find, you need to double them up. Also, since companyname is a string value, it must be enclosed in apostophes. Copy and paste this code to see how it works. Add a text box (text1).
option Explicit
Dim con as ADODB.Connection
Dim rs as ADODB.Recordset
private Sub Combo1_Click()
rs.MoveFirst
rs.Find "CompanyName = '" & Replace(Combo1.Text, "'", "''") & "'", , adSearchForward, adBookmarkFirst
LoadCustomerData
End Sub
private Sub Form_Load()
set con = new ADODB.Connection
set rs = new ADODB.Recordset
' I am establishing the connection
con.Open "Provider =Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = c:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
"Persist Security Info = false"
' I am setting my querry
rs.Open "SELECT * FROM Customers ORDER BY CompanyName", con, adOpenStatic, adLockOptimistic
' I am populating a combo box
Do While rs.EOF = false
Combo1.AddItem rs!CompanyName
rs.MoveNext
Loop
End Sub
private Sub LoadCustomerData()
Text1 = rs!ContactName
End Sub
David Paulson
http://forums.codeguru.com/
Thank you for the excellent code and pointers. But I am also interrested in putting selections from one box and filtering it on the 2nd box so that one effects what the other views.
This is not so much for this database, but for future refrence. I think it would be a great way to narrow down many feilds with many selections that could relate to the previous field.
Thank you for everything.
Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]
I added a bit more. 2 combo baxes and 2 text boxes.
option Explicit
Dim con as ADODB.Connection
Dim rs as ADODB.Recordset
private Sub Combo1_Click()
rs.MoveFirst
rs.Filter = "CompanyName > """ 'set the recordset back to all records
rs.Find "CompanyName = '" & Replace(Combo1.Text, "'", "''") & "'", , adSearchForward, adBookmarkFirst
If Not rs.EOF then
LoadCustomerData
else
MsgBox "Account not found"
End If
End Sub
private Sub Form_Load()
set con = new ADODB.Connection
set rs = new ADODB.Recordset
' I am establishing the connection
con.Open "Provider =Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = e:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
"Persist Security Info = false"
' I am setting my querry
rs.Open "SELECT * FROM Customers ORDER BY CompanyName", con, adOpenStatic, adLockOptimistic
' I am populating a combo box
Do While rs.EOF = false
Combo1.AddItem rs!CompanyName
rs.MoveNext
Loop
End Sub
private Sub LoadCustomerData()
Text1 = rs!ContactName
Text2 = rs!ContactTitle
PopulateContactCombo
End Sub
private Sub PopulateContactCombo()
rs.Filter = "ContactTitle = '" & Trim(Text2) & "'" 'Filter the recordset
Do Until rs.EOF
Combo2.AddItem rs!CompanyName 'Load the combo
rs.MoveNext
Loop
End Sub
David Paulson
http://forums.codeguru.com/
Thankyou, This is exactly what I was looking for.
Timothy H. Schilbach
Alpha Omega Design Inc.
[email protected]