For won't work for new and existing data ACCESS 2010

I have a data entry form that when it opens, defaults to going to a new record.  There is an option group at the top that has two selections New and Existing.  However, when I check Existing so the customer drop-down changes to only existing, it freezes.  that is, the drop down does have the correct information, but it will not let me select name, nor can I change the option group back to New.  It won't let me enter data in the detail section.  I do not want to have to create two forms that are basically the same thing.  But how do I get it to switch between being able new records and edit existing records?  Below is the code behind the option group. The actual record selection for existing client is done when the user makes the selection from the combox box, but then again, I can't even get to it.

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.LastName, PER.FirstName & ' ' & PER.LastName AS Fullname " & _
                         "FROM tblPerson AS PER LEFT JOIN tblCase AS CAS ON PER.[PersonID] = CAS.[PersonID] " & _
                         "WHERE PER.PersonType ='Caregiver' AND IsNull(CAS.PersonID) "
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 AS CAS ON PER.[PersonID] = CAS.[PersonID] " & _
    "WHERE PER.PersonId = CAS.PersonID "
End If

Me.cboPersonID.RowSourceType = "Table/Query"
Me.cboPersonID.RowSource = strSelectCaregiver

Debug.Print "Select Caregiver: " & strSelectCaregiver

'Record source for form and change properties
If Me.fraNeworEdit.Value = 1 Then 'Case new case
    strSelect = "SELECT * FROM tblCase "
    Me.DataEntry = Yes
    DoCmd.GoToRecord , , acNewRec
    Me.AllowAdditions = Yes
    Me.AllowDeletions = Yes
    Me.AllowEdits = Yes
    Me.AllowFilters = Yes
ElseIf Me.fraNeworEdit.Value = 2 Then  'Edit an existing case

    strSelect = "SELECT tblCase.*, tblPerson.LastName, tblPerson.FirstName " & _
        "FROM tblPerson INNER JOIN tblCase ON tblPerson.[PersonID] = tblCase.[PersonId] " & _
        "ORDER BY tblPerson.LastName, tblPerson.FirstName "
    Me.DataEntry = No
    Me.AllowAdditions = Yes
    Me.AllowDeletions = Yes
    Me.AllowEdits = Yes
    Me.AllowFilters = Yes
End If

Me.RecordSource = strSelect
End Sub

Open in new window

Sandra SmithRetiredAsked:
Who is Participating?

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

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.

John TsioumprisSoftware & Systems EngineerCommented:
I think something is missing in your would be much faster to include a sample...this "freeze" is rather strange
Rey Obrero (Capricorn1)Commented:
try creating a new form, add the option group and the combo box
-use the same codes you have above, up to the point where you change the row source

see how the combo box will behave on the new form.
Sandra SmithRetiredAuthor Commented:
I made some changes and at least the drop-down now work, but am getting other errors. I HATE BOUND FORMS!  I have attached the database with the relevant tables and form as well as a screen shot of the error messages.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Sandra SmithRetiredAuthor Commented:
I have spent two solid days on this and have gotten nowhere.  I give up and simply going to make it unbound form and create code to do what I want.  It says it cannot create duplicate record when I try to move, it won't save data and it is just a pain.
Rey Obrero (Capricorn1)Commented:
sorry, can't open your Access file now..

but the messages are clear..

try cutting lines 5 to 18 and place them after line 46
Sandra SmithRetiredAuthor Commented:
Rey, did not work.  I am giving up and simply making it unbound and updating/adding the recordset.  I have NEVER gotten bound forms to work the way I want and at least I know the recordset code will work.
Gustav BrockCIOCommented:
Why is this tagged "Visual Basic Classic", "Databases", and "VB Script"? Only the two remaining are valid.

Gustav BrockCIOCommented:
Your problem is that you use the customer/caregiver/PersonID combobox to search for a record while it is bound (to the current record).

Thus, you change the record without saving, then search. An edit at that point is bound to fail.

A combobox intended for searching must be unbound.

If you use Access, live with bound forms and play by the rules. If you "hate" bound forms - which is quite fair - then Access isn't for you. If so, seriously, look into Visual Studio where the options are wide open and you are free to set the rules.

I am always amazed by people who say they hate bound forms.  Once you understand the event model, you have as much control as you need.  In my 20 years of using Access, I have found only one event I ever wanted that wasn't there and that one is "LeavingRecord".  I want this to be the last event fired BEFORE the form moves to a new record or closes.  I spent many years writing COBOL applications where I had complete control over forms as long as I was prepared to code.  With Access, I found I had as much control without any of the tedious code to make things happen once I stopped trying to control everything and accepted the "Access way".  Just learn what causes an event to fire and you'll have a much better understanding of where you need to place your code to get the effect you are after.

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:
Pat I think it has a lot to do with how one is trained, and I was trained to use unbound forms and I understand them.  However, I have come cross database that use bound forms so am trying to get educated on their use.  If it cuts down writing code, all for it.  Sounds like I need to go back and really investigate event sequence firing.
The form's BeforeUpdate is the small end of the funnel through which ALL updates MUST pass.  That makes it the most important event in the entire form model.  Some validation can be done in other events but think of this event as "the buck stops here".  I acquired an application that had at least 5,000 lines of code in one form and the vast majority of it was useless because it was in the wrong events and so the validation was not actually happening.  Apparently, each time the validation failed to work in one event, the developer copied it and pasted it into another event.  He was also doing cumulative validation so the on exit event of fld1 validated fld1.  The on exit event of fld2 validated fld2 AND fld1.  The on exit event of fld3 validated fld3 AND fld2 AND fld1, etc.  Oh, the users got error messages, but the bad data still got saved.  I reduced the code to under 200 lines and put it where it belonged (most went into the form's BeforeUpdate event) and that stopped the bleeding so no more bad data was saved.
Sandra SmithRetiredAuthor Commented:
Good grief, I am not the only one that has come across this type of coding before!  I am currently working on a database that had five previous developers and none of them ever took note of what was done before so yes, repeating code, forms, tables and reports.  I finally told the client that I would NOT work on it unless I could start over again and rewrite it.  So far, I have removed 126 tables (yes, 126), and am down to about 20 that really do the work and are the basis for the forms that are currently actually being used.  There are over 500 hundred queries, but am not even going through them, simply going to review the reports they use and re-create them.  I think the reason I like unbound is I can keep validation in the minimum number of procedures and reuse them, (that is call them, not copy them).,  But you mentioned the Before Update event and let me know if this should actually be another question, is that the event I should be using to call my validation?  I would really like to understand how to use them, that way, code could be reduced even further.
Sandra SmithRetiredAuthor Commented:
I am going to post another question about the Before update event.
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.