Did I really want to update that field !!

Posted on 2011-10-17
Last Modified: 2012-05-12
This is a slightly unusual high-level question!

I have a database of 5,000 students in a school.
It is mainly just static data, names and addresses etc.
However, the form is often viewed and there is always the danger that the user accidentally changes the "name" of the student or some other field.

I do NOT want to make this form read only.
Question: Is there any easy way I can flag a field so that .... it will give a warning message .. "Did you really want to change that field ? ".

I know how to do this in VBA but I am looking for something simpler (which may not exist!).
Ideally, a "property" that can be set to "Prompt  with Warning On Data Change".

Any thoughts??

Question by:Patrick O'Dea
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)

     Sorry, but all that is up to you<g>.  No such property or feature exists.

    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)

      As an a side, you could short change the process a bit by grouping the static fields together, make them disabled but locked for this form, then have everything else that is changeable below that.

      If the user wants to change one of the static fields, force them to click a button "Edit static data", then popup another form to edit only those fields.

      In that way they are acknowledging that they actually want to change one of those fields specifically.

    LVL 119

    Accepted Solution


    <I do NOT want to make this form read only.> Why NOT?

    if the record needs to be updated/edited, just open another form showing only the particular record for editing using a double click event on the record unique id field.
    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    The other thing that I do, although it uses VBA, is to lock the entire form, but provide an "edit" button so that the user is prevented from editing the form, unless they first click the edit button.
    LVL 119

    Expert Comment

    by:Rey Obrero
    you can also use the form's beforeupdate event..
    using the control/field  OldValue property

    Author Comment

    by:Patrick O'Dea
    Thank you all for your suggestions ... more welcome!

    I like the one from fyed in particular as it appears to suit my needs best.

    The other thing that I do, although it uses VBA, is to lock the entire form, but provide an "edit" button so that the user is prevented from editing the form, unless they first click the edit button.

    Any further contributions??

    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    actually, my comment just duplicates one of Jim's comments (two comments above mine).
    LVL 9

    Assisted Solution

    I would recommend presenting a dialog window to the user, to confirm that they really did want to modify the record.  This way, you don't have to make your form read-only, but it does provide a level of protection against unintended changes they may have made.  Here is a specific code example of such a solution:
    LVL 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<Any further contributions??>>

      Not in approach no, but to further fyed's comment (which is not a duplicate of mine), I do the same thing:

     Form screen shot showing edit button
     As part of that, I use the attached code to lock/unlock the controls on the form.

      I think though this goes beyond what you were looking for as you indicated you only wanted *some* of the controls to be checked.


    Function StdLockForm(frm As Form, intLockedState As Integer, Optional bolFlagRequiredFields As Boolean = False)
              'Lock/unlock all the fields in the detail section of the form that are enabled.
              'If unlocked, also set required fields back color to highlite them
              'If locked state, change them to white.
              Const RoutineName = "StdLockForm"
              Const Version = "1.3.0"
              Dim ctrl As Control                                    'Used for controls on form.
              Dim ctrlSF As Control                                  'Used for looping in subforms.
              Dim rs As DAO.Recordset                                'Recordset of the form.
              Dim strField As String                                 'Name of the field a control is bound to.
              Dim intRet As Integer
    10        On Error Resume Next
    20        Set rs = frm.RecordsetClone
    30        For Each ctrl In frm.Controls
                  ' Don't do any control execpt those in the detail section
    40            If ctrl.Section = acDetail Then
    50                With ctrl
                          ' Set locked state
    60                    Select Case .ControlType
                              Case acCommandButton
    70                            ctrl.Enabled = Not intLockedState
    80                        Case acSubform
                                  ' Have a subform control, which is special.
                                  ' First, it's always enabled so that the user can scroll
                                  ' through records even in inquiry mode, so we don't play with enabled
                                  ' at all.
                                  ' Second, we use the enabled flag to determine if controls need to be
                                  ' locked/unlocked.
                                  ' Third, we also need to handle the allowadds, allowdelete, and allowedit
                                  ' properties.  If we don't take care of these, user will be able to:
                                  ' 1. Never do anything (all the allows are turned off
                                  ' 2. Always be able to do something (all the allows are turned on
                                  ' To get around this, we expect all the default allow... properties to be turned off
                                  ' and the custom properties, EditAllowed, DeleteAllowed, and AddAllowed to be
                                  ' set indicating if these operations are allowed or no when in edit mode.
                                  ' Need to loop on the subform's controls, so call lock form again.
    90                            If ctrl.Enabled = True Then
    100                               intRet = StdLockForm(ctrl.Form, intLockedState, bolFlagRequiredFields)
                                      ' For a subform, we always turn these off.
    110                               If intLockedState = True Then
    120                                   ctrl.Form.AllowAdditions = False
    130                                   ctrl.Form.AllowDeletions = False
    140                                   ctrl.Form.AllowEdits = False
    150                               Else
    160                                   If ctrl.Form.EditAllowed Then ctrl.Form.AllowEdits = True
    170                                   If ctrl.Form.DeleteAllowed Then ctrl.Form.AllowDeletions = True
    180                                   If ctrl.Form.AddAllowed Then ctrl.Form.AllowAdditions = True
    190                               End If
    200                           End If
    210                       Case Else
    220                           If ctrl.Enabled = True Then ctrl.Locked = intLockedState
    230                   End Select
    240                   If bolFlagRequiredFields = True Then
                              ' Set the background color
    250                       Select Case .ControlType
                                  Case acTextBox, acComboBox, acListBox
    260                               If intLockedState = True Then
                                          ' Assign white as background
    270                                   ctrl.BackColor = vbWhite
    280                               Else
                                          ' Assign background color if required.
                                          'Ignore unbound, or bound to an expression.
    290                                   strField = ctrl.ControlSource
    300                                   If (strField <> vbNullString) And Not (strField Like "=*") Then
    310                                       With rs(strField)
    320                                           If (.Required) Or (.ValidationRule Like "*Is Not Null*") Then
    330                                               ctrl.BackColor = &H99CCFF
                                                      'Call MarkAttachedLabel(ctrl)
    340                                           End If
    350                                       End With
    360                                   End If
    370                               End If
    380                       End Select
    390                   End If
    400               End With
    410           End If
    420       Next ctrl
    430       Set ctrl = Nothing
    440       Set rs = Nothing
    End Function

    Open in new window


    Author Comment

    by:Patrick O'Dea
    Thanks sah18,

    Can I check something with you?

    1. Will your code undo ALL changes made on my form ?
    2. What if I had a subform - I presume your simple bit of code would need to be enhanced to reverse changes in a sub form??

    LVL 9

    Expert Comment

    I'm actually not sure about the subform.  I would create a copy of your database, apply the code, and test it out.  My first instinct says that no, this would not catch changes in a subform, since it is only referring to the main form event, but I'm not 100% certain.  But yes (aside from the subform uncertainty), it would undo all changes on the primary form.  Not sure if this is what you're looking for your not.  If you wanted to monitor only certain fields, you would need to code these individually field by field.
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)

     Yes, that would be correct (only affects main form).  Further complicating that, if you do have a subform, the main record is saved if it's dirty as soon as you enter the subform control.

     This is one thing Microsoft has never addressed and it's a royal pain for developers as the main/subform combo is one of the most used types of interfaces.

      They (Microsoft) did make a change that will let you do this, which is that you can now set a forms recordset in code.  This means that the recordset exists in a workspace you control and you can use a transaction.  But it's complicated to do and I've nver heard of anyone really using it a lot.

      For one thing, it means you need to handle all the master/child linking your self.

      many continue to use the approach of copying the records to temp tables (when the user hits edit) and have the form edit against the temp tables.  Then when the user clicks save, they copy everything back.  If the user clicks cancel, they do nothing.


    Author Comment

    by:Patrick O'Dea
    Thanks JDettman,

    This is turning out to be a very informative discussion.

    Can I ask one final question.
    If I have a form AND a sub form then .. will the following work ? What is your gut feeling on it?

    a) Set both to read only
    b) Click "Edit" which will set both form and sub form to "edit" mode.

    LVL 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<a) Set both to read only
    b) Click "Edit" which will set both form and sub form to "edit" mode.>>

      Just be aware that any controls you have for seaching, etc will be disabled as well.

      Really the overall best approach is locking/unlocking individual controls.


    Author Closing Comment

    by:Patrick O'Dea
    Thanks for all contributions ... very helpful

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now