Updating table through a form (1 Viewer)

Hi,

I'm using Access 2010. I have created a table and a form to enter new records into the table. The form is controlled by a combo box. In the combo box, I can enter a new value for the primary key and fill out the rest of the form to create a new record.

Alternatively, I can select an existing value of the primary key from the combo box. When I select an existing primary key from the combo box, I have written an Event Procedure that runs "On Change". The procedure loads the values for the chosen record directly into the form.

Once I've loaded an existing record into the form, I would like to change the value of one of the fields and then update the record. However, Access does not let me do this because it says that it would create a new record and I can't have duplicate primary keys.

How do I simply update the record in the form after loading existing values it rather than duplicating the record?

I would do this with 2 forms.

Form 1 is unbound (not based on a table) and has a drop down with existing records you can select and underneath it a button that says 'Open'. You select a record and click that button and Form2 opens to that record.

Also on Form1 is another button that says 'Add New' which opens up Form2 to a new record you can add data to.

Form2 is the real key. It should be bound to your table. That way it can open/edit an existing record, or add a new one based on the command it receives.

Lastly, to open the form you will need to use the Docmd.Open form method - https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-openform-method-access

have your combobox bound to your PK field.

@Plog - is creating two forms the only way? That seems really cumbersome.

@arnelgp - in the property sheet for my combo box, under data tab, I have bound column set as 1. Doesn't that mean i'm already about to my primary key? Should I set bound column to 0?

you are already set!

since the combo is bound, and i will assume the rest of the textboxes on your form is also bound, there is no need to to put code on the "OnChange" event on the combo.

s creating two forms the only way?

Click to expand...

No it's not.

ok here it is.
remove the OnChange Event of your combo.
instead add code to it's AfterUpdate event:

Private Sub combo_AfterUpdate()
Dim strComboValue As String
Dim rs As DAO.Recordset
Dim bk As Variant
strComboValue = Me.combo
Me.Undo
Set rs = Me.RecordsetClone
With rs
.FindFirst "[PKFieldName]='" & strComboValue & "'"
If Not .NoMatch Then
bk = .Bookmark
End If
End With
Set rs = Nothing
If IsEmpty(bk) Then
DoCmd.GoToRecord , , acNewRec
Me.combo = strComboValue
Else
Me.Bookmark = bk
End If
End Sub