MS Access 2003 - Code to inform the user to click the edit button

[sam12]

I have an application where all the fields on the form are locked preventing editing of the information(Through a function lockUnklockfrm) until an edit button on the form is clicked or NEW RECORD EVENT occurs which then unlocks all the fields for editing. However, most users come in and move to a field and forget that all fields are locked and that they try to edit the data. Is there any event code that can be placed on a field when the user starts to edit the data a message tells them they must click the edit button first.I want to do it through functions because this database has 20 forms and hundreds of controls.Thanks in advance

[FishVal]

Hello.

You may handle KeyDown event, but it will require to write event handler for or put function call in OnKeyDown property of each form control being protected.

On the other hand you may put a well visible control indicating lock state of current record or change form caption appropriately or whatsoever.

Additionally if you want to lock entire record you may use Form.AllowEdits property instead of locking multiple controls. However this way you will not have any event to determine user edit activity.

Regards,
Fish

[missinglinq]

Or you could upgrade to more intelligent users!

Linq ;0)>

[NeoPa]

If the choice is to assume more intelligent users or program such that the application is more user-friendly - I think we all know the way to go Linq ;)

Having said that, of course, giving a visual clue (change the colour of locked controls or the background of the form) could mean you could avoid the necessity somewhat. I feel that would also be more user-friendly.

[Stewart Ross]

I use a prominent status indicator on one of my applications (an HR system where records are read-only until their status is changed) but it does not stop users from trying to change the records without changing the status first :(

I agree with Linq...

-Stewart

[missinglinq]

We all spend an inordinate amount of time trying to make apps "idiot-proof." But as a local printing company's poster used to say, "The problem with making something idiot-proof is that idiots are so @&%# ingenious!"

On the serious side, if a user can't figure out, after a day or two, that they have to click on a button to edit a record, do you really want this person to have the ability to change your important business data?

Linq ;0)>

[sam12]

Thanks all of you, you all are right
But some customers are so stupid
I was using lock/unlock button with color and caption chages.
they said they need edit button instead of lock/unlock button and now they want "A waring message should pop-up when you click on locked form"
I tried my best to resolve it but still working on it.
I used formname.allowedits method to lock forms
I want to capture the mouse click event on form(On all controls )
I tried to do it through form_click_event()
But when i click on any control compiler doesn't go in form_click event()
Thanks a lot again for ur views

[NeoPa]

Perhaps the Form_MouseDown() event procedure will help.

Search Help for the full info and come back if you have any specific questions on how to get it to work on your form.

MouseDown is better for this than OnClick as it triggers no matter where on the form the mouse is clicked. You will need to be careful to exclude the part of the form where the EDIT button is though of course (All controls have Top; Left; Height and Width properties).

[FishVal]

...
MouseDown is better for this than OnClick as it triggers no matter where on the form the mouse is clicked. You will need to be careful to exclude the part of the form where the EDIT button is though of course (All controls have Top; Left; Height and Width properties).
....

Form_MouseDown as well as Detail_MouseDown event is not fired when user clicks on form control. :( Not in Access 2003 at least.

It maybe not very elegant, but I think the following would work.

• Create a public function, something like the following.
  

  Public Function CatchLockControlKeyboardEvent(blnLocked As Boolean) As Variant
   

• On Form_Load event and anytime lock status changes iterate controls and write to OnKeyDown property
  =CatchLockControlKeyboardEvent(<..lock state>..)
• A better way is to pass reference to the control, but that may require more comprehensive logic in a case control is located in deep nested subform. Though nothing impossible of course. ;)

Regards,
Fish

[NeoPa]

You're absolutely right there Fish, although it would be nice if there were a practicable way of determining whether or not the mouse clicks anywhere in the form.

As the desire is to pop up a message before any damage is done, adding some code to capture clicks in ALL the bound controls of the form may be the most appropriate (if a little tiresome) approach.

[cm5th]

Hello.

You may handle KeyDown event, but it will require to write event handler for or put function call in OnKeyDown property of each form control being protected.

On the other hand you may put a well visible control indicating lock state of current record or change form caption appropriately or whatsoever.

Additionally if you want to lock entire record you may use Form.AllowEdits property instead of locking multiple controls. However this way you will not have any event to determine user edit activity.

