Auto-fill field from related table

[Egg 'n' Bacon]

I'm not sure if I'll be able to describe this properly, but here goes.

I'm trying to create a form (FrmRoleRqdCourses), with a sub-form (SfrmCourseRole-role), to show/list/allow amendments from 2 related tables.
The main form is based on a table (TblRole) and is the 'one', in a one-to-many relationship to the contents of the sub-form.
The sub-form is based on an 'intermediate' table (tblCourseRole), which is also the 'many' (another one-to-many relationship) to a third table (TblCourse).
In the sub-form, I want to populate (or restrict) one of the fields with the Max, of the related field/record in TblCourse.

I have tried setting the default value, with SQL, refering to other fields, but to be truthfull, I've only got myself confused now.

Attached is a stripped-down version of what I'm working on.

TIA Paul

[HansV]

Before we go into your question: why do you have a CName field in tblCourseRole? Should the user be able to use a different name here than in tblCourse?

[JohnH]

CID is not the key for tblCourses. Instead it is a joint key from CName and Version.
So tblCourseRole needs both CName and Version to link back to the correct record in tblCourses.

But tblAttendance seems a bit confused about what its key should be.

I think I would break tblCourses into two: tblCourses with CID as Primary Key, then have tblCourseVersions with CID as a Foreigh Key, and a new auotnumber CourseVersionID.

I don't like multiple keys so I would add an autonumber to both tblAttendance and tblCourserole, and use them as the key, and add CourseVersionID as a foreigh key to both.

[Egg 'n' Bacon]

I understand where you're both coming from and the relationships (and PK/FK) with TblAttendance etc still need sorting.

The reason for the multiple key in TblCourse is dow to the fact that the unique records would be based on the two fields selected for PK.

To answer your question Hans, I want the control of the course names to be from within TblCourse. So no, the user should not be able to use a different name. This is a bit clunky, but it's to maintain consistency in the records. i.e. I don't want a course of "SWP01" and another of "SWP 01"

[JohnH]

You can create a unique index on a combination of fields without them being the Primary Key.
So personally, I always (well nearly) use a single field as the Primary Key, but if I want to restrict a combination of other fields to be unique, I create a unique on that pair of fields.

[Egg 'n' Bacon]

Ah, I see now. My understanding was that the PK should be based on the uninqueness of the records.

This highlights a gaping hole in my Access knowledge. How is a unique index, on a combination of fields, achieved please?

[HansV]

The screenshot below shows how a unique index on a combination of fields is defined:

x46.png

By entering a name for the index in the first row, and leaving the name column in the next row blank, you define an index on two (or more) fields.
In the lower half of the dialog, the Unique property of the index has been set to Yes (but the Primary property is set to No).

[Egg 'n' Bacon]

I can see the logic there & how to set indexes. Thank you.

Just one question on this though; I thought the use of indexes (or at least overuse) was slightly frowned on?

[HansV]

Overuse of indexes is indeed frowned upon, but indexes are very useful and sometimes essential:
- If you want to enforce a field or combination of fields to have unique values, you must create an index on that field/those fields.
- If you want to create an updateable query based on more than one table, you usually need to have a unique index on the join field in one of the tables.
- If you have a table with many thousands of records, and you need to sort and/or search a field frequently, it helps to have an index on that field.

But since an index takes up space, you should not create too many indexes. For example, it's not a good idea to create an index on every field that you might ever sort or search. Neither should you have two indexes on the same field; this can happen easily if you let Access create indexes automatically.

BTW, I would finish the data design first, including the indexes and relationships, and only then start to create forms. It's a lot of work to modify forms if you change the data design later on.

[Egg 'n' Bacon]

Thank you, that has helped my understanding of indexes and can now see some advantage to using them.

I'm a little curious about what the potential problems are with using multiple keys, though.

[HansV]

What exactly do you mean by 'multiple keys'?

[Egg 'n' Bacon]

Multiple fields as PK or FK

[HansV]

There is no objection to a composite primary key, but a single field makes it easier to join tables in the Relationships window and in queries.

[JohnH]

My internet connection dropped out before, but I am back now.

The problem with using multiple fields as the Primary Key is that any expression that wants to identify a record is made more complicated. The same applies to linking forms and subforms (reports and subreports).
And once you have two fields in this table, you typically find the next table has 3 fields, then the next can have 4. etc

But having single fields has Primary Key is not obligatory.

Here is a view where each table has a single key.

Training.gif

But I don't understand tblAttendance. When I have done something similar, I would have a table that represents instances of a course, and it is instances of a course that people attend. Sometimes a course refers to a specific body of knowledge, sometimes it refers to something that happened, with a start date and an end date etc. I use instance of a course to refer to the latter.

[Egg 'n' Bacon]

Thank you John, I can see where you're coming from now.

Regarding TblAttendance, part of the problem is the use of the word "course". A better word would be "skill" (as in a required skill). As many people would need many skills (an not always correlating with each other) TblAttendance seemed to be a logical solution for a many-to-many relationship.

[Egg 'n' Bacon]

So I have now sorted the PKs & indexes, thank you both for your patience and knowledge.

My original question remains, however;
I have a form (based on TblRole) with sub-form (based on TblCourseRole). How do I get the sub-form automatically populate a field from a related value in a third table (TblCourse)? In the stipped version of my DB, I'd like to have the CVersion field of the sub-form automatically show the Max (of the related record) after CboCrsName has been used to select a Course

[HansV]

In the relationship between tblCourse and tblCourseRole, you have joined CID to RoleID instead of to CourseID.

tblCourseRole should *not* contain a CName or CVersion field. They can be looked up from the CourseID field. It needs only CourseID and RoleID, and the combination of these should be the primary key.

Getting the highest version number requires you to jump through some hoops in code. See the attached version.

Copy (2) of TrainingDB.zip

[Egg 'n' Bacon]

Many thanks Hans, that's the sort of thing I had in mind. Though my mind has not been working too well of late

I see what you mean about the code; I would never have got that.

[Egg 'n' Bacon]

Just wondering about the sub-form; is it possible to have that as a continuous form, without shed loads of work?

[HansV]

It would be extra work, because the course name combo box is unbound, so it would display the same value in all records of a continuous form. And the version combo box would have the same row source (list) in all records.
If you really want it, I can look into it later today.