Refreshing List Box

[JamesJoey]

I have a single record form that I placed a List Box on.
The form and the List Box are based on the same table.
I'm synching selected value in the list box to sync with the form's date
using the following code in the After Update of the List Box:

Private Sub lstListing_AfterUpdate()

   Dim rs As DAO.Recordset
   Set rs = Me.Recordset.Clone
    
   With rs
     .FindFirst "ContactID=" & Me.lstListing
     If Not .NoMatch Then
        Me.Bookmark = .Bookmark
    End If
End With

End Sub

This works fine but I'm running into a problem when I try to update the List Box data after I've made
changes to the form's date.

When I make changes to the form's data I use the After Update of form to requery
the form and lIst Box:

Code:
Me.Requery<br> Me!lstListing.Requery

Problem is I get Runtime error'3021': No current record.

Any ideas how I can make sure the data in the list box syncs with the form dara?
James

 

[theDBguy]

Have you tried stepping through all the form's code? Does it tell you which line is causing the error?

 

[JamesJoey]

Yes:
Me.Bookmark = .Bookmark

 

[cheekybuddha]

' ...
   Set rs = Me.Recordset.Clone     ' This is ADO
' ...

' Instead use:
' ...
   Set rs = Me.RecordsetClone      ' This is DAO
' ...'

 

[arnelgp]

not sure but you may try:

With Me.lstListing
    .RowSource = .RowSource
End With
Me.Requery

 

[JamesJoey]

I don't get the error when I insert a 'On Error Resume Next' before '.RowSource = .RowSource'
But then I'm not sure if that's a good idea?

 

[arnelgp]

Me.Bookmark = .Bookmark

which part of the code does the above belong, your listbox?
i don't think Requerying your form or listbox will trigger the AfterUpdate event of the listbox.
when you Requery the form, all Bookmarks are become invalid and
new "bookmarks" are created.

 

[MajP]

No real need to use the bookmark and can all be done in one line of code.

   Me.Recordset.FindFirst "ContactID=" & Me.lstListing

If it exist the form moves if not it does not move.

 

[JamesJoey]

No real need to use the bookmark and can all be done in one line of code.

   Me.Recordset.FindFirst "ContactID=" & Me.lstListing

If it exist the form moves if not it does not move.

The code here worked fine.
No more error.

Thanks to all for the help,
James