Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Lost Focus or Deactivate On Form

Lost Focus or Deactivate On Form

Good afternoon. Beating my head against the wall. Here is the scenerio. I have a form which gets used for ordering product. When the user puts in quantity which is not divisable by the required order amount (different for each item), I pop up another form. On the form you have 3 choices, select a lower divisible number, select a higher divisible number or simply override (eg. Use the number they put in but at least they are cognisant of what they are doing.). Here is the problem. At first I had users, closing the form and it defaulted always to OVERRIDE. Easy fix, I took on the ON CLOSE button (the X top right hand corner). Now I have another user who decided they wanted to click on another box on the original order form without hitting 1 of the 3 options. Great. So now my box with the 3 options is in the backround. They are now working on another item and then all havoc breaks lose. I tried doing something with the lost focus on the form but that didn't do what I wanted. Basically, I do not want them to get out of that form without choicing one of the 3 options. Simple concept but I can't get it. I tried on event Deactivate but that gets hit it seems on everything. What am I missing? Thanks in advance. I attached an example of the forms. On the original form I entered 458 cases. My window popped up with 400 or 800 or override (eg. use the 458). Please let me know if you need any more information or I confused you.

Remember when... everything worked and there was a reason for it?

RE: Lost Focus or Deactivate On Form

I would think you want to open the form modal dialog. That why they cannot click out of it. There is a trick though to return a value from a modal dialog form. The trick is to remove the X from the form as you said. Then when they select a choice it sets the form to visible = false. In your case it appears the returned value could come from three places. So I would make a hidden textbox and push the value into that textbox, then hide the dialog pop. The below function would "pull" the value from the hidden textbox.

This is basically the wrapper function I use to get the value from a pop up form.

CODE -->

Public Function getValueFromPopUp(formName As String, PopUpControlName As String) As Variant
  'FormName: Name of the popup form
  'PopupControlName: Name of the control on the pop up/dialog that you want the value
  Dim frm As Access.Form
  DoCmd.OpenForm formName, , , , acFormEdit, acDialog
  'wait until form is closed or hidden
  'The popup needs an OK button that hides the popup(me.visible = false), and a Cancel button that just closes it
  If CurrentProject.AllForms(formName).IsLoaded Then
    Set frm = Forms(formName)
    getValueFromPopUp = frm.Controls(PopUpControlName).Value
    DoCmd.Close acForm, formName
  End If
End Function 

RE: Lost Focus or Deactivate On Form

Normally I have an "ok" and "cancel" button, but in your case I do not think cancel is an option they have to pick one of the three.

RE: Lost Focus or Deactivate On Form

Hey there. Going home. I will decipher what you are saying on Monday. A tad confused. You are saying to open my current form with the 3 buttons in formedit mode with acdialog. Never did that before. What is the difference between that and a normal open of a form? Those 3 buttons I have at the bottom update, the prior form with the number above them. I am a little unclear as to how I continue to do that. Are you telling me to scrap that form totally? By the way, before the form a message box used to be used but the problem there was, it was just a reminder and the user would then close the message box and type in the number. It was a waste of time. This is much cleaner, with my one caviot which I have to fix. If there is any other light you can shed on this, that would be great. I am little confused when you say modal dialog. I guess once I do it, it will make more sense. I will cut and paste your code next week. Can you highlight in RED which fields I have to replace versus the standard VBA code? Thank you very much.

Remember when... everything worked and there was a reason for it?

RE: Lost Focus or Deactivate On Form

If you open the pop up form as modal mode you cannot click out of the form until you close it. You do that by using the acdialog argument of the form

DoCmd.OpenForm formName, , , , acFormEdit, acDialog

Here is a very good explanation



it's simple. it stays on top of other MS Access windows. that's about all.

here are some things you CAN do when a popup form is open:
*1) Manipulate portions of other MS Access windows that are visible. including the ribbon menus, any custom menus, and navigation pane / database window.
*2) Use VBA code to manipulate any other open object

Items with a lower Z-Index than a popup form
*1) Any windows that appear via the buttons and options that are on the HOME and CREATE menus in 2007 (and equivalents in earlier versions).
*2) Non-popup objects
*3) Other popup windows opened previously to the current popup form
*4) A Modal window that has been opened.
*5) The navigation pane / database window

