Opening and closing record sets

[Nickaroo]

I have two tables, with a one to many relantionship. Main (Customer record) and many(billing records). Permit# as a index key

I am opening and closing the(billing table) recordset every time the Customer records moves from one to the next.


Is there a more efficient way doing this than what I have listed below?

Thanks.



Private Sub UpdateHistoryRecord(Pf As String)
Dim rst As ADODB.Recordset, countrecords As Integer
Set rst = New ADODB.Recordset
selectionrecords = "select * from history where dispos in('HE','EM')" _
& " and permit = '" & Pf & "'" & " order by datepermit"
countrecords = 1
rst.Open selectionrecords, CurrentProject.Connection, adOpenKeyset, adLockPessimistic, 1
With rst
If .RecordCount > 0 Then
.MoveFirst
While Not .EOF
.Fields("history&quot = countrecords
countrecords = countrecords + 1
'End If
.MoveNext
Wend
End If
End With
rst.Close
Set rst = Nothing

End Sub


Private Sub UpdateRecords_Click()
Dim rst As ADODB.Recordset, permitf As String
Set rst = New ADODB.Recordset

rst.Open "armaster", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
With rst
.MoveFirst
While Not .EOF
permitf = .Fields("permit&quot
UpdateHistoryRecord (permitf)
.MoveNext
Wend
rst.Close
MsgBox "Sucessfull ", vbInformation, "UPDATE"

End With

Set rst = Nothing

End Sub