May 23 2012, 05:04 AM
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
!TSR = GetTSRName
!DMSCode = Me.cboDMSCode
!FiscalYear = Me.cboFiscalYear
Me.Bookmark = rst.LastModified
That last line will produce the same error (index can't contain null value) as well.
May 23 2012, 05:33 AM
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.
May 23 2012, 05:01 PM
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?
May 23 2012, 05:10 PM
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?
May 23 2012, 06:42 PM
I had a Current event which turned out to be the problem. Thanks for your help!
May 23 2012, 08:09 PM
>> Current event <<
Yea, they can be tricky. Glad you got it worked out.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here