Add a New record vs Edit Existing

[WineSnob]

I am using the following code to Add a new record to a table. This works fine. However how do I modify the code to find an existing record with the ProposalID and Edit it instead of Adding a New. Do I need some sort of If statement before the AddNew or change the OpenRecordset ???? Not sure how to check to see if there is an existing record. Also this code is on a Form_Close event. Maybe I should change that to dirty????

Private Sub Form_Close()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblProposalValues] WHERE ProposalID = 0")
    With rs
        .AddNew
        ![ClientID] = Me.txtClientID
        ![ProposalID] = Me.ProposalID
        ![Bucket1Final] = Me.txtBucket1Final
        ![Bucket2Final] = Me.txtBucket2Final
        ![Bucket3Final] = Me.txtBucket3Final
        ![Bucket4Final] = Me.txtBucket4Final
        ![Bucket5Final] = Me.txtBucket5Final
        ![Bucket6Final] = Me.txtBucket6Final
        ![Bucket7Final] = Me.txtBucket7Final
        ![Bucket8Final] = Me.txtBucket8Final
        ![Bucket9Final] = Me.txtBucket9Final
        ![EstateBucket] = txtEstatePV
 
 
        .Update
    End With
 
 
 
End Sub

 

[mdlueck]

1) Change your SELECT to zero in on exactly the one record which needs to be edited
2) Drop the call to .AddNew

Just that might be enough, assuming your code works for INSERT.

 

[WineSnob]

I want to EDIT the record if it exists but AddNew if it doesn't. With your suggestion I get too few parameters expected.

 

[mdlueck]

The SELECT gets the daoRS object associated with a table.

The adoRS.AddNew does an INSERT into said table which the daoRS is already attached to.

You would already need to know if a record existed in the table, have done a specific SELECT to obtain that record, read the daoRS attributes to populate an edit record form, then if Save/Commit is requested, then read the form field controls back, place the data back in the daoRS attributes, and tell the daoRS object to Commit the UPDATE.

Sounds like you need to think through the shell game of where the data needs to go to accomplish what you want to do. The data flow is a bit different between INSERT and UPDATE.