Hi Kent
If you send me an email address, I will send you a Listbox Demo that show you how to flag an item to move to history, providing you add the appropriate field. Look at it and see if it gives you an idea on an approach to your proble,

[email protected]
John

Hi Kent
I think you should look at Rick's videos on Many to Many Relationships. That relationship is needed when you have a situation where you have many people (prisoners in your case) on one side and many Lessons in your case)on the other side. In a many to many relationship we need a third table to connect the other 2 tables. Rick's video will show you hos to do it. If you need additional help, let me know.
John

John, thank you for your reply.  I have replied to your email twice now, but I don't know if the problem is on my end or what.   I would love to get a copy of your demo database.  Please email me at [email protected] or let me know where I can snag a copy for download.

Watched Richard's Many to Many video and the extended cut (several times in fact).

- When I look up a Prisoner (student) using their TDCJ number, all is well using the ExistingStudentF
- Leaving the Prisoner (student) blank, I can make changes to the subform StudentLessonF with no errors.

Now the problem..........

If I look up a Prisoner (student) on the ExistingStudentF and try to make changes to the subform StudentLessonF the database complains that I must enter a value in the StudentT.TDCJ or if I select Next Student the database fields blanks like expected in the ExistingStudent form, but the error remains after I try to make changes on the StudentLessonF.

I tried placing a TDCJ from the ExistingStudentF on the StudentLessonF,  but it made no difference.

Private Sub TDCJ_AfterUpdate()
    
    StudentID = Nz(DLookup("StudentID", "StudentT", "TDCJ=""" & TDCJ & """"), 0)
    TDCJ = DLookup("TDCJ", "StudentT", "StudentID=" & StudentID)

End Sub
_____________________

So it looks like I need a way to change the StudentLessonF for each Prisoner (Student).

Any help would be most appreciated.

Forgot to mention I tried the above query and it gave the results but not sure how to relate them, if that makes sense.

I am still having difficulty with tying the subform StudentXLessonSubF which contains the history of each prisoner/student with the prisoner I look up by their TDJC number.  The code of the subform is below.  The screenshot for the connection table is included as well.

Option Compare Database
Option Explicit

Private Sub DateCourseReceived_AfterUpdate()
    
    Dim sDateCourseReceived As String
    Dim SQL As String
    
    Rem StudentID = Nz(DLookup("StudentID", "StudentT", "TDCJ=""" & TDCJ & """"), 0)
  
        ' Lesson Date Course Received
            sDateCourseReceived = DateCourseReceived
            
            SQL = "INSERT INTO StudentXCourseT (DateCourseReceived) " & _
            "SELECT """ & sDateCourseReceived & """"
            
            DoCmd.RunSQL SQL
            
        End If
        
End Sub

Private Sub LessonGraded_AfterUpdate()
    
    Dim sLessonGraded As String
    Dim SQL As String
    
   Rem StudentID = Nz(DLookup("StudentID", "StudentT", "TDCJ=""" & TDCJ & """"), 0)
  
        ' Lesson Graded Records
            sLessonGraded = LessonGraded
                      
            SQL = "INSERT INTO StudentXCourseT (LessonGraded) " & _
            "SELECT """ & sLessonGraded & """"
            
            DoCmd.RunSQL SQL
                    
End Sub

Private Sub LessonGrader_AfterUpdate()
    
    Dim sLessonGrader As String
    Dim SQL As String
    
    Rem StudentID = Nz(DLookup("StudentID", "StudentT", "TDCJ=""" & TDCJ & """"), 0)
  
        ' Lesson Grader
            sLessonGrader = LessonGrader
                      
            SQL = "INSERT INTO StudentXCourseT (LessonGrader) " & _
            "SELECT """ & sLessonGrader & """"
            
            DoCmd.RunSQL SQL
                    
End Sub

Private Sub LessonSent_AfterUpdate()
    
    Dim sLessonSent As String
    Dim SQL As String
    
    Rem StudentID = Nz(DLookup("StudentID", "StudentT", "TDCJ=""" & TDCJ & """"), 0)
  
        ' Lesson Sent Records
            sLessonSent = LessonSent
                      
            SQL = "INSERT INTO StudentXCourseT (LessonSent) " & _
            "SELECT """ & sLessonSent & """"
            
            DoCmd.RunSQL SQL

End Sub

Is there any advice for this last problem I have?  Any help would be most appreciated.

Thanks in advance