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

[LeighW]

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

[zmbd]

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?

[LeighW]

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

[zmbd]

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

[LeighW]

Thank you, I'll have a good look at it and yes I believe that is what I'm after

[zmbd]

@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

[ariful alam]

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.

[zmbd]

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_frm_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

[ariful alam]

@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

[LeighW]

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!)

[zmbd]

Thnx for clearing up form 4...

The In-Laws... help FIL lay 3yards concrete... long, hot day... but you know, he's helped us out... and a 65ish old man doesn't need to be doing that kind of work in the heat. However, I think I've come down with something so I may be just lurking for the next few days.... I don't do the sick thing very well :(

-z

[ariful alam]

If i create a Form named Form3 using a query value of Table_1, Table_2 and Table_3, the Form3 will be a parent-child form or a linked form of two form. in both cases, in child form or in linked form multiple data can be added for the parent form. but it is not possible to add multiple data in parent form because of every child/linked form data.

do you both agree?

[zmbd]

Ariful... I'm not sure where you're going with this train of thought as on the surface it does not appear to relate to OP question. (Could be that I'm just under the weather too)

From my understanding in the OP, LeighW only needs a way to show [Title].value as it relates to the records shown on frm_two.

IMHO, w/o having a copy of the DB w/ test data, as I suggested in post 4 and 6; either a form/subform or the information in NeoPa's tutorial should provide the answer to the question. If not, I suspect LeighW will be back (and I hope will tell us either way)

I'm sure that there different ways to restructure LW's DB; however, W/O the DB I wouldn't even want to start such an adventure :)

-z

[ariful alam]

@ZMBD, in post#3 LeighW wrote,

"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."

that's why i post the above before you. What you suggested NeoPa's tutorial is solved by VBA code. But my problem is I don't like to do VBA code in MS Access database. I try anything in MS Access using Expression Builder or Macro. So, that's why I am finding a way that may not need to add VBA code directly.

Thanks.

[LeighW]

Looking at NeoPa's method it looks a bit too complicated for what I need though it does seem interesting in how it works.

Z - I hope you get better (at least you had sunshine)! and cheers for the help.

Ariful - That's correct thanks for the help so far.

Edit - Also I'd give the DB but it would take an awful lot of time to change it to non-sensitive

[ariful alam]

@LeighW,

did you saw post#12 here in serial by me? do you need that in your work? if yes, is that in that way is described there or any other thing?

is it possible to provide a copy of your work without data here?

thanks.

[zmbd]

Ariful... I'm sorry, I still do not follow how your suggestion will answer the OP.

LeighW... NeoPa's tutorial does appear to be staggering upon first read; however, I assure you that it is much easier to do than to write :)

Both: If your are of the group that likes to avoid VBA as much as possible then the link to the thread between Rabbit and Myself (#6) about the form/subform is as Ariful suggested one such solution.

In anycase, I hesitate to offer any specific solution to the question as I do not have a clear understanding of the database. However, as there is a common field [Title] you could create something along the lines of the following where I have a single parent form and three child forms. Selecting from the list box in the upper left corner causes an update against the three subforms as they are linked to the value of the control

-z

[ariful alam]

@zmbd,

what u did in your attached image is easy to do using query of the tables which are taking the list/combo box value as parameter. with every selection of list/combo box it can be done by macro action "requery" in the list/combo box's "after update" event. is it right?

[zmbd]

There is no code, macro nor VBA, driving the subform updates.

The three subforms are linked in the properties...
for the subform showing the items requested:

Just noticed that "List11" this is very old database... now days I would have had something like. z_lst_labid for the name for the master field.
-z

[ariful alam]

@zmbd,

a BIG BIG & BIG thanks.

It may be done by the wizard when we add a subform. Is it?

[zmbd]

Hmmm... I tend to do these by hand as I rename controls... I haven't tried it using the Wizards... worth a try.

I gave up on the wizards years ago (except for the very few cross-tab) because I had to go in and change so much that I decided it was easier to just do the work by hand. :)

-z