?
Solved

DataCombo and two data sources

Posted on 2003-03-15
3
Medium Priority
?
170 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
  • 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month12 days, 21 hours left to enroll

579 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