Link to home
Start Free TrialLog in
Avatar of Tom Lobb
Tom LobbFlag for Canada

asked on

How do I prevent trying to save a record when part of the primary key is null?

I have a data entry application. The records are claims that are being submitted for payment, and the claims are grouped in batches. The default action when the claim form is opened, is to add a claim. The primary key consists of the batch and claim numbers. Upon entry, the batch number is filled in but not the claim number, because that's dependent on the doctor chosen. Rather than enter a new claim, I choose to modify a previous claim that was rejected. So I use recordset clones to copy the data into a new record. All that works. But when I try to finish the process by syncing the recordset clone with the original recordset, I get an error that it can't save a record with a null primary key. I need to know how to delete the first record that was started upon entry or overwrite it with the new record.

Here's the code

        Set rstNew = .RecordsetClone
        Set rstOld = .RecordsetClone

        With rstOld
            With rstNew
                strCrit = "Claim='" & strClaim & "'"    ' to get original claim data

                For Each fld In rstOld.Fields
                    With fld
                        ' code that copies the data
                        ' from the original record to the new one
                        ' it works
                    End With

                ErrMsg = "Copying cleanup"
                Me.Bookmark = .Bookmark                 ' sync form to added record
            End With

        End With

Thanks in advance for any suggestions.

Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

Fields of Primary key must be always with proper values. You can have proper validation to ensure this before saving the record.
Avatar of Tom Lobb


I think that I found a solution. Before I start copying data, I check to see if the form is dirty and if it is, undo the record.

I'd still be interested if there are any other comments.


Avatar of Gustav Brock
Here is generic code to copy the current record: Copy record.

But that seems not to be your problem. However, I'm not sure I understand your scenario, but - at least - either make sure the form is saved before the copying, or block the copying if the form is unsaved. Alternatively, rethink your concept.
Check it deeply to ensure no incomplete keys/columns would be saved into the table. Pre-validation is always needed before saving the record.
 Thanks Gustav. That’s the code that I started with.

Thanks Peter. I understand about null valued keys. My problem isn’t trying to save the record, it’s needing to delete it.

I need to know how to delete the first record that was started upon entry or overwrite it with the new record.

Maybe we don't understand what you are doing. You don't overwrite with a new record, you edit the existing record. To use a new record, first delete the old one. Or rethink your process.
I guess one way (guessing) it would be to use an unbound form - fill the data and just insert them nice and neat since you need unknown keys during data entry
Thank you everyone. The undo is working, so that’s what I’ll go with. Sorry if I was unclear. Without the undo, when I tried to add a new record, Access tried to save the partially completed record, causing the error. But since I didn’t want the partially completed record, I just needed a way to get rid of it. Therefore the undo.
Avatar of Tom Lobb
Tom Lobb
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial