In the query for the select assessor combobox you can have a field like: AssessorName: Surname & ", " & FirstName - you can sort on that. Whenever you want to display their name you can concatenate the two fields however you want.
The team will be a combobox with the district and the org ID's included in the row source. Once they select the team (AfterUpdate) populate those two fields from the combobox. They will both be Enabled=False and will be bound fields.
You need a combobox to select assessments.
For the ass-nbr, add this code to your add new button:
Code:
Dim NewNbr As Long
On Error Resume Next
NewNbr = DLookup("AssessmentNumber", "TblAssessment", "ChildID=" & Me!HoldChildID)
If NewNbr > 1 Then
Me!AssessmentNumber = NewNbr + 1
Else
Me!AssessmentNumber = 1
End If
'add the new number to the combobox
Me!SelAssessment.Requery