?
Solved

How to load a combo box with database info in VB 6.0

Posted on 2004-04-18
11
Medium Priority
?
981 Views
Last Modified: 2013-12-25
Hi all,

I have been reading through solutions to similar questions and trying to follow the solutions, but to no avail. I am not very good at using VB 6.0, but i had recently done a project in VB.Net and had no problems with this.

So i have created a access database called ApplicationsDB and the table in this databse is called Applications. What i want to do in VB is this: i want to load all of the information from the column called Application Name. At the moment i have it so that it is actually bound to the DB and in my combo box, Combo1, it is putting in one item from Application Name but no matter what i try i can't make it so that it fills it with all the information.

Any help would be greatly appreciated asap as i have just started a new job and this is one of my tasks. Thanks in advance.

Regards,
Mary    :)
0
Comment
Question by:marypebbels
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10855338
using ADO (ActiveX Data Objects).

1) Add a reference to the ActiveX Data Objects Library (Project/References)

2) in you code:

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<path to your database>;"
cn.Open
strSQL = "Select ApplicationID, ApplicationName from Applications"
Set rs = cn.Execute(strSQL)
With Combo1
    Do While Not rs.EOF
    .AddItem rs.Fields(1)
    .ItemData(.NewIndex) = rs.Fields(0)
    rs.MoveNext
    Loop
End With


AW
0
 

Author Comment

by:marypebbels
ID: 10858385
hi arthur,

i forgot to mention that i think it is an adodc connection that i have to the db-its called datPrimaryRS, but i suppose this doesn't matter seeing as its a new connection. I did the ADO thing that you mentioned but was not sure where to place the code in your step2 but i put it into Private Sub Combo1_Click() and it ran but there was still only one item in the combo box.
thanks
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10858461
place a breakpoint on the line:

Set rs = cn.Execute(strSQL)


and then use F8 to single step throuhg the code.  How many times does the AddItem method get called.

Also, id the ComboBox BOUND to the dataControl?  

AW

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:marypebbels
ID: 10858878
arthur,

I placed the breakpoint, and went through the code and the AddItem method never gets called, so the thing is that i obviously have placed the code in the wrong place?? As i said i put it into Private Sub Combo1_Click()

I'm not exactly sure about the ComboBox- the thing is that i had set up an adodc connection to my database and this is called datPrimaryRS. So in the properties of ComboBox i have dataSource set to datPrimaryRs and dataField set to Application Name-i really am so confused!!

Thanks Arthur
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10860102
No, not in the Combo1_Click event handler.

Try placing the code in the Form_Load() event  so that the combo box is loaded when the form is loaded into memory.

AW
0
 

Author Comment

by:marypebbels
ID: 10860739
Arthur,

Thanks a million for your help on this.

I put the could into the load-form() and i now have a runtime error '-2147217904' and it says that "No value given for one or more required parameters" and the area of the code that its pointing at is "Set rs = cn.Execute(strSQL)".

Thanks Arthur
0
 

Author Comment

by:marypebbels
ID: 10860799
Sorry arthur, the runtime error that I am actually getting is:"Syntax error(missing operator) in query expression Application Name".

The sql statement I have is this: strSQL = "Select Application Name from Applications"
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10861495
If the field in your table is in fact "Application Name" (with the embedded blank), then change the SQL like this:

strSQL = "Select ApplicationID, [Application Name] from Applications"


Access (and in fact almost EVERY database) does NOT like to have embedded Blanks in the names of objects (such as Tables or fields).  I strongly adise you to change the blanks to _:

Application Name ===>  Application_Name for example.  But if you MUST keep the embedded blanks, then surround the object name with [...]  as in [Application Name].

Access sees the embedded blank as an invalid character, and the [...] tells Access to accept that invalid character, anyways.

Same applies to the use of RESERVED words, such as DATE or MONTH or YEAR (all of which are the names of built-in Access functions) which novice users seems to LOVE to use as field names in tables.  These will also lead to syntax error in SQL, so you enclose the RESERVED word(s) in [...] to tell Access that you understand that this was a RESERVED word, but to use it as a Field name anyways.  This is a poor solution, but Access allows it all the same.

My advise here is to avoid using RESERVED words, for fields names, or variable names.

AW
0
 

Author Comment

by:marypebbels
ID: 10868120
Hi Arthur,

I have done everything now that you asked but to no avail. I started up a new form there now just in case all the stuff that I had be doing to it had messed it up.

So I have created a form with the Combo1 and I Added a reference to the Microsoft ActiveX Data Objects 2.0 Library and i have the following code:

Private Sub Form_Load()
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Dept\User Support\Database Reports\Application\ApplicationDB.mdb;"
cn.Open
strSQL = "Select Application_Name from Applications"
Set rs = cn.Execute(strSQL)
With Combo1
    Do While Not rs.EOF
    .AddItem rs.Fields(1)
    .ItemData(.NewIndex) = rs.Fields(0)
    rs.MoveNext
    Loop
End With
End Sub


So that's pretty much it, any idea what the problem is?
The runtime error that it is now giving is 3265: "Item cannot be found in the collection corresponding to the requested name or ordinal"

Thanks arthur
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 200 total points
ID: 10870808
I assumed that you were also getting the ApplicationID, so change the code like this:

strSQL = "Select Application_Name from Applications"
Set rs = cn.Execute(strSQL)
With Combo1
    Do While Not rs.EOF
    .AddItem rs.Fields(0)
    rs.MoveNext
    Loop
End With


AW
0
 

Author Comment

by:marypebbels
ID: 10876962
Arthur,

Its perfect-I cannot thank you enough for your help. Cheers   :)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 6 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question