Cannot Update Record Error

[Stuart Grant]

I have a very simple Personnel Record Database. The main form has a
2-column ListBox, named NamesList, with LastName,FirstName with the Bound
column LastName. Then there are text boxes which give all the other details
in the main table - address etc.. You select the record by clicking
LastName in the list box and the details appear.

It used to work but now after editing an entry, when I try to move to
another record I get the error message "Cannot update etc because duplicate
in the index or primary key or relationship. None of which makes sense. The
Primary key is autonumber, the only field indexed is LastName and it has
duplicates allowed and anyway I did not change the last name.

The code to find the record set is

Private Sub NamesList_Click()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[LastName] = '" & Me.NamesList & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

It now chokes on Me.Bookmark = rs.Bookmark.

What has gone wrong ?

Stuart

 

[Willem]

How can you find the record on last name when duplicates are possible?
Try binding it on your autonumber.

 

[Willem]

Btw, for single select tasks, combo boxes are preferred (over
listboxes).

 

[Stuart Grant]

Willem

Good point. Ok I added the autnumber RecNo to the Query. Increased the
columns of the listbox to 3, made the columns RecNo, LastName,FirstName,
made the width of the first column zero. Comes up fine.

But when I click in the listbox nothing happens. I changed the code line to
rs.FindFirst [RecNo] = Me.NamesList.Column(1)
No error message but the record just doesn't change.

Do you know why

Stuart

 

[Willem]

Old VB bug/feature. Indices sometimes start with 0 (zero) and sometimes
with 1. In this case you need the first column, which is 0. Or just
omit the column like you had before should work fine too.