First of all, need to set subform container Master/Chilld Links properties so forms will synchronize related records.
Second, tblCast and your cascading combobox makes no sense to me. If anything, Character combobox should only list characters that are associated with movie. tblCharacter would be better named tblMovieCharacters and remove CastID_FK field. Have a textbox named tbxMovie in subform. Combobox properties:
RowSource: SELECT tblCharacter.CharacterID, tblCharacter.CharacterName FROM tblCharacter WHERE (((tblCharacter.MovieID_FK)=[tbxMovie]));
ColumnCount: 2
ColumnWidths: 0";1"
ControlSource: CharacterID_FK
Then you need VBA code that requeries combobox for each movie.
Code:
Private Sub Form_Current()
Me.frmMoviesSubFrm1.Form.CmbCharacterSelect.Requery
End Sub
Third, there is no need for CharacterName textbox nor code to save this value. CharacterID is saved via combobox and character name can be retrieved in query linking tables. Same for ActorActressName textbox. Change this combobox properties:
RowSource: SELECT tblCast.ID, tblCast.ActorActressName FROM tblCast;
ColumnCount: 2
ColumnWidths: 0";1"
ControlSource: CastID
Remove ActorActressName field in tblMovieCast. Should probably rename CastID to CastID_FK and MovieID to MovieID_FK. Be consistent with naming convention.
Remove CharacterID_FK and CharacterName from tblCast. Better table name might be tblPerformers and PerformerID as primary key field name.
However, this gets more complicated when you create a new movie record because that requires also creating character records. So will likely need combobox NotInList event code.