473,842 Members | 1,958 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

On child form create bound list box to show related records from parent form

LeighW
73 New Member
Hi all,

I have two tables with a many-to-many relationship which is causing many problems. My parent and child forms are linked via a junction table. I want to make it possible to create a read-only version of the child table so that you don't have to link to it from the parent table.

A form linking the two together has been created with a combobox where you can pick and choose which records relate to each other.

I'd like on the child form there to be a list box or combo box which shows the records from the parent table linked to that record from what is picked within the comboboxes of the "junction form".

In essense it would mean that people reading the child form would know which names the record is based on if they didn't want to go via the parent form to read it.

Any help would be great,

Leigh
Jul 4 '12 #1
20 34977
zmbd
5,501 Recognized Expert Moderator Expert
I'm not following your logic here... are you saying that you already have a Parent/Child form and that you do not want users to be able to edit the related records in the child form?
Jul 4 '12 #2
LeighW
73 New Member
Sorry this is my beginner side of access showing. When I said Child form it's not that at all, it's because it used to be like that. It's a separate form. I'll explain using Form 1 and Form 2.

Form 1 (Based on Table 1) and Form 2 (Based on Table 2) are not directly linked (they used to be that's why I used that language). A junction table (Table 3) between the two forms links them to create a many-to-many relationship. I have a form (Form 3) which is based on a query using all 3 tables. I use Form 3 to create new records which then also appear in Form 2. Form 3 lets me show the records linked between Table 1 and Table 2. I use yet another form (Form 4) which has a parent (Table 1)/child (Table 2 and 3) relationship with a combobox in the child form which allows me to choose the records from Table 2 that link to Table 1. The records chosen then also show in Form 3.

Form 3 cannot be looked at if you want to see the records from table 2 with no duplicate records. Form 2 however shows only the records from Table 2 (no duplicates).

Form 1, Form 3 and Form 4 share a field from table 1, lets call it, "Title" field (Datatype Text). I'd like "Title" to also show in Form 2 (as well as records from Table 2) as an uneditable list or combo so that readers can see which "Titles" the records in Form 2 are related to from Form 1.

I hope that makes more sense and there could be a way of doing what I want.

Leigh
Jul 4 '12 #3
zmbd
5,501 Recognized Expert Moderator Expert
So you'd be using the combobox on form 2 basically like a filter, yes/no?
I'm not sure if this is exactly what you are after; however, NeoPa has a pretty good example of form filtering here:http://bytes.com/topic/access/insigh...filtering-form in anycase, the information should hopefully get you on the right track.
z
Jul 4 '12 #4
LeighW
73 New Member
Thank you, I'll have a good look at it and yes I believe that is what I'm after
Jul 6 '12 #5
zmbd
5,501 Recognized Expert Moderator Expert
@LeighW
Thinking about this a tad... in addition to using the "filter" concept, you might look at creating a new form/subform setting properties to prevent adding/changing records at the form/control level in their properties. Rabbit and I had a thread discussing this... Rabbit points out that the advantage is that no VBA is needed to get the information out of the db.

Discussion: Advantages of a Subform vs. Filtering

OK, off to do the Family thing with the In-Laws ... really not as bad as it sounds... if they feed me! ;-)

-z
Jul 6 '12 #6
ariful alam
185 New Member
Are you saying:

Table 1 linked to -> Table 2
and
Table 2 linked to -> Table 3?

And you like to use Table 2's Form to view records on Table 1 & Table 3 in a Form.
Jul 8 '12 #7
zmbd
5,501 Recognized Expert Moderator Expert
Ariful,
In post #3, Leigh gives the relationships.. . I had to read it twice to follow too...

So, if one ignores the forms to start out with then, from what is posted in #3, I understand the following to be true(an Leigh might need to correct):

tbl_one -> (1:m) tbl_three (m:1) <- tbl_two

There are forms based on each of these tables...
frm_one -> tbl_one
frm_two -> tbl_two
frm_three -> tbl_three

There is an additional form ... say.... frm_four.
Now frm_four gets a tad confusing...
I'm not sure that frm_four is related to our solution; however, it is good to have an understanding of the form.

From what I understand for frm_four:
frm_four->tbl_one:sub_fr m_four_A
Somehow, sub_frm_four_A is related to tbl_two and tbl_three
Somehow, sub_frm_four_A will allow a linking between tbl_one and tbl_two.... this is where having a copy of the DB with some made-up records would be helpful

In any-case, there is a field [Title] that is currently common to frm_one, frm_three, and frm_four.

Leigh appears to want that same field to show up on frm_two. The control that will be using [Title] as it's source needs to be un-editable and filters the records shown in frm_two to those related only to the [Title].value selected

To me, there are two ways that appear to be possible solutions to the question asked based upon the available information... the first was the link offered for NeoPa's article (link in post #4) and the second is to create a form/subform wherein the parent form's controls are blocked from being editied (and I figure, also from adding new records?), some of the merits of which are in the thread noted in post #6.

