How do I data bind a second ADO object in VB6?

I don't ask for help all too often, so in case this is a n00b question for you guys, I am sorry for wasting your time, but I have been busting my *** trying to figure this out for 2 days now.

I have a mySQL database and am using VB6 and the ODBC 5.1 Connector to communicate with it. I have recently discovered the ADO object and the ability to data bind objects to the record sets it works with. That works great for me. Like in MS Access, I would like to create a sub form / sub query as a second ADO object. The second object is the one that is giving me problems.

TABLE 1 (tblBusinesses)
Company
CompanyID (PK)
Address
City
etc

TABLE 2 (tblClients)
CompanyID
ContactName
ContactEmail
ContactPhone
BackupBattery
LiquorSystem
Notes

I realize that with the above example I could cheat and merge the tables into one, however I have more tables and entities in the DB and need to know how to properly work with them.

Currently my RecordSource property for my second ADO object is:

Code:

---

SELECT * FROM tblClients INNER JOIN tblBusinesses ON tblClients.CompanyID=tblBusinesses.CompanyID

---

This works (coincidentally I believe) for the first record, but when I goto next record in the first ADO, the second ADO should automatically update it's values. In MS Access this kind of binding will work, even though it doesn't make alot of sense, as you save it as a query and then turn that query into a subform on your main form, however, there must be a similar or proper way of getting this to work. I have downloaded a ton of e-books and none of them explain how to visually use a second ADO control and data bind text boxes, combo boxes, etc to the control's data.

Alternatively, I tried to adjust my SQL statement to use the WHERE clause instead of an INNER JOIN, plus I tried making 2 separate forms, one ADO control on each form. I created a button on the first form with the following code:

Code:

---

Private Sub Command1_Click()
    Dim nf As New Form2
    nf.txtCompany.Text = Me.txtCompany.Text
    nf.clients.RecordSource = "SELECT * FROM dennisk_rsi.tblClients WHERE dennisk_rsi.tblClients.Company='" & nf.txtCompany.Text & "'"
    nf.clients.Recordset.Requery
    nf.Show
End Sub

---

This also doesn't work. I am truely lost. Please help me figure this out. I'd appreciate any help offered.

I have figured out how to do this via code by updating the RecordSource property, but it doesn't fix the design-time problem of not being able to set a variable within the SQL statement of the RecordSource Property on the ADO object's property page. Does anyone know how to do this? Does VB even support this?

Welcome to VBForums :wave:

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

Like many experienced developers I don't use data controls enough to be able to help with this. For an explanation of why that is, see the article Why is using bound controls a bad thing? from our Database Development FAQs/Tutorials (at the top of this forum)

That article contains a link to the recommended alternative (ADO code), which has an example that replicates the main features of the data control. There are many other articles in the "Classic VB - ADO" section of the FAQs thread (particularly "the further steps" article), which help you to extend it in many ways - like adding search functionality, working with other controls, etc.


While that wont actually answer your question, it will enable you to change your project so that many more people can help you - and based on the things you have written, I suspect you will work out how to do what you want.