Regards,
Fish

Have you considered using a Form-level event handler for KeyDown? I've used it before. I do this...

1) In Form_Load event, include
Form.KeyPreview = TRUE

2) In Form_KeyDown event handler, use code like...

    Set ctlCurrentControl = Screen.ActiveControl
      strControlName = ctlCurrentControl.Name
      Select Case strControlName
        Case Control1_Name
          .... VBA for Control1
        Case Control2
          ... VBA for Control2
        Case ...
         .....
        Case Control9_Name
          ... VBA for Control 9  
        Case Else
          'All other controls on form that need no action
 
      End Select

[FishVal]

Nice. Thanks for the tip.

Regards,
Fish

[NeoPa]

Thanks all of you, you all are right
But some customers are so stupid
... and now they want "A waring message should pop-up when you click on locked form"
...
Thanks a lot again for ur views

These (key-based) solutions are fine if you consider this post just an expression of the users' (wild) desires, and not critical. This may well be the case in reality, but explains why I was exploring mouse related event procedures, while you guys were concentrating on key-related ones.

[cm5th]

These (key-based) solutions are fine if you consider this post just an expression of the users' (wild) desires, and not critical. This may well be the case in reality, but explains why I was exploring mouse related event procedures, while you guys were concentrating on key-related ones.

Sorry I did not address the mouse-clicks earlier. The form KeyPreviews also enable events like Form_Click for mouse click, Form_MouseDown for MouseDown events. Identification of the ActiveControl identified the control the user clicked). I used the Form Key events more so I can consolidate keystoke filters.

[NeoPa]

I thought (from Fish's post #9) that those mouse events didn't fire when on one of the controls of the form. I can't pretend that I use these techniques much myself, but if they don't fire over the controls I expect we're back with my post #10 - using mouse events on all the form's controls.

I say this from simple logic but without full confidence as there may well be something I'm missing in all this. Any further light is always welcomed.

[cm5th]

I thought (from Fish's post #9) that those mouse events didn't fire when on one of the controls of the form.

You are correct. I stand corrected. My carelessness.

[FishVal]

Hello, gentlemen.

Actually it is not that difficult to write a simple class to "merge" mouse events of form components.

• Object variable of the class declared withevents in a form module and instantiated on Form_Load.
• When instantiated it adds form controls to internal collection within the instances of other custom class.
• Instances of that other class will listen to controls events and when such happen invoke code in parent class which will raise an event in form module.

An example could be found in attachment to Access VBA to handle excel ComboBox events thread.

Regards,
Fish

The code in the attachment is not exactly what I mean (the collection doesn't fire events), but it could be easily adapted. And I will do it if we will not turn into black hole in less than half an hour. ;)

[NeoPa]

LOL Fish :D

Don't worry - It didn't happen ;)

[NeoPa]

You are correct. I stand corrected. My carelessness.

Hey, we all make mistakes.

Your contributions have certainly been worthwhile :)

[FishVal]

LOL Fish :D

Don't worry - It didn't happen ;)

Well. The End was postponed again.
So, here is a sample of event merging form buildup.

• 3 controls to the left of the form has "native" and custom Click event handling mechanism
• all controls has custom Click and MouseMove event handling mechanism
• event handlers give feedback in 3 controls to the right

BTW, did anybody know that Click event on ComboBox is fired only when an item has been selected from dropdown list. Personally I think this is weird.

Regards,
Fish

[FishVal]

For those who download and use the attachment in previous post.

A serious bug was found.

Description.

FormControls class contains colFormControls collection. Each member of the collection after instantiation get reference to parent class (FormControls). This cause a situation when object of FormControls class could not be destroyed with
Set objFormControlsInstanceName = Nothing
Object (inaccessible after running command above) remains in memory which sometimes causes application crash.

Way to resolve.

Obviously members of colFormControls collection has to be destroyed before correspondent object of FormControls class could RIP.
The following method should be added to the class.

Public Sub PrepareToDeath()
    'destroy collection and thus all its members and thus
    'all references to the parent FormControls object
    Set colFormControls = Nothing
End Sub
 

After invoking this method, object could be destroyed by setting it to nothing.

objFormControlsInstanceName.PrepareToDeath
Set objFormControlsInstanceName = Nothing
 

Best regards,
Fish