Forcing a form to stay open until all criteria is met

[Sara Carbone]

Hi! I am trying to use a macro, set at an event of a form (like Before Update or At Unload), that will verify that certain "requiered" fields are not left blank - the macro would not allow the user to leave the current record, leave the new record unfinished or close out of the form until all the required ones are filled. The macro I have - set at Before Update - uses multiple conditions that check each field (six in total) and force the user back to the blank fields. However, the user is able to get out of the form with an incomplete record (missing required fields) by closing it. I have tried disabling the close button or CancelEvent, but I can't seem to get it to work correctly (I can't create mulitple conditions for a macro, before it runs an action!). I am trying to avoid VB cause I am not very strong in it, though I have tried a bit with this problem.. Any thoughts out there? Thanks!

[jhemphill]

If you are trying to avoid using "VB" then what type of form are we talking about? I will try to help.

[Sara Carbone]

Hello! Thank you for responding so quickly to my post! Sorry in advance if I am repititious with my info. The form I have created is just a simple list of records with about 15 different fields that the user can edit at will. 6 of those fields are requiered - cannot be null. It has record selectors and navigations buttons - they can press the * to create a new record (autonumbered). There is a close button I created - the only option out of the form. The macro occuring at Before Update lists each condition (ie - [certain field] Is Null) and Actions are MsgBox, Cancel Event, GoToControl. So it won't let the user switch to a different record, it kees forcing them to the blank fields. Does this answer your question?

[jhemphill]

Not exactly. :-) I meant what did you use to create the form if it is not done in Visual Basic?

[Sara Carbone]

Oh! Sorry, Access '97.

[jhemphill]

Ok, Here is what you can do to make the 'X' button on the menu bar go away.

Place the following lines in the (General) - (Declarations) section of the form you are trying to lock down.


' This API will return the handle of the system menu for the form it is called in.
private Declare Function GetSystemMenu Lib "user32" (byval hwnd as Long, byval bRevert as Long) as Long

' This API will remove menu items. See function for more details.
private Declare Function RemoveMenu Lib "user32" (byval hMenu as Long, byval nPosition as Long, byval wFlags as Long) as Long

private Const MF_BYPOSITION = &H400&





After that is done, place the following sub into the same form. It has to be done this way I believe because Access won't allow 'Public' Declarations.


private Sub RemoveCancelMenuItem(frm as Form)
Dim hSysMenu as Long

' get the system menu for this form
hSysMenu = GetSystemMenu(frm.hwnd, 0)

' Please, remember when you try to remove any of the form menu items, to take
' them off in reverse order. Otherwise, the numbering will get confused.
' the numbers are from 0 - 8 top to bottom.

' Removes the close menu item.
Call RemoveMenu(hSysMenu, 6, MF_BYPOSITION)

' Removes the separator that is over the close menu item
Call RemoveMenu(hSysMenu, 5, MF_BYPOSITION)

End Sub




When that is done, all that is left is to call the function in the Form_Load event. Like so,

Call RemoveCancelMenuItem(me)




That should take care of your exiting problems. Then you can use your data checking macro in your Close button to be sure all the required data has been entered.
I hope this helps! :-)