How Does Access Update Fields Within Rows?

[Guest]

I have a subform showing the time and date worked for a volunteer or other
worker, within a form showing individual volunteers. Sometimes blank rows are
added to the activity table or incomplete rows are added. As an ex-mainframer
I expect all editing to occur when the enter key is pressed, allowing me to
make cross-field edits, range edits, appropriateness edits, etc. when the
"page" is updated. This does not seem to occur with Access, causing
essentially empty rows to be written to the activity table and some incorrect
rows to be written to the activity table. I have tried various event
procedures for the individual fields and for the sub-form without success.

I want a way to edit each of the fields in the row for completeness and
appropriateness prior to ANY field being written to the table, because some
edits are cross-field depending on to field. What "event procedure" is used
to accomplish this? I have tried "beforeupdate" on individual fields,
"afterupdate" on individual fields, etc. and some "event procedures" for the
form without success.

Any suggestions? I am capable of writing complex event procedures in VBA if
need be.

Earl Phillips
Ex-mainframer volunteering in Access
At Local Food Bank

 

[Allen Browne]

Use the BeforeUpdate event of the *form* for record-level validation checks.

 

[Guest]

This works for the editing. If there is an error which requires that I stay
on the current record to correct errors, what VBA code do I use in the
"beforeupdate" "event procedure" to keep the form from progressing to the
next record, e.g. telling it to ignor the "record selectors" choice?

Earl Phillips
Frustrated Ex-mainframer trying to bend Access to my will

 

[John Spencer]

Set Cancel = True in your procedure. That Cancels the update.

 

[Rick Brandt]

This works for the editing. If there is an error which requires that
I stay on the current record to correct errors, what VBA code do I
use in the "beforeupdate" "event procedure" to keep the form from
progressing to the next record, e.g. telling it to ignor the "record
selectors" choice?

If your validation in BeforeUpdate fails you set Cancel = True. That
cancels the update and whatever they did to trigger the update will also be
cancelled. They will not be able to leave the record until they fix it.

 

[Guest]

Quite apparently I do not know how to code "Cancel = True". I code what is
within the quote marks in the VBA code if the conditions require that I stay
on the current volunteer. The error message displays, but the "record
selector" process still transfers me to the next row (next volunteer) and
sets the focus on the same field on the subform where the edits failed on the
prior volunteer. Obviously, I am missing something. What is it?

Earl Phillips
Frustrated Ex-mainframer

 

[Guest]

The form does not transfer to the next volunteer until the message box OK is
clicked, if this is an issue.

Earl Phillips
Just frustrated with Access

 

[Albert D. Kallal]

Your code in the before update event would look like:


if isnull(me.LastName) = true then

msgbox "please enter the last name"

cancel = True

end if


the above would display a message box if the last name control on the form
is empty

Further, we could even enhance the form's ui by placing the cursor back into
the last name field. The code would thus become



if isnull(me.LastName) = true then

msgbox "please enter the last name"

cancel = True
me.LastName.SetFocus

end if

Remember this is forms before update event...not for a individual control.
This means that while the form is in design mode, you
have to select the FORM, and THEN you can view/use the "events" tab in the
properties sheet...

so, while in design mode, you go edit->Select form

and, then view the properties sheet (if it is not displayed, then go
view->Properties).

You then click on the events tab...and can then select the before update
event..and click on the [...] to enter the code for this event...

What is a "new" concept here is what we call event driven programming, and
the result of this is in place of one big main type code routine...you wind
up with zillions of little tiny stand alone code snippets....

 

[Guest]

Once the user has started this process, if they choose to abort it before
actually creating the row, how can they break out of this cycle of enforcing
the edits. I have tried a variety of things, but none seem to allow me to
abort the row-creation after it has been started, as in the case where they
unintentionally or accidentally started it.

Earl Phillips
Ex-mainframer volunteering in Access
At the Local Food Bank

 

[Albert D. Kallal]

I have tried a variety of things, but none seem to allow me to

abort the row-creation after it has been started, as in the case where
they
unintentionally or accidentally started it.

The above is different problem, and different question then our simply
validating routine.

However...the user can use edit->undo (might have to do this 2 times), then
any changes to the record will be un-done..and thus they can exit.
(and the before update event will NOT fire since they not edited the
record).

the user can also hit esc key, and that will also bail out. The key issue
here is if a field is blank, and you test for it, then it is blank and WE DO
NOT want the user to continue. So, keep in mind the two issues here!!

The record is ONLY created if the user starts typing into the record...so,
if they do NOTHING, then record is not created.

The INSTANT the user starts to type, the record is created..but will still
be empty until you allow the update, or undo any changes (edit->undo...or
hit esc key), which will then discard the record if it is new, or simply
discard changes if it was a existing record...

 

[Guest]

This has solved everything I needed to know for this round. I gratefully
thank all who contributed. I learned quite a bit about Access in the process.
We can now prevent GIGO with the data and Harvesters Food Bank can now
operate more efficiently. Thank you, one and all.

Earl Phillips
A Less Frustrated Ex-Mainframer Learning Access