I'd like to be able to add a record to a form using VBA, but when I do that, the form is left in a state where clicking on anything gets me an error telling me that the index or primary key can't contain a null value. I have to press ESC to get out of it. When I look at the underlying table afterwards, the record is being added, but the form doesn't handle it very well. Here's the code I'm using:

Private Sub btnAddToHospitals_Click()
Dim rst As DAO.Recordset
Set rst = Me.Recordset
With rst
.AddNew
!TSR = GetTSRName
!DMSCode = Me.cboDMSCode
!FiscalYear = Me.cboFiscalYear
.Update
End With
Me.Bookmark = rst.LastModified
End Sub

That last line will produce the same error (index can't contain null value) as well.

I don't generally add records like this, but try running a Me.Requery on the form to see if it pulls a new record in? Depending on the form's recordset type it may need a manual requery.

hth

I get the same error when doing a requery. It's really strange what it does. I find one of the existing records in edit mode as a new record, yet the error I get is not about creating duplicates, but about a null primary key, which it isn't.

Anyway, the fields I'm adding data to for the new record aren't supposed to show, but I can just hide them and then fill them in directly on the form using VBA.

How would you handle that?

What is the recordsource of the form? I've added records using similar methods and that's a first for me. It sounds to me that there's something that need to be handled that was handled automatically when you edit it interactively.

Do you have any code in BeforeUpdate event or BeforeInsert event?

I had a Current event which turned out to be the problem. Thanks for your help!

>> Current event <<

Yea, they can be tricky. Glad you got it worked out.

Cheers,