?
Solved

DataCombo and two data sources

Posted on 2003-03-15
3
Medium Priority
?
164 Views
Last Modified: 2013-12-25
Hi all,

I am trying to create a form which contains a combo box, the list elements of which are populated by one table (the fullname field from a table named users) - the value of which comes from another table (the rep_id field from a table named clients).

ie. the choice from the combobox should read and write to the rep_id field, but the other list values should come from the fullname field.

I have found the following example at MSDN which explains how to do this using two data sources:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconlinkingtwotablesusingdatacombodatalistcontrols.asp

However, I am trying to achieve the same result using ADO code. I have the following code but it errors when it reaches line 26:

1:  ClientId = 1
2:
3:  Dim db As Connection
4:  Set db = New Connection
5:  db.CursorLocation = adUseClient
6:  db.Open "PROVIDER=MSDASQL;dsn=dev;uid=;pwd=;"
7:
8:  Set adoPrimaryRS = New Recordset
9:  adoPrimaryRS.Open "select client_id,client,contact1,contact2,address1,address2,town,county,postcode,country,daytel,mobile,fax,email,website,active,rep_id from clients where client_id = " & ClientId, db, adOpenStatic, adLockOptimistic
10:
11:  Dim userdb As Connection
12:  Set userdb = New Connection
13:  userdb.CursorLocation = adUseClient
14:  userdb.Open "PROVIDER=MSDASQL;dsn=callimedia;uid=;pwd=;"
15:
16:  Set adoUserRS = New Recordset
17:  adoUserRS.Open "select fullname from users order by fullname", userdb, adOpenStatic, adLockOptimistic
18:
19:  Dim oText As TextBox
20:  For Each oText In Me.txtFields
21:    Set oText.DataSource = adoPrimaryRS
22:  Next
23:
24:  Set DBCombo1.DataSource = adoPrimaryRS
25:  DBCombo1.DataField = "rep_id"
26:  Set DBCombo1.RowSource = adoUserRS
27:  DBCombo1.ListField = "fullname"
28:  DBCombo1.BoundColumn = "fullname"

Any help would be much appreciated.

Many thanks,
Trevor
0
Comment
Question by:trevorw
[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
  • 2
3 Comments
 
LVL 3

Accepted Solution

by:
QJohnson earned 200 total points
ID: 8145161
I know this suggestion is going to sound pretty lame, but this same topic came up in a discussion on CompuServer about a year ago and I happen to have saved it.  I can give you the whole posting if you want it, but the bottom line is that the SEQUENCE of assignments is the problem although this appears not to be documented anywhere.

You have it almost exactly correct - just make the assignment of the BoundColumn come before the assignment of the ListField (swap them).

That magic order is:

Set .DataSource
Assign .DataField
Set .RowSource
Assign .BoundColumn
Assign .ListField

Again, if you want the poster's full text (which would add which versions of the libraries they used in their testing), just let me know.

Don't you just love Microsoft when you come across stuff like this? <g>

Q
   
0
 

Author Comment

by:trevorw
ID: 8146085
Hi Q,

Thanks very much for your help - got it sorted now ... damn M$ :) If poss could you please send on the complete original post ... I wouldn't mind a read thru it... u can mail me at trevor@webtribe.net

Thanks again,
Trevor
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8146702
Sure - here it is.  

>>>> from James Brockman in a CompuServe posting >>>>
We have been able to set all properties of the datacombo in code, but there is a trick to it...our code looks like this:

Set Me.dcmCity.DataSource = rsContacts
Me.dcmCity.DataField = "City_Fips"
Set Me.dcmCity.RowSource = rsCities
Me.dcmCity.BoundColumn = "City_Fips"
Me.dcmCity.ListField = "City"

The trick is sequence:  It is imperative that you set the properties from RowSource, BoundColumn, and ListField in exactly the order shown above.   In our testing, any deviation from this order resulted in a blank combo box.  

The test project we discovered this with uses no data controls at all, and binds the textboxes and comboboxes in code to a recordset created entriely in code.  

In our case, the database was Access 97, and our ADO version is 2.7. using VB6, SP5.  Be advised: There may be something significant in this combination of environment and libraries as well, but time didn't allow us to test that theory.

<<<< end of pasted text  <<<<<<
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no 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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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, 18 hours left to enroll

770 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