Solved Displaying related records on a form (in a subform?) (2 Viewers)

[John O'Malvern]

I have a main form displaying fields from my table of members (PK ContactID)
Members can have more than one category (Bike, car, commercial, minibus) and these values are listed in a table:-

(I think that Category Type in tblContact_Categories is superfluous, and I will delete if so. )

At the moment I have a subform that displays correctly any and all categories for a member, but I cannot (and need to be able to) add categories via that subform (or equivalent).
I have tried every thing I can think of but have failed.

I would like it to look more or less like the current , but better integrated into the main form if possible. I want all 2 or 3 or 4 categories to display in a list when they do belong to that member.
Please.

 

[DHookom]

The record source of the subform should be tblContact_Categories with a single combo box bound to the CategoryTypeID field which uses tblCategoryTypes as its Row Source. Set the link master and child fields to the ContactID and make sure the form allows additions and updates.

 

[GPGeorge]

I have a main form displaying fields from my table of members (PK ContactID)
Members can have more than one category (Bike, car, commercial, minibus) and these values are listed in a table:-
View attachment 123447
(I think that Category Type in tblContact_Categories is superfluous, and I will delete if so. )

At the moment I have a subform that displays correctly any and all categories for a member, but I cannot (and need to be able to) add categories via that subform (or equivalent).
I have tried every thing I can think of but have failed.
View attachment 123449

I would like it to look more or less like the current , but better integrated into the main form if possible. I want all 2 or 3 or 4 categories to display in a list when they do belong to that member.
Please.

I have a sample database which illustrates the concept Duane described.

https://www.gpcdata.com/Downloads/MainFormFilterSubform.ZIP

 

[John O'Malvern]

The record source of the subform should be tblContact_Categories with a single combo box bound to the CategoryTypeID field which uses tblCategoryTypes as its Row Source. Set the link master and child fields to the ContactID and make sure the form allows additions and updates.

Do you mean I should create a subform on my main form, and put a combobox on the subform? I tried to do that and the cmb always sits on the main form, not the subform.

 

[mike60smart]

Are you able to upload a copy of the Database with no confidential data?

 

[John O'Malvern]

I don't understand the consequences of this choice:

as I am sometimes inputting data here, I assume I need to 'store that value in this field' ,but
when I select that option, I get a list of the fields on the main form, not any to do with categories.

 

[mike60smart]

You need to be creating the Combobox on the Subform which would be based on your table "tblContactCategories"

 

[John O'Malvern]

GP George: That link doesn't work for me - HTTP 404.0 not found.

 

[Ken Sheridan]

Do you mean I should create a subform on my main form, and put a combobox on the subform? I tried to do that and the cmb always sits on the main form, not the subform.

You should design the subform separately from the parent form, and include the combo box in it. The subform can then be embedded in the parent form by dragging it from the navigation pane and dropping it into the parent from in form design view. The LinkMasterFields and LinkChildFields properties of the subform control (i.e. the control in the parent form which houses the subform) should then both be set to ContactID.

The attached file includes an example of a form/subform set up in this way. It also includes examples of other possible interfaces, but you can ignore them.

 

[John O'Malvern]

I'm getting closer..., but the combo box remains empty.
this is what I see:

This is its row source:

Control source is Category Type, bound column 1

for the subform,

What have I missed?
Many thanks.

 

[mike60smart]

Is the Subform for Category set as Continuous Form?

 

[John O'Malvern]

Is the Subform for Category set as Continuous Form?

Err, should that be an option under Name on the property sheet using the All tab? I don't see it.
EDIT - I had the frame of the form selected, not the body of the form (IYKWIM), and not it wasn't, but is now, and makes no difference.

 

[GPGeorge]

GP George: That link doesn't work for me - HTTP 404.0 not found.
Wow. I don't know how that folder got deleted. Thanks for the heads up. I'm rebuilding it now, but it will take a few minutes

 

[Ken Sheridan]

Control source is Category Type, bound column 1

The ControlSource should be CategoryTypeID. The Category Type column should be deleted from tblContact_Categories, as you identified yourself in your initial post. The combo box's ColumnWidths property should be 0 (Access will enter whatever unit of measurement you are using automatically). This hides the first column so that the value shown in the control will be that of the Category Type column in the referenced table. The value of the control, and hence in the column to which it's bound will be the value of the CategoryID for the selected category type.

 

[mike60smart]

Err, should that be an option under Name on the property sheet using the All tab? I don't see it.
EDIT - I had the frame of the form selected, not the body of the form (IYKWIM), and not it wasn't, but is now, and makes no difference.

Hi John

Attached is a rough example

 

[Ken Sheridan]

I had the frame of the form selected, not the body of the form (IYKWIM), and not it wasn't, but is now, and makes no difference.

Open the subform independently of the parent form in design view. Then select the Form object by clicking on the little square at the top left corner, at the intersection of the vertical and horizontal ruler bars. In the form's properties sheet's Format tab set the Default View property to Continuous Forms. Then save the amended design and close the form. When it opens as a subform in the parent form it should now show multiple rows, one for each category type selected, along with an empty row at the bottom for selecting a new category type. See the StudentCourses file I attached to post #9 for an example.

 

[John O'Malvern]

The ControlSource should be CategoryTypeID. The Category Type column should be deleted from tblContact_Categories, as you identified yourself in your initial post. The combo box's ColumnWidths property should be 0 (Access will enter whatever unit of measurement you are using automatically). This hides the first column so that the value shown in the control will be that of the Category Type column in the referenced table. The value of the control, and hence in the column to which it's bound will be the value of the CategoryID for the selected category type.

Thank you. I've ditched the Category Type column from tblContact_Categories, and also it's own ID numbering column (as I have two FKs to keep records unique.).
EDIT - oops sorry, i didnt see your latest post, before writing what follows. I'll check it out now.
I followed the example in your database (thanks) and although it works, I cannot delete a category once added (on the subform - I know i can go to the table and do it) .
Also I would like it to show all categories that a member holds, in some cases 3 [possibly 4] . I've created a list box also which highlights each category in turn [for a multi-category member]

(Mr J C )

but always shows all categories for everyone.

(Mr C C )

 

[mike60smart]

Thank you. I've ditched the Category Type column from tblContact_Categories, and also it's own ID numbering column (as I have two FKs to keep records unique.).
I followed the example in your database (thanks) and although it works, I cannot delete a category once added (on the subform - I know i can go to the table and do it) .
Also I would like it to show all categories that a member holds, in some cases 3 [possibly 4] . I've created a list box also which highlights each category in turn [for a multi-category member]
View attachment 123463 (Mr J C )

but always shows all categories for everyone.
View attachment 123464 (Mr C C )

John
Have you looked at the example I have uploaded?

 

[John O'Malvern]

John
Have you looked at the example I have uploaded?

No not yet. catching up...

 

[John O'Malvern]

Open the subform independently of the parent form in design view. Then select the Form object by clicking on the little square at the top left corner, at the intersection of the vertical and horizontal ruler bars. In the form's properties sheet's Format tab set the Default View property to Continuous Forms. Then save the amended design and close the form. When it opens as a subform in the parent form it should now show multiple rows, one for each category type selected, along with an empty row at the bottom for selecting a new category type. See the StudentCourses file I attached to post #9 for an example.

I did that to the Form's format property, but it hasn't changed the appearance of it, but I can scroll down to second or third records...

I'll take another look at your example DB.