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
.AddNew
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
Next
ErrMsg = "Copying cleanup"
.Update
.MoveLast
Me.Bookmark = .Bookmark ' sync form to added record
.Close
End With
.Close
End With
Thanks in advance for any suggestions.
ASKER
I'd still be interested if there are any other comments.
Thanks
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.
ASKER
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.
ASKER
Fields of Primary key must be always with proper values. You can have proper validation to ensure this before saving the record.