Update table from combobox from listbox selection

[New2VB]

Greetings Gurus

Some advice or help please...

My form has a listbox (list42) which contains names looked up from a table called AllNames. It also has a combobox which contains the possible status of the list42 names.
For e.g. "Bob" on list42 can be selected as "absent", "present" etc.

I am trying to code an event whereby, depending on the status set in the combobox, I can edit the name in AllNames & requery the form.
My code thus far:

Private Sub Status_AfterUpdate()

If Me.Status = "Do Not Contact" Then
    Dim coName As String
    coName = Me.List42.Column(1)
        Dim db As Database, tb As DAO.Recordset
        Set db = CurrentDb
        Set tb = db.OpenRecordset("AllNames", dbOpenDynaset, dbSeeChanges)
        tb.FindFirst "ID" = Me.List42.Column(0)
        tb.Edit
        tb!Status = Me.Status
        tb!Name = "XXX " & coName
        tb.Update
        tb.Close
        db.Close
End If

End Sub

Whenever the code executes I get an error "Update or CancelUpdate without AddNew or Edit"

If I remove the "ID" line then the code runs but it updates the wrong name (the first one in the table because it doesn't know which record to change) but I cannot find a way to get it to update the record selected in the listbox.

Thank you in advance.

 

[spikepl]

1. Insert

debug.print Me.List42.Column(0) to see what it contains

2. You seem to be re-storing a name, picked up from the same table via the combo, preceded by XXX. Why?

3. Name is a reserved word -use another designation for that field, or you will get into trouble sooner or later

 

[New2VB]

Hi Spike,

Thanks for the answer. I only just got it as I was posting a reply. FYI column0 contains the ID of the name, column1 contains the actual name, so that part was right. The "XXX" (now changed to ZZZ) is used to push the name to the bottom of the list so that I know to ignore those names.

Worked the answer out after a couple of hours away from the screen .

Code is...

Private Sub Status_AfterUpdate()

If Me.Status = "Do Not Contact" Then
    Dim coName As String
    coName = Me.List42.Column(1)
        Set rs = Me.Recordset
        rs.FindFirst "[ID] = " & Str(Nz(Me![List42], 0))
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
        rs.Edit
        rs![Name] = "ZZZ " & coName
        rs.Update
End If
Me.List42.Requery

End Sub

Hope this helps others in a similar situation

 

[spikepl]

Whatever

The customary test for something found (or not) by FindFirst is

If Not rs.NoMatch ' then it's found