Problem with code in a Form

[MikeBres]

Hi folks,

I'm trying to make a form that selects from a listbox and
displays the selections in another listbox. I am setting
a value on the InUse field, then trying to requery the
new list so that it shows all the records with the InUse
field set to True. What happens is: I select the
record, click on the command button, and nothing is added
to the new listbox until I go to a new record and click
the command button again. Then the previous record is
added to the new listbox.
Can anybody tell what I am doing wrong? Here is my code.
The table has three fields, ZIPRange, Destination, &
InUse. 'List0' has the complete table of destinations
listed and 'List2' displays the destinations with only
InUse set to True.

Private Sub Command6_Click()
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

rst.findfirst "[Destination] = """ & Me.List0 & """"
Me.Bookmark = rst.Bookmark
rst.Edit
Me.InUse = True
rst.Update
rst.Close
Me.List2.Requery
End Sub

 

[Wayne Morgan]

You are editing rst, the RecordsetClone, but setting Me.InUse=True then
saving the changes you made (actually didn't make, the change was made on
the form) to rst.

After the Me.InUse=True try
Me.Dirty = False
to save the change then requery the listbox.

Also, in your search, you set Me.Bookmark=rst.Bookmark without checking to
see if the item was even found. To see if the item was found you could use

If Not rst.NoMatch Then
'move the form record here
End If

or you could just do the search on the form's recordset in the first place
(as of Access 2000).
Me.Recordset.FindFirst "[Destination] = """ & Me.List0 & """"