Hello Everyone,

I'm MAJORLY stuck on something that could possibly have a very
simple solution (which would be excellent, if it is possible).
The best way to describe this is by using an example.

I have a bound form in Access where the user has 10 textboxes that
can be updated. I also have a separate table that lists all of the
control/field names for the form as well as an indicator as to
show whether the field is mandatory.

The application I am working on will allow a "superuser" to set
this mandatory field indicator within the control/field table as
they please, so I do need to keep the indicators in the table.

As an example, the superuser indicates that the 1st, 2nd, and 10th
fields are mandatory. The regular user may go into the form, enter
values in the 1st and 2nd field and then close the form without
entering a value in the 10th field. I do have working code that
checks all mandatory fields and then presents the user with a
dialog to indicate that the 10th field is mandatory. As of current,
the code is being called from the Form_BeforeUpdate() sub. I can
even prevent the updating of the underlying row by using the
DoCmd.CancelEvent or Cancel = True option within the BeforeUpdate()
subroutine.

While both the error message and the canceling of the update are
successful, I want the form to stay open on the current record until
either a) The user enters the mandatory field or b) Undoes the
changes at the record/row level. As I understand, the
BeforeUpdate() will be invoked when the user attempts to update one
or more fields on either a NewRecord-type row or an already-existing
row. Also, I am aware that the auto-save processing for a bound form
will be invoked on a "dirty" row when the user either tries to exit
the form, navigates to another existing row, or tries to add a new
row.

I do know of a similar process that works at the field/control level
which utilizes a DoCmd.CancelEvent on a field's OnExit() event. This
will force the user to either enter a permitted value or to undo the
field contents by pressing the [Esc] key before that person is
allowed to exit the field.

There is an existing process that runs within MS-Jet where if a
user enters data into a bound form but does not include values for
the columns that comprise a primary key, it will give a message
like "You can't save the record at this time. Microsoft Access may
have encountered an error while trying to save a record. If you
close this object now, the data changes you made will be lost. Do
you want to close the database object anyway?". I need something
that may more or less emulate this type of reaction when a mandatory
field (in this case) has not been specified on a user form.

Does anyone know of a way to do this same type of thing, but on a
form/row level within a form using the constraints that I have
described above? Should I be putting this mandatory field check
code into another event?

Any help would be EXTREMELY helpful. Thank you for your time.

Warren

OK, in a table you are keeping the name for all of your fields. if not, you should. so

it Text1.text = "" then do your checking, which would be a lookup via ADO (find method of ADO) to the table (recordset) containing the field names. The recordset, should only pick up the field names that the "Superuser" (I call them Application Administrators) has checked as required.

you would keep in the table, the Actual field name, maybe the textbox name on the form and a checkbox for Required identifier.

For this type of scenario, your users will be rightly anoyes by never knowing which fields are required. Instead, this is what I have done. I have alowed users to save the entered record as a sort of "Template" or "Inactive" record, or call it something else you wish, but what this does is it keeps ther data in the database table, but does not actually show it as active until all required fields are filled in.


I did this for a real estate company that could never get all of the required information at once.


Anyway, either way should work.

If you do not let the user out of the text box, this will also become anoying, specially if they know the rest of the info, but not that particular field.

Sal

Sal,

Thank you for your help. Here's some more details of the situation -
I already have a "control" table that has 3 columns - form name,
control name, and mandatory usage indicator. Similarly as you had
mentioned about ADO, I do have a generic function that retrieves only
the mandatory fields using the form name as an argument to the
function. Using this method, it really is quite simple to read
through all entries in the table and set the background color of
mandatory textboxes, etc to some special color to indicate to the
user the type of usage.

The only thing that I am not quite clear on is your reference to the
idea of using a template or inactive record for the form - other than
a boolean or yes/no type of field within the record itself to
indicate the record as "active", that's basically all that comes to
mind. In that case, it seems that I would need to add this type
of indicator to any table being used in a bound data-entry form.

Honestly, I am trying to find a somewhat simple solution if possible
because I will need to apply this code to an application that contains
over 150 forms so far. Whatever I apply to one form will need to be
applied to all.

And don't worry - I was using the textbox "no-exit" as an example for
a comparison of the type of operation that I need to emulate at the
form or record-level, rather than a field level. After working with
online user-level applications for over 16 years on both mainframe
and PC, one sure hears enough of what will and will not go over with
user interfaces. ;)

Check the LostFocus event of the textbox. Use this to check for text1.text = ""
This would be better by using ADO for the record Add, Edits. If the user closes a bound form, the record will be saved anyway and it will be wrong.

Sal

I just found something interesting - by setting "Cancel = True" within the BeforeUpdate() upon checking a user error via code, Access will invoke the error message of "You can't save the record at this time ..." message which will force the user to either go back and correct any mistakes or to cancel the edit of the current record. Because this is in the BeforeUpdate event, it will work when either the user tries to close the form, navigate to another existing record, or attempt to add a new record.

The interesting point is in regards to when and how a user attempts to close the form. Apparantly, there is some type of difference in the actual underlying processes between the execution of "DoCmd.Close" from within the form, versus closing the form using the form's close button, using Ctrl-F4, or File|Close from the menu bar. If the "DoCmd.Close" button is used in this instance, Access does NOT seem to process the "Cancel = True" the same way as it does with the form's close button, Ctrl-F4, etc.

As a test, I have temporarily replaced the code behind a form's option button to close the form from "DoCmd.Close" to "SendKeys "^{F4}"" which will invoke the same process as File|Close etc, thus replicating the "You can't save the record at this time ..." message to appear to the user in the event of any errors found in the BeforeUpdate.

From what I have understood over the past couple of versions or so is that there has been a recommendation to switch from SendKeys to DoCmd.acRunCommand. I did try the two options of "DoCmd.acRunCommand acCmdClose" and "DoCmd.acRunCommand acCmdCloseWindow", but both gave me the same results as "DoCmd.Close" and "DoCmd.Close acForm, Me.Name" - both will cancel the update, but will not prompt the user with the "You can't save the record at this time ..." message.

So what it looks like so far, I may need to use "SendKeys "^{F4}"" to prompt the user with this error message when attempting to close a form while attempting to enable the user to perform necessary corrections if one or more user data-entry errors are trapped via code.

If anyone has an alternative to using the SendKeys with the {F4} (like something using acRunCommand or whatever), it would be most appreciated.

I hope this can be useful for anyone else who could use this info.

Thank You,

Warren
:D