Did I really want to update that field !!

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??

Patrick O'DeaAsked:
Who is Participating?
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

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

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  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.

Rey Obrero (Capricorn1)Commented:

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

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
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.
Rey Obrero (Capricorn1)Commented:
you can also use the form's beforeupdate event..
using the control/field  OldValue property
Patrick O'DeaAuthor Commented:
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??

Dale FyeCommented:
actually, my comment just duplicates one of Jim's comments (two comments above mine).
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:

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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

Patrick O'DeaAuthor Commented:
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??

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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 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.

Patrick O'DeaAuthor Commented:
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.

Patrick O'DeaAuthor Commented:
Thanks for all contributions ... very helpful
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.