I'm very new to VBA and have a master detail form. The master is: Teacher Absences.
Detail #1: [Substitutes available]
Detail #2: [Call Log]

I'd like to track calls made until I find a Substitute to fill an absence. I added a call_log table with absenceid#, subid#, date_time, and status. Status can be: ACCEPTED, DECLINED, REJECTED, WAITING.

Requirements:
1. When I'm on the Available Subs subform, I'd simply like to click a button [Call] prior to calling someone and have it log it. That is, I've called this Sub.

2. When I've found someone for the call, I'll click [Fill Absence], and it will automatically set the call status to APPROVED for the "current substitute" record, then jump to another form to show the Assignment.

3. I'd really like to have the substitute records in the [Substitute Details] form HIGHLIGHTED if I've called them.

When I wrote the code to do requirement#1, the subform for the call log shows #Name? in the fields after I click the [Call] button. Here's the code:

Private Sub cmdCall_Click()
    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim numsubstitute As Integer
    Dim numabsence As Integer
    Dim bNoRecords As Boolean


    Set dbs = CurrentDb()

    numabsence = Forms![fill absences]!AbsenceID.Value
    numsubstitute = Forms![fill absences]!Available_Substitutes.Form!SubstituteID

    ' Set rst = dbs.OpenRecordset("Absence_Call_Log", dbOpenDynaset)

    bNoRecords = False
    Set rst = Me("absence_call_log").Form.Recordset

    If rst.EOF Then
     bNoRecords = True
    Else
     rst.MoveFirst
     rst.FindFirst ("AbsenceId=" & numabsence & _
                     " and SubstituteID=" & numsubstitute)
    End If

    If bNoRecords Or rst.NoMatch Then
     rst.AddNew
     rst("substituteid") = numsubstitute
     rst("absenceid") = numabsence
     rst("status") = "WAITING"
     rst.Update
    End If

    rst.Close
    Set rst = Nothing

End Sub


IF I open up a recordset for the entire table, then I can find the record but that seems "inefficient", after all, I have the records right there on the subform. The problem seemed to be caused by closing the recordset and setting it equal to nothing. AM I programmatically removing the underlying recordset at that point?

I rewrote the code as:

Private Sub cmdCall_Click()
    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim numsubstitute As Integer
    Dim numabsence As Integer
    Dim bNoRecords As Boolean

' MsgBox Me.Absence_Call_Log.Form.RecordSource, vbInformation, "Subform's Record Source"

    Set dbs = CurrentDb()

    numabsence = Forms![fill absences]!AbsenceID.Value
    numsubstitute = Forms![fill absences]!Available_Substitutes.Form!SubstituteID

    ' Set rst = dbs.OpenRecordset("Absence_Call_Log", dbOpenDynaset)

    bNoRecords = False

    Set rst = Absence_Call_Log.Form.Recordset.Clone

    If rst.EOF Then
     bNoRecords = True
    Else
     rst.MoveFirst
     rst.FindFirst ("AbsenceId=" & numabsence & _
                     " and SubstituteID=" & numsubstitute)
    End If

    If bNoRecords Or rst.NoMatch Then
     rst.AddNew
     rst("substituteid") = numsubstitute
     rst("absenceid") = numabsence
     rst("status") = "WAITING"
     rst.Update

     ' Unless this line was put in, I would not see the record I added.
     Absence_Call_Log.Form.Requery

    Else
     Absence_Call_Log.Form.Bookmark = rst.Bookmark
    End If

    rst.Close
    Set rst = Nothing

End Sub


This seems to do what I want. I just want clarification that this is a valid way of approaching it, and if possible, ANOTHER WAY of doing it (to assist with learning).

The reason I ask is that I read on P2P that I could use RecordsetClone and not have to refresh the form, but when I did that method, the recordset was always at EOF and it would always attempt to add new rows, and then violate the PK if you clicked on [Call] for the same person twice.

Also, an answer to Requirement #3 above.

Thanks to any/all suggestions!!