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

[Ken Sheridan]

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]

To delete a category in the subform you need to delete the row, not just the value in the combo box. Click on the record selector to the left of the row, and press the Del key. You should then be prompted to confirm the deletion. This will delete the member's association with the category. It does not delete the category from the tblCategoryTypes table.

I don't quite follow what you are saying in the second paragraph above. Provided that the subform is in continuous forms view (like the courses subform in my demo) you'll see all the categories for the current member as multiple rows in the subform. Forget about a list box. I know my demo includes an example in which a student's courses are shown as selections in an unbound multi-select list box, but I've only included that because I've occasionally had requests from people who want to show related items in this way. As I say in the demo, I'd nevertheless recommend a conventional form/subform interface. Unlike a list box this is code-free, though you might want to include code in the combo box's NotInList event procedure to add a new category not yet represented in the list. My demo includes this functionality.

 

[tvanstiphout]

I don't understand the consequences of this choice:
View attachment 123450

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.

This asks the question: is the combobox unbound (not connected to a database field) or bound (connected to the selected field).
Often the second option is the correct one, because comboboxes are often used to select an ID value, while the user is looking at Text values. E.g. the dropdown has a RowSource of "select ProductID, ProductName from tblProducts" with ColumnWidths of "0;1" to hide the ID column, and will select a ProductID, but is showing ProductName to the user.
This is an important concept to grasp.

 

[mike60smart]

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...
View attachment 123465
I'll take another look at your example DB.

It looks like you may have extra space below the Combobox in Design View. Remove the extra space and you should be OK.

 

[John O'Malvern]

This asks the question: is the combobox unbound (not connected to a database field) or bound (connected to the selected field).
Often the second option is the correct one, because comboboxes are often used to select an ID value, while the user is looking at Text values. E.g. the dropdown has a RowSource of "select ProductID, ProductName from tblProducts" with ColumnWidths of "0;1" to hide the ID column, and will select a ProductID, but is showing ProductName to the user.
This is an important concept to grasp.

So 'store that value in this field' is the right choice when the combobox is bound, thanks.

 

[John O'Malvern]

It looks like you may have extra space below the Combobox in Design View. Remove the extra space and you should be OK.

Spot on!

To delete a category in the subform you need to delete the row, not just the value in the combo box. Click on the record selector to the left of the row, and press the Del key. You should then be prompted to confirm the deletion. This will delete the member's association with the category. It does not delete the category from the tblCategoryTypes table.

I don't quite follow what you are saying in the second paragraph above. Provided that the subform is in continuous forms view (like the courses subform in my demo) you'll see all the categories for the current member as multiple rows in the subform. Forget about a list box. I know my demo includes an example in which a student's courses are shown as selections in an unbound multi-select list box, but I've only included that because I've occasionally had requests from people who want to show related items in this way. As I say in the demo, I'd nevertheless recommend a conventional form/subform interface. Unlike a list box this is code-free, though you might want to include code in the combo box's NotInList event procedure to add a new category not yet represented in the list. My demo includes this functionality.

Thank you; all has come right now. I think I may have been confused by too much space on the subform, anyway removing it (and maybe something else changed ) and it all works.

In the unlikely event of another category being needed, I'll add a record to the table.
I did notice something odd: on your subform I see header and footer, but the property sheet says 'no' to 'Show...'

and the *opposite* with mine:

No doubt there is an explanation, but it eludes me!

It would be nice to make this subform invisible or disabled until some other data has been entered on the main form (as there is no ContactID until that has happened, so it throws an error, of course). I shall go and investigate doing that soon, but...

Many thanks to all for your contributions and help.

 

[Ken Sheridan]

It would be nice to make this subform invisible or disabled until some other data has been entered on the main form (as there is no ContactID until that has happened, so it throws an error, of course).

In my demo put the following in the Current event procedure of the frmStudents_3 form:

    Me.fsubStudentCourses.Enabled = Not Me.NewRecord

and the following in the same form's AfterInsert event procedure:

    Me.fsubStudentCourses.Enabled = True

 

[Ken Sheridan]

I did notice something odd: on your subform I see header and footer, but the property sheet says 'no' to 'Show...'

That property refers to the Page Header and Footer, not the Form Header and Footer.

 

[John O'Malvern]

In my demo put the following in the Current event procedure of the frmStudents_3 form:

    Me.fsubStudentCourses.Enabled = Not Me.NewRecord

and the following in the same form's AfterInsert event procedure:

    Me.fsubStudentCourses.Enabled = True

This worked a treat on your DB, of course!, and disables my subform as needed (great!) , but even when I enter two or three [at least] fields the SubForm only becomes enabled when I move to another record and back.
I tried putting the enable code in the After Update event of the form, but that didn't work either.
There is a lot of code (4-5 screen-fulls to give an idea) for this form, which I inherited, and which I'd have a lot of trouble understanding. (So I don't think I'm going to be able to find out why your suggestion isn't working on my form.)
Would the After Update event of the LastName field be a good choice to use? This works, (enabling the form) so unless it is bad programming practice for some reason , I'll use it. (We can assume that anyone using this DB will have to enter a LastName soon enough for this to work. )
Thanks again for the suggestion and the lines of code.