?
Solved

Joining Access 2003 form with sub form by field name

Posted on 2011-05-08
10
Medium Priority
?
326 Views
Last Modified: 2012-05-11
How can joining Access 2003 form with sub form by field name using ADO ?

On other meaning
Designing a main form contain a sub form, the main form display some data from a table and the sub form display another data from another table but joining the two data by equal fields in both tables with ADO.
0
Comment
Question by:egovernment
  • 6
  • 2
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35715525
This is nothing to do with ADO.

In the main form_open event procedure you do your equivalent of..

Me.subformcontainercontrolname.LinkChildFields = "fieldnameInSubformrecordset"
Me.subformcontainercontrolname.LinkMasterFields = "fieldnameInMainformrecordset"


subformcontainercontrolname  is the name of the control on the main form that contains the subform.
0
 
LVL 10

Expert Comment

by:Korbus
ID: 35715722
I agree with peter.  
note: the data source used on the subform in this case, is an unfiltered/joined list of all items in the table (i.e. the table itself).  The info peter gave above FILTERS this list, to display only the related record(s).
0
 

Author Comment

by:egovernment
ID: 35715888
I read the data from ADO therefore the form and subform display the data from ADO so I want to make link between main form and sub form with command through ADO
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:egovernment
ID: 35715914
Hi peter57r

Where I put these statements ?

Me.subformcontainercontrolname.LinkChildFields = "fieldnameInSubformrecordset"
Me.subformcontainercontrolname.LinkMasterFields = "fieldnameInMainformrecordset

in which procedure in the form ?
0
 

Author Comment

by:egovernment
ID: 35716220
I'm using

Me.subformcontainercontrolname.LinkChildFields = "fieldnameInSubformrecordset"
Me.subformcontainercontrolname.LinkMasterFields = "fieldnameInMainformrecordset

But I get error messege

Data provider could not be initialized
0
 
LVL 10

Expert Comment

by:Korbus
ID: 35720528
egovernment, are you able to view the subform by itself (if you do NOT open the main form)?  you SHOULD be able to, and if not, that is where you should start troublshooting.  (opening this form by itself will show all records, it obviously wont be filtered to match the main form, when opened alone).

I would start by looking at the "dataSource" field for the sub-form.
0
 

Author Comment

by:egovernment
ID: 35865475
I've requested that this question be deleted for the following reason:

There no correct answer and I found it by myself
0
 

Accepted Solution

by:
egovernment earned 0 total points
ID: 35954911
This the code by ADO can join between the Mainform and the Subform

dim vr_SQLExp as string
vr_SQLExp = "SELECT A FROM B WHERE A = " & Me!A
dim vr_RdSet as new ADOBC.Recordset
vr_RdSet.open vr_SQLExp, vr_DbConn, adOpenStatic, adLockOptimistic
set Me.subform_name.form.recordset = vr_RdSet
vr_RdSet.close
set vr_RdSet = nothing

Open in new window

0
 

Author Closing Comment

by:egovernment
ID: 35987595
This is the correct answer of my question
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

839 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