How to change bound continuous form with required fields

[Julia B]

Hi all, I posted a couple of weeks ago and got an answer to this question
which I thought would solve my problem, but my users have just complicated
matters.

I've got a continuous form bound to a table with required fields. My users
want to skip out of the form half way through records and get data from other
sources (I originally thought it was data from other records in the form) to
copy & paste into the form. The form has 10 or so fields and users may want
to get data from other sources for perhaps 3 of the fields, however this will
vary depending on the record.

The problem is that if they try and move the focus to something else half
way through the record Access throws a wobbler and won't let them because the
required fields have not been completed.

I'm worried about changing the fields to not required because the
controlling the validity of the data becomes very difficult, unless anyone
has any brilliant ideas.

Thanks in advance for your help!
Julia

 

[Julia B]

All, I've managed to work a solution, although it's fiddly.

I've done the following:

1 - added an extra field to the underlying table called RecordComplete which
is a boolean with default value of False
2 - put put some validation on the form unload event which cancels the event
if the validation fails (i.e. check all underlying fields in the
recordsetclone to ensure necessary fields are complete)
3 - put some processing on the form close event which updates all the
records in the recordsetclone to change the value of the RecordComplete field
to True

In theory this means that all records are marked as not complete and can
only be marked as complete if the form close event happens successfully. I
can then use this RecordComplete value in other places in the application to
ensure that any records that have not been validated are not used.

Julia

 

[Klatuu]

One way would be to remove the requirement in the table definition and do the
checks in the Form's Before Update event.

 

[BruceM]

I remember this thread. I may have been the one who suggested using combo
boxes with row sources derived from existing data in the database, but I see
that users want to get some of the data from outside the database.

One option may be to use the Before Update event for notification only.
That is, don't cancel, and don't undo. Just let the users know there are
incomplete fields. If these fields are not completed, the record will not
be used. If the fields are completed, the Before Update event can silently
set RecordComplete to True. No need to wait for the Unload event. You
could include a message box that the record will be deleted at the end of
the day, or at the end of the session, or whatever makes sense. I have code
in a purchase order database that after one week deletes records that do not
include line items. It uses the form's Unload event. In your case it may
go something like this:

Dim db As DAO.Database
Dim strSQL As String

strSQL = "DELETE DISTINCTROW YourTable.* " & _
"FROM YourTable " & _
"WHERE YourTable.RecordComplete = False"

Set db = DBEngine(0)(0)
db.Execute strSQL, dbFailOnError
Set db = Nothing

Another (or concurrent, if you wish) possibility could take place if the
user is identified in the record. In that case you could use the form's
Unload event to cancel the event if there are records for that user in which
RecordComplete is False. You could build RecordSource SQL including just
the incomplete records for a particular user, then reset the RecordSource to
the new RecordSource SQL before cancelling the Unload event.

If users are annoyed by the message in the Before Update event before they
leave the database, perhaps they could get in the habit of assembling what
they need onto the clipboard before starting the record. I believe you can
go to the clipboard without triggering the Before Update event.