how to use one form for both new data entry and editng in ACCESS 2010

I have inherited a very complex tabbed form that is actually in the database twice.  Once for new data and another, virtually the same form, for editing data.  I am trying to get rid of the extra form and have only one, but attempts to use the form for both gets confusing.  When I open it and select a PersonID, if there is data for the person in one of the tables, it errros with the message cannot enter data twice.  Fine, I don't want to, but what is the easiest way to tell the form that (1) it is new data or (2) I want to edit existing data.  I am trying an option group where the user selects either New or Existintg as an option and then, with VBA, establish the appropriate datasource for the form as well as the combo box for PersonID.  The PersonID is based on whether or not they already exist in the case table.  Below is what I have, but I am not sure if this is the best approach.  I have not put error checking in yet as that will come later when I get this to work.

Private Sub fraNeworEdit_AfterUpdate()
Dim strSelect As String
Dim strSelectCaregiver As String

'Set values for combobox drop-down
If Me.fraNeworEdit.Value = 1 Then 'Case new case
    strSelectCaregiver = "SELECT PER.PersonID, PER.FirstName & ' ' & PER.LastName AS Fullname " & _
        "FROM tblPerson AS PER INNER JOIN tblCase AS CAS ON PER.[PersonID] = CAS.[PersonID] " & _
        "WHERE PER.PersonID <> CAS.PersonID AND PER.PersonType = 'Caregiver' "
ElseIf Me.fraNeworEdit.Value = 2 Then  'Edit an existing case
    strSelectCaregiver = "SELECT PER.PersonID, PER.FirstName & ' ' & PER.LastName AS Fullname " & _
    "FROM tblPerson As PER INNER JOIN tblCase ON PER.[PersonID] = tblCase.[PersonID] " & _
    "WHERE PER.PersonId = tblCase.PersonID "
End If

'Record source for form
If Me.fraNeworEdit.Value = 1 Then 'Case new case
    strSelect = "SELECT CAS.* FROM tblPerson AS PER INNER JOIN tblCase AS CAS " & _
                "ON PER.[PersonID] <> CAS.[PersonID] "
ElseIf Me.fraNeworEdit.Value = 2 Then  'Edit an existing case
    strSelect = "SELECT * FROM tblCase "
End If

Me.RecordSource = strSelect
Me.Requery
End Sub
Sandra SmithRetiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
From an outside view, the easiest method would be to leave the forms. I guess you have other things to look for in this inherited project, and that would be a better way spending your limited time.

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sandra SmithRetiredAuthor Commented:
Yes,, I wasted too much time already and no matter what I try, does not work.  Frankly, since this thing is now in my hands, am cleaning it up and revamping to normal, established standards so the next developer working on this mess does not have the same problems and it is in a usable condition.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.