Once again... Leigh is certainly encouraged to correct any errors I may have made. (esp about frm_four... normally a parent form controls the subform; however, we may be talking nomenclature... just don't know :) )


-z
Jul 8 '12 #8
ariful alam
185 New Member
@zmbd, are you saying that @LeighW explained in post#3 the following:

Two individual tables named Table_1 and Table_2 are linked with another table named Table_3.

Form_1 is a direct form of Table_1.
Form_2 is a direct form of Table_2.
Form_3 is a queried form of Table_1, Table_2 and Table_3 to show the link data in Table_1 and Table_2 via Table_3.

And

He used Form_3 also to entry a new record in Table_1 and Table_2.

He has also another form Form_4 that use Table_1 as parent object and Table_2 and Table_3 as child objects.

Are those right?

If those right, now tell me in Form_4; Table_1, Table 2 and Table_3 used as new form or he used the old forms Form_1, Form_2 and Form_3?

And which is search chooser section in Form_4 and which are result shower section in Form_4

Thanks
Jul 9 '12 #9
LeighW
73 New Member
Yes everything both of you have said are right. Thank you for understanding! Many-to-many relationships are difficult to explain/grasp. I'm back from the weekend + braindead Monday so I'll have a look at both methods and see which is best. A subform might be a bit clunky for just one field, that's perhaps the drawback for that method but I see how it would work.

Ariful alam - You are right in how the forms and tables work. I'm not sure I follow your question though? Form 4 is just used to choose the links between Table 1 and Table 2 in the many-to-many relationship using a combobox record selecter. Form 4 is always filtered to the record from Form 1 (as you are directed to Form 4 from Form 1) and you cannot edit/add to the parent in Form 4. Form 4 is just playing with existing data from Tables 1 and 2 and creating duplicates in Table 3 to create the many-to-many linkage.

Also I hope you had a good time at the In-Laws zmdb (it's usually a daunting experience!)
Jul 10 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

4
2647
by: news.online.no | last post by:
In a query, I need too be able to show if a parent record has a child record. Using the query in a combo box too select record in a form. Thanks :)
25
10286
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list...
0
1391
by: Wolfgang Kreuzer | last post by:
Hi, I am starting to migrate an Access 2.0 application to Access 2000 (I know it's not the latest version, but ist supported in our company). I found some funny behaviours where I could not find a solution in google groups. I retrieve data from sql server (2000) and created an access project. Form is bound to a view, in the form header there are two filter
2
12601
by: developer | last post by:
I have a form with a menu on the top. This menu show another form. I want to open this second form maximized inside the parent form just below the menu of the parent form. (Like Microsoft Word). How do i do that ? Thanks
2
1476
by: Mike L | last post by:
The child form can be dragged out of the Parent form. I set the child form to IsMdiContainer = False and the Parent Form IsMdiContainer = True. I also coded in the Parent Form on load, Dim f As New frmEntryForm f.MdiParent = Me What am I missing? The child form appears not to be attached to the parent form.
3
1621
by: cbrown | last post by:
I have two forms Main & Child. Main has several readonly properties that I would like to access from the child form. How do I reference the MDI-Parent form MAIN and use those properties.
0
1737
by: kolalakitty | last post by:
Hopefully someone here can help me/point me in the right direction. I've found tons of references towards making relations, creating rows, saving said rows, using datagrids, databinding objects, and etc in regards to Windows Forms, C#, and Visual Studio .Net 2003. I seem to have run into a bit of a problem however. I'm not using datagrids to show related data, my users wouldn't have any idea how to use them, and they want to see all of...
2
3027
by: dynamictiger | last post by:
I am building a wizard and have an issue with one sub form. Whilst all others are about 5 fields tall this one is 15 fields. My choices are either to resize the parent form ridiculously large for the other forms or resize the parent form on loading this subform. I have tried the following: Me.Parent.Form.Section(acDetail).Height = 1.4 * Me.Parent.Form.Section(acDetail).Height 'Me.Parent.Form.Section(acDetail).Repaint
14
3764
WyvsEyeView
by: WyvsEyeView | last post by:
On my form frmTopics (bound to table tblTopics), I've added a Copy button that copies the current topic record to create a new, identical one...simple enough. However, each topic record can have 1+ related records in tblTopicAttributes (a record for each attribute associated with the topic). So, I also want the copy action to create new and identical related records in tblTopicAttributes for the newly-created tblTopics record. I can do this with...
1
2458
Jerry Maiapu
by: Jerry Maiapu | last post by:
Hi Please help. How can I delete i single record in a datsheet subform from main form using a delete button on the parent form.? i will be more grateful for your help... jm
0
9870
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9715
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10610
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10671
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10310
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7855
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7035
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4499
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4088
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.