Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

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
Me.Refresh

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
Me.Requery
End Sub

Open in new window

Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

I think something is missing in your description...it would be much faster to include a sample...this "freeze" is rather strange
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.
Avatar of Sandra Smith

ASKER

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.
KIN-DEVELOPMENT_CaseData.accdb
ErrorMessage.png
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.
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
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.
Why is this tagged "Visual Basic Classic", "Databases", and "VB Script"? Only the two remaining are valid.

/gustav
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.

/gustav
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
I am going to post another question about the Before update event.