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