Checking correctness of data in forms and subforms with VBA

[ghotion]

I'm using Access 2000 on XP

I have a main form (Form A) and a subform (Subform B). The main form
is associated with Table A, the subform is associated with Table B (a
set of dates). For example, Table A contains the fields: AID, ANAME,
AINFO. Table B contains BID, AID, BDATE, linking to table A via AID.

Normally, to make a change I search for a particular ANAME, then
update AINFO, and add a new record in Table B (via Subform B) with
BDATE in the future. I then search for another ANAME, etc. The problem
is, I sometimes forget to add the new record in table B before going
to the new record.

I wrote some Public VBA code in Subform B that would look to be sure
that its recordset has a future date, and that works OK. Initially, I
put some code into Form A at the "Before Update" event that would call
that routine. It works, but the problem is that I get my error
immediately after changing Table A and moving my focus to subform B,
before I even get a chance to add the record via the subform.

So, what I would like to do is to perform my check only if I attempt
to get a new record (via search or any other way) for table A. I.e.,
when I think I am done with all my updates for those associated
records - not when moving in and out of subforms. Is there an event
that I can use? Any suggestions?

Tom

 

[tina]

if you decide, on each instance of "FormA" data entry, what date to enter in
SubformB, suggest you just add an unbound textbox control to FormA and enter
the date there. you can add code to FormA's AfterUpdate event procedure, to
automatically add the date the SubformB; you can further add code to the
form's BeforeUpdate event to require that a date be entered in the unbound
control, before the record is updated.

on the other hand, if the date is a "standard" date for each instance of
data entry (for example, "today's date + 7 days"), suggest you just run
code in FormA's AfterUpdate event to add the calculated date to the subform,
without entering it manually anywhere. and unless you need to *see* the list
of dates for each FormA record, you don't even need the subform, in this
solution.

hth

 

[ghotion]

if you decide, on each instance of "FormA" data entry, what date to enter in
SubformB, suggest you just add an unbound textbox control to FormA and enter
the date there. you can add code to FormA's AfterUpdate event procedure, to
automatically add the date the SubformB; you can further add code to the
form's BeforeUpdate event to require that a date be entered in the unbound
control, before the record is updated.

on the other hand, if the date is a "standard" date for each instance of
data entry (for example, "today's date + 7 days"), suggest you just run
code in FormA's AfterUpdate event to add the calculated date to the subform,
without entering it manually anywhere. and unless you need to *see* the list
of dates for each FormA record, you don't even need the subform, in this
solution.

hth











- Show quoted text -

That's an interesting approach; I'll think some more about it. But can
you think of any other way? Is there some event that takes place when
I'm changing records in Table A? E.g., something equivalent to "Before
Changing Row/Record"?

If so, I could then check everything just prior to changing to another
"main" record - which would be perfect.

 

[John W. Vinson]

So, what I would like to do is to perform my check only if I attempt
to get a new record (via search or any other way) for table A. I.e.,
when I think I am done with all my updates for those associated
records - not when moving in and out of subforms. Is there an event
that I can use? Any suggestions?

The nub of the problem is that the table is updated more often than
you want. TableA is updated, the record is saved, the instant you type
anything on the subform; TableB is updated every time you move to
another record, or set focus back to any control on the mainform. Once
the record is written to disk, it's considerably more work to "undo"
or check the validity of what's written.

In particular, if the entry isn't "complete" until some fixed number
of records have been added to TableB, you may need to go to a fair bit
of effort! You can't check in the mainform's BeforeUpdate event
because that fires before you have entered *anything* into TableB; you
can't check in TableB's BeforeUpdate either, because that fires with
every record.

If this is really critical, you may need to base the Form on two
"scratchpad" tables which exist only for adding new records, and have
a command button or some other event to run Append queries to copy the
valid data to the "real" tables.

John W. Vinson [MVP]

 

[tina]

the only event that is tied to simply moving from one record to another, is
the Current event - but that occurrs for the record you move *into*, not the
record you move *out of*.

there are two form events connected with moving out of a record that has
been added or edited, BeforeUpdate and AfterUpdate. and there is the Dirty
property, which can be checked at any time to see if the *current* record
has been edited but not yet written to disk (including new records).

i can't think of a solution using any of these other than the ones i've
proposed. perhaps John's suggestions will give you what you need.

hth

 

[ghotion]

the only event that is tied to simply moving from one record to another, is
the Current event - but that occurrs for the record you move *into*, not the
record you move *out of*.

there are two form events connected with moving out of a record that has
been added or edited, BeforeUpdate and AfterUpdate. and there is the Dirty
property, which can be checked at any time to see if the *current* record
has been edited but not yet written to disk (including new records).

i can't think of a solution using any of these other than the ones i've
proposed. perhaps John's suggestions will give you what you need.

hth








- Show quoted text -

Thank you both. This is good information. I was wondering if I had
missed an obvious solution, and I feel better that it's not simple!
But with your help, I have a few alternatives.

Tom

 

[tina]

you're welcome

Thank you both. This is good information. I was wondering if I had
missed an obvious solution, and I feel better that it's not simple!
But with your help, I have a few alternatives.

Tom