Finding and Editing a Record with VBA

[shurgs]

Hey, I'm stuck (again)
So, I want to look up a record in my table "youngperson" using the current value of a combobox and then update the "Outcome" field with some text. This is what I'm trying:

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim i As Integer
        
        
        Set db = CurrentDb
        Set rs = db.OpenRecordset("youngperson")
        rs.Edit
        rs.MoveFirst
        For i = 1 To rs.RecordCount
            If (rs("ypid") = Me![com_yp]) Then
                rs("Outcome") = comOutcomes.Text
                lblOutcome.Caption = rs("Outcome")
                rs.Update
            Else
                rs.MoveNext
            End If
        Next
        rs.Close

Any ideas would, as always, be greatly appreciated!
Thanks for readin'
Shurgs

Edit: I just changed the code to this and I think I'm on the right tracks now. I'll tell ya if it goes wrong.

 

[WayneRyan]

shurgs,

Dim rs As DAO.Recordset
        
Set rs = CurrentDb.OpenRecordset("Select * From youngperson where [ypid] = " & Me.[com_yp])
If rs.EOF And rs.BOF Then
   MsgBox("No Matching Records.")
   Exit Sub
Else
   rs.Edit
   rs.Outcome  = comOutcomes   ' --> Removed the ".Text", that's for the OnChange event ONLY
   lblOutcome.Caption = rs.Outcome
   rs.Update
End If

hth,
Wayne

 

[boblarson]

Why use a recordset and not an UPDATE QUERY instead? An Update Query is much more efficient.

 

[WayneRyan]

Bob,

Good idea, but shurgs also wanted to retrieve a value from the table:

lblOutcome.Caption = rs.Outcome

btw, Happy Birthday !!!

Wayne

 

[boblarson]

Bob,

Good idea, but shurgs also wanted to retrieve a value from the table:

lblOutcome.Caption = rs.Outcome

Ah, didn't see that one.

btw, Happy Birthday !!!

Wayne

Thanks

 

[shurgs]

Hi Wayne. I was unaware you could do that! Very helpful indeed, thanks! -Shurgs

 

[shurgs]

Reopening an old thread here. This code works, but occassionally I get this error message when I use the dropdown box to select the new Outcome.

"The data has been changed. Another used edited this record and saved the changes before you attempted to save your changes. Re-edit the record."

Now I know for a fact that there's no one else using this database other than me. Do you think perhaps when it says "another user" it is infact referring to the code in some way trying to edit the record twice at the same time? Is that even possible? I mean, it's not a major issue as you can just close it, re-open it and edit it again. I don't expect you to debug my code for me, but I'm just wondering if anyone else has had this issue before?

 

[boblarson]

If you are updating the same table that the form has open and there have been changes to the form, it would create a situation like that.

 

[shurgs]

I have a Query Subform of the Progress Table, which is related to the Young Person table I am editing with this code. Could be the problem? There's not really any way I can get rid of that subform though, so if that is the problem then I guess there's nothing I can do about it...

 

[boblarson]

Just to note - a common mistake is for people to put a table from the main form into the subform's record source when it should not be there. Perhaps you have done this.