troubleshooting Question

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

Avatar of Tom Lobb
Tom LobbFlag for Canada asked on
Microsoft Access
9 Comments1 Solution12 ViewsLast Modified:

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 CERTIFIED SOLUTION
Tom Lobb
Access developer / 1 web App

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 9 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 9 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004