RecordsetClone Problems

[EddyLLC]

I am trying to use a combo boxes AfterUpdate event to create a new record in a table then populate the form the combo box is on with the date from that record. I am using the following code I cut and pasted from various sources with no luck. the record is created but the new record does not appear on the form until I leave the form and come back. How do I call the newly created record up so it appears on the form? Thanks.

Private Sub Select_AfterUpdate()
Dim strReference As String
Dim rs As Recordset, db As Database
On Error GoTo Err_Select_AfterUpdate

Forms!frm1.RecordsetClone.FindFirst "[Reference] = '" & Forms!frm1![SelectRef] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
strReference = SelectRef


If SelectRef <> Reference Then
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl1", dbOpenDynaset)
rs.FindFirst "[Reference] = '" & Forms!frm1![SelectRef] & "'"
If rs.NoMatch Then
rs.AddNew
rs!Reference = SelectRef
rs!SeqNo = 1
rs!Field1 = Forms!frm1!Name
rs.Update
End If


Dim MyRS As Recordset, criteria As String
Me.Dirty = False
Set MyRS = Forms!frm1.RecordsetClone
' Build the criteria.

criteria = "[Reference]=" & "'" & Forms!frm1![SelectRef] & "'"

' Perform the search.
MyRS.FindFirst criteria
If Not MyRS.NoMatch = True Then
' Synchronize the form's record to the dynaset's record.
Forms!frm1.Bookmark = MyRS.Bookmark
End If
MyRS.Close
Set db = Nothing

 

[Remou]

You need to requery:

<...>
        If rs.NoMatch Then
            rs.AddNew
            rs!Reference = SelectRef
            rs!SeqNo = 1
            rs!Field1 = Forms!frm1!Name
            rs.Update
        End If

[b]Forms!frm1.Requery[/b]
<...>