Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Did I really want to update that field !!

Posted on 2011-10-17
Medium Priority
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
  • 5
  • 4
  • 2
  • +2
LVL 58
ID: 36979225

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

LVL 58
ID: 36979238

  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 120

Accepted Solution

Rey Obrero (Capricorn1) earned 400 total points
ID: 36979263

<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.
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 400 total points
ID: 36979278
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 120

Expert Comment

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

Author Comment

by:Patrick O'Dea
ID: 36979291
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 49

Expert Comment

by:Dale Fye
ID: 36979337
actually, my comment just duplicates one of Jim's comments (two comments above mine).

Assisted Solution

sah18 earned 400 total points
ID: 36979493
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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 800 total points
ID: 36979606
<<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
ID: 36979685
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??


Expert Comment

ID: 36980538
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 58
ID: 36981324

 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
ID: 36982099
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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 800 total points
ID: 36986061
<<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
ID: 37003280
Thanks for all contributions ... very helpful

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Implementing simple internal controls in the Microsoft Access application.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

577 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