On Entering form control seem to be in a text select mode

[Bigdoggit]

I have a form with unbound controls. The controls are updated through the Execute method. I am using an SQL statement that updates a specific field in a table. I recently added the BlockInput API call so that my users, who like to do a bunch of updates at once, don't whip through the fields and get ahead of the SQL update thus receiving an error message that the data has been updated since their edits since in reality it is their update causing the error.

The BlockInput works great but unfortunately if input is blocked before the Execute statement then when input is unblocked the control the user has moved to is in a select mode. The hourglass icon appears and when the mouse is moved text is highlighted. The only way to get out of this is to click on the mouse again. Not even pressing ESC on the keyboard helps. If the BlockInput call is right after the execute statement all is fine so I am sure it is this command causing the glitch. I can't figure out how to avoid the problem. Suggestions are appreciated. Is there a way to exit from selecting text, programatically I mean, or is there a better solution?

[RuralGuy]

How are you doing the BlockInput?

[Bigdoggit]

When the user exits a control the LostFocus event is triggered. From this event I call the AfterUpdate event for the control. This event calls the BeforeUpdate for the form, then calls a separate module to run the SQL update, then ends the input block. At this point the Gotfocus event for the control being moved to is fired. Does that answer the question well enough. Please let me know if you would like more info.

[RuralGuy]

I have to really question your man-handling of the events of your form. They all occur at a specific time for a specific reason. If there is some specific code in each of these events you need then I would separate them out in their own private procedures and call them wherever needed. Invoking an AfterUpdate event of a control from the Exit event of the same control is really not very good form.

[Bigdoggit]

**Interesting note to make on this - when I have the front end and back end on my desktop the strange selection behavior does not present itself. However, on the network it does.**

I understand your feeling on that. I originally was not but wanted to trigger events similar to those found in bound controls. I have already tested the process using only the AfterUpdate event of the control. In this test I only call my UpdateData procedure which is in a separate module - no events on the form. I still get the same behavior. The next control is still in some sort of select mode where moving the mouse is selecting text and I have to click a second time to exit this mode. As long as the BlockInput call comes after my Execute command there is not problem. Somehow a mouseup event is being lost here - that is my guess at this point anyway.

I am no longer going to trigger the AfterUpdate event from a LostFocus event for the same control. I am curious though - if I call events, even for the same control, am I not just calling code? For instance, if I call the change event becuase it has code that determines the control's background color and another control's background color is that bad form or better than having yet another procedure somewhere dealing with the specific scenario which is called by not only the control's change event but say the lostfocus event as well? I imagine the answer is not black and white and if that is the case that answers my question.

Still open to ideas since the wierd selection behavior is prohibiting use of BlockInput - which is really nice to invoke here. Side question though - is there anyway to invoke a block on only the active window or active application? I know there is an EnableWindow API but that still lets input through. It would be even better to block input to just the active window in Access or just the active Access database. Maybe I can or maybe I can't but I figured I should ask.

[RuralGuy]

It is important to remember that the Event code is really defined by Access and could involve undocumented action deemed appropriate by the development team at Microsoft. I do not know this to actually be the case but I've always factored out code I required into its own procedure if needed. FWIW, there has only been one situation I've ever seen where an unbound form made sense. All other situations were *much* easier to handle using a bound form.

[Bigdoggit]

I am constantly getting record locks - the error message "Could not update; currently locked" - the 3218 error message. I have a separate front end and back end, each computer has their own front end, I have memo fields in their own tables, and use a query as the record source for my form. The query does include memo fields. The whole goal is to get rid of record locking. I have this issue posted in another thread but no one responded to that one. I have to catch up on an issue real quick and then look back to this post so let me know what you think I will add more about the other post if you like.

[RuralGuy]

Do you believe the issue with your locking problems is caused by multiple users in the system at the same time?

[Bigdoggit]

I do believe that. We do not have the issue until more than 2 users are in the database and the issues gets out of control once we start hitting around 12. We have stopped using one field that is updated regularly and the problem has largely gone away. However, that is not a fix, just a stop gap measure. Unfortunately, no matter how hard I try I cannot reproduce the problem in testing.

[RuralGuy]

Is the field you have "stopped using" a memo field?

[Bigdoggit]

No, it is a text field that is accessed via a combo box

[RuralGuy]

There should be no conflict until you attempt to save the "changed" record. Did you have some special code in the AfterUpdate event of the ComboBox?

[Bigdoggit]

The problem arises when the user goes to save their change. However, once a user starts to edit any control on the form the pencil icon appears and I suspect Access is doing some kind of locking even though I say not to. I suspect it is doing this because the form is based on a query that includes memo fields. Unfortunately, I cannot reproduce this scenario in testing and with 20 people in the database I cannot have everyone stop what they are doing and walk around to each computer to try and find the issue. By using unbound form controls the only time anything is even being affected is when I run my Execute command with my SQL statement.

I am very open to any thoughts or suggestions on this topic of course.

Going back just a bit - any thoughts on a fix for the block input issue I am seeing?

[RuralGuy]

I believe the pencil icon indicated the record is now Dirty but not that Access will really care until you try and save the record. As for the BlockInput idea: is that some VB API code you have used? I'm not familiar with it as an intrinsic function of VBA.

[Bigdoggit]

It is an API call. The Declaration is:

Public Declare Function BlockInput Lib "user32" (ByVal fBlock As Long) As Long


It was provided by someone else on this forum. I myself need to study this subject a bit more.


Your comment about the Dirty situation makes sense. The other thought that crossed my mind was when a user actually makes a change that since the form was bound to a query which had memo fields the change was locking a lot of records briefly. Add to this a network which definitely drops connections from time to time and I feared the save operation used by Access was partially to blame. My SQL saves affect only one field at a time so I expect this to drastically cut down on traffic and the potential for people to step on each other. What do you think?