Items with a higher Z-Index than a popup form
*1) 2007 Ribbon Menus / Menu Bars
*2) Any windows from menus other than the ones listed in item #1 above.
*3) Other popup windows subsequently opened.

Popups are useful sometimes. Here are some good uses of them:
*1) Stop the user from clicking around at will when they're supposed to be focusing on the current form.
*2) Get the attention of the user.
*3) Indicate to a user that there is something to do with the form on screen before anything else is done.


this is not so simple, but still easy to explain. a modal form simply freezes everything else on screen except the form itself. in other words, everything on screen other than the modal form and it's children items are inaccessible (this includes the navigation pane / database window). popup objects that still appear on top of modal forms on the screen are also inaccessible, even through they're "on top".

here are some things you CAN do when a modal form is open:
*1) Manipulate the modal form
*2) Access menu bars and ribbons
*3) Use VBA code to manipulate any other open object

Items with a lower Z-Index than a modal form
*1) Non-popup objects
*2) Navigation pane / database window

Items with a higher Z-Index than a modal form
*1) 2007 Ribbon Menus / Menu Bars
*2) Any windows that appear via any menu bar option or ribbon menu item.
*3) ANY popup object, regardless of when it was opened

Modal forms are rarely useful (without being coupled with the "popup property). But, they do serve some
*1) Stops the user from clicking anywhere outside the modal form and having something happen on accident.
*2) when they're maximized. this plainly tells the user that something should be done with it.


in my opinion, this is by far the best option if you're gonna mess around with either of these properties at all. and here are some good reasons why:

*1) objects like this ALWAYS have the highest z-Index of all objects that are open.
*2) besides being on top, they also freeze everything else on screen, regardless of properties that are set on other objects.
*3) they are by far the best way to tell the user what to do when you have a lot of "submenus", and forms on top of forms that need to be opened to perform tasks that are buried deep inside the program.
*4) they get more attention (in my experience) if coupled with the "autoresize" and "autocenter" properties.

If you call the form to open using acdialog code execution stops in the calling form until the popup form is closed or hidden. So you it is difficult for the form that calls it to "grab" a value from the popup. You can do what you are doing and have the pop-up "push" the value back to a control on the calling form. That design is less flexible because you have hard-coded the popup and it cannot be easily reused. In other words another form in the database could not call it. Also you cannot pass the value back to the code that called it because that code is stopped, but you could pass it back to a control on the form.

If you are pushing the value from the popup to the calling form you can simply use "acdialog" argument and it will work. The form will popup and you will be forced to click a button. It will always have focus until closed. You do not have to use the code I provided.

However, imagine I want to call a popup from many different forms. Lets say it is a Custom calendar. You simply want to get a date and then do something with it. On the calendar there is only code to store the selected value on the popupform, and buttons for OK and cancel. Cancel closes the popup and OK makes is invisible. I can then get a value from the calendar by doing something like this is the calling form

Private sub SomeProcedureOnCallingForm()
dim SelectDate as date
'some code here
SelectDate = getValueFromPopUp("frmCalendar", "txtBoxSelectedDate)
'You call the function and when the function calls the popup form the code stops until the popup is closed or hidden
'If it was hidden the value is returned above and code will continue
'now you could do something with the returned date
Msgbox SelectDate
End sub

RE: Lost Focus or Deactivate On Form

I always like to K.I.S.S.

Why not a default value? That, is the usual (best practice approach) anyway.

I'd plump for the higher divisible.

The user STILL get's the message - has been warned - and a small label in red can state: "This will be overridden unless you choose otherwise."


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

RE: Lost Focus or Deactivate On Form

MajP has given you the right way to do this, but FYI, vis-à-vis the Form's LostFocus and Deactivate should know that a Form can only have Focus if there are no Controls on it that can receive Focus...and if it can't have can't lose Focus! So unless you're talking about a Form with no Controls that can receive Focus, you're pretty much stuck with the Deactivate event to pinpoint when you're moving off of a Form. Also keep in mind that Deactivate will also fire when the Form is closed.

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

RE: Lost Focus or Deactivate On Form

Hey everyone,
I sort of got lost in what you were trying to tell me. What I did was open the form in design view, set the popup and modal attributes to YES. I then opened the form as normal and it works perfectly. Is it that easy?

Remember when... everything worked and there was a reason for it?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close