By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,564 Members | 810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,564 IT Pros & Developers. It's quick & easy.

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

P: 16
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
Sep 2 '08 #1
Share this Question
Share on Google+
20 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Sep 2 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
Or you could upgrade to more intelligent users!

Linq ;0)>
Sep 2 '08 #3

NeoPa
Expert Mod 15k+
P: 31,441
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.
Sep 2 '08 #4

Expert Mod 2.5K+
P: 2,545
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
Attached Images
File Type: jpg screen1.jpg (43.9 KB, 252 views)
File Type: jpg screen2.jpg (41.0 KB, 220 views)
Sep 2 '08 #5

missinglinq
Expert 2.5K+
P: 3,532
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)>
Sep 2 '08 #6

P: 16
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
Sep 2 '08 #7

NeoPa
Expert Mod 15k+
P: 31,441
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).
Sep 2 '08 #8

FishVal
Expert 2.5K+
P: 2,653
...
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.
    Expand|Select|Wrap|Line Numbers
    1. Public Function CatchLockControlKeyboardEvent(blnLocked As Boolean) As Variant
    2.  
  • 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
Sep 3 '08 #9

NeoPa
Expert Mod 15k+
P: 31,441
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.
Sep 3 '08 #10

P: 11
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...
Expand|Select|Wrap|Line Numbers
  1.     Set ctlCurrentControl = Screen.ActiveControl
  2.       strControlName = ctlCurrentControl.Name
  3.       Select Case strControlName
  4.         Case Control1_Name
  5.           .... VBA for Control1
  6.         Case Control2
  7.           ... VBA for Control2
  8.         Case ...
  9.          .....
  10.         Case Control9_Name
  11.           ... VBA for Control 9  
  12.         Case Else
  13.           'All other controls on form that need no action
  14.  
  15.       End Select
Sep 8 '08 #11

FishVal
Expert 2.5K+
P: 2,653
Nice. Thanks for the tip.

Regards,
Fish
Sep 9 '08 #12

NeoPa
Expert Mod 15k+
P: 31,441
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.
Sep 9 '08 #13

P: 11
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.
Sep 9 '08 #14

NeoPa
Expert Mod 15k+
P: 31,441
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.
Sep 9 '08 #15

P: 11
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.
Sep 10 '08 #16

FishVal
Expert 2.5K+
P: 2,653
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. ;)
Sep 10 '08 #17

NeoPa
Expert Mod 15k+
P: 31,441
LOL Fish :D

Don't worry - It didn't happen ;)
Sep 10 '08 #18

NeoPa
Expert Mod 15k+
P: 31,441
You are correct. I stand corrected. My carelessness.
Hey, we all make mistakes.

Your contributions have certainly been worthwhile :)
Sep 10 '08 #19

FishVal
Expert 2.5K+
P: 2,653
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
Attached Files
File Type: zip FormControls.zip (22.4 KB, 115 views)
Sep 10 '08 #20

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. Public Sub PrepareToDeath()
  2.     'destroy collection and thus all its members and thus
  3.     'all references to the parent FormControls object
  4.     Set colFormControls = Nothing
  5. End Sub
  6.  
After invoking this method, object could be destroyed by setting it to nothing.

Expand|Select|Wrap|Line Numbers
  1. objFormControlsInstanceName.PrepareToDeath
  2. Set objFormControlsInstanceName = Nothing
  3.  
Best regards,
Fish
Nov 16 '08 #21

Post your reply

Sign in to post your reply or Sign up for a free account.