How do I edit a recordset if it already exists and add a recordset if there isn't one yet in vba?

Question

I'm working on a project for school where I'm using a form to add/edit records in an Access database table. AccountID is the Primary Key used to link different tables, so I can't have duplicates of it. The AccountIDs are in a drop down list box on the form. Right now my code is: `

Option Compare Database

Private Sub btnAddRecord_Click()
    'Declare variables
    Dim db As DAO.Database
    Dim rst As Recordset
    
    'Set the current database
    Set db = Application.CurrentDb
    
    'Set the recordset
    Set rst = db.OpenRecordset("tblHOAFees")
    
    'Cycle through records
    Do While Not rst.EOF
        If rst!AccountID = lstAccountID.Value Then
            rst.Edit
            rst!HOAID = txtHOAID.Value
            rst!Location = txtLocation.Value
            rst!House = chkHouse.Value
            rst!Rooms = txtRooms.Value
            rst!SquareFeet = txtSquareFeet.Value
            rst!HOAFees = txtHOAFees.Value
            rst.Update
        Else
            rst.AddNew
            rst!AccountID = lstAccountID.Value
            rst!HOAID = txtHOAID.Value
            rst!Location = txtLocation.Value
            rst!House = chkHouse.Value
            rst!Rooms = txtRooms.Value
            rst!SquareFeet = txtSquareFeet.Value
            rst!HOAFees = txtHOAFees.Value
            rst.Update
        End If
    Loop
    
    
End Sub

` Right now when I select an AccountID from my list I keep getting Runtime error '3022'. It's saying my changes would create duplicates in the value index. I'm stuck, any help would be appreciated.

I've tried changing .Value to .Text, but I got a different error for that.

You are looping through the records, so if the id doesnt equal the lstAccountID value, you try to add multiple times. So maybe use a check to see if the account id exists as your if statement — Nathan_Sav, Dec 02 '22 at 13:13
As I read the code in the OP's question it is NOT moving through the recordset. It needs a .FindFirst or a .MoveNext somewhere. This link may help - https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/recordset-findfirst-method-dao — user10186832, Dec 02 '22 at 14:32
Was just writing what @user10186832 put about not looping. Even if it was looping though you'd still have problems as Nathan indicated: On the first record the Account ID's don't match as the Account ID is in record #5 so your code tries to add a new record and finds a duplicate. — Darren Bartrup-Cook, Dec 02 '22 at 14:36

How do I edit a recordset if it already exists and add a recordset if there isn't one yet in vba?

0 Answers0

Linked