Validation strategy

[BruceM]

I have a Purchase Order database that generally seems to be working as
intended, but I have run into a wall on an aspect of data validation. The
PO record is in the main table, with the LineItems in a related table. The
user interface is a main form with a subform. It's a standard set-up for
such things.

I would like to assure that users select at least one line item. If they do
not, the PO record is undone in code. The problem is that clicking into (or
otherwise entering) the line items subform causes the main form's Before
Update code to run. There are no line items yet, so the validation fails,
and a message tells the user they must enter line items when they were in
fact about to do that very thing.

My question is whether anybody can think of a way to enforce the requirement
that there be at least one line item, but still allow the user to enter the
subform (and thereby save the main form record) in order to add a line item
record.

If there was such a thing as a Record Change event that would do the trick.
The validation code would not run until navigating to a new main form
record. Clicking into the subform would not cause the code to run.

I have been experimenting with using a Boolean. If it is false the
validation code does not run; if it is true the code runs. However, even
assuming this is a reasonable approach I have not figured out when to toggle
the value.

 

[Allen Browne]

This is an old chestnut, Bruce. You may be able to search for previous
answers at groups.google.com.

Essentially it's a chicken'n'egg problem. You cannot create the related
records until the primary record exists, and hence you cannot require
related records as a condition for creating the primary record.

Various workarounds have been suggested. One approach is to use the
AfterInsert event procedure of the main form to insert something into the
subform (e.g. an line ordering zero peanuts.) Another is to write the
records to a temp table, and append them to the real table only when you are
sure line items exist.

In the end, an INNER JOIN is going to ignore any main records that have no
details anyway, so it's not really going to make much difference. Perhaps
it's a case where you as a developer are taking on responsibility for
something you should just leave to the user.

 

[AndrewOfAzotus]

I have a Purchase Order database that generally seems to be working as
intended, but I have run into a wall on an aspect of data validation. The
PO record is in the main table, with the LineItems in a related table. The
user interface is a main form with a subform. It's a standard set-up for
such things.

I would like to assure that users select at least one line item. If they do
not, the PO record is undone in code. The problem is that clicking into (or
otherwise entering) the line items subform causes the main form's Before
Update code to run. There are no line items yet, so the validation fails,
and a message tells the user they must enter line items when they were in
fact about to do that very thing.

My question is whether anybody can think of a way to enforce the requirement
that there be at least one line item, but still allow the user to enter the
subform (and thereby save the main form record) in order to add a line item
record.

If there was such a thing as a Record Change event that would do the trick.
The validation code would not run until navigating to a new main form
record. Clicking into the subform would not cause the code to run.

I have been experimenting with using a Boolean. If it is false the
validation code does not run; if it is true the code runs. However, even
assuming this is a reasonable approach I have not figured out when to toggle
the value.

On occasion when I have had to do something similar, if check to see if the
'autonumber' or 'identity' column has a value. I tend to use Idx so:

if nz(me![Idx],0) > 0 then
<Perform Validation>
endif

 

[BruceM]

Thanks for the replies. Allen, I tried searching, but I must not have come
upon the right search string. You are correct that it won't really make a
difference, except that it could become confusing if there are a number of
Purchase Order records with no line items for the same company. Since this
database will represent a new way of doing things it will meet with
resistance from some, so I am trying to give those people as few
opportunites as possible to "prove" the system is flawed.
Perhaps I can check for an existing PO record with no line items for a
particular company, and send the user to that record (if there is one) when
they set out to create a new PO. That way I will get around the validation
difficulties, but at the same time keep the amount of garbage to a minimum.
I would need to do some studying about temp tables before trying that
approach, but it strikes me that appending the records to the real table
when I am sure there are line items is going to present validation
difficulties similar to what I am experiencing now.
Regarding inserting a subform record, I think I would rather avoid "dummy"
records unless there is no other option.
Or maybe I'll just skip the validation and see if it turns into a problem.
Maybe I am anticipating difficulties that are unlikely to occur.

 

[Allen Browne]

Excellent, Bruce. It helps to think out loud, and it's always worth
considering the various scenarios. I agree with most of your conclusions
too.

If you think it is worth chasing up, it's easy enough to use the Unmatched
Query Wiz (first dialog when you create a new query) to get any PO that has
no line items. If you wish, you could then give that to a user as report for
her to fix, or create a DELETE query to get rid of the itemless POs.

 

[BruceM]

Thanks for the reply and the encouragement. I have contiinued to experiment
with this. One option is a scorched earth approach to delete records that
have no child records. In the form's Unload event:

Dim db As DAO.Database
Dim strSQL As String

strSQL = "DELETE DISTINCTROW tblPO.* FROM tblPO " & _
"LEFT JOIN tblPO_Items " & _
"ON tblPO.ReqID = tblPO_Items.PO_ReqID " & _
"WHERE tblPO_Items.PO_ReqID Is Null"

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

ReqID is a number that is incremented for new records using DMax + 1. It is
the PK. I am using an incremented number because it is exposed to the user
as the Requisition Number. After the Purchase Order is approved a PO number
will be assigned automatically. tblPO is the main form's record source;
tblPO_Items is the subform's record source.

This has the intended effect of deleting records that have no child records
(Purchase Order line items). Another option is to have this as the Click
event for an administrative "Clean Up" command button. Or maybe I can use
the code in the Unload event, but look only for records that were created
more than a week ago. Whatever the details, I think the idea could work.

However, it could be rather mysterious to the users since the deletion
happens without warning. I would like the ability to advise the user that
records are going to be deleted, and to specify the number of records. I
have found that DBEngine(0)(0) has a RecordsAffected property, but it seems
to lag behind the current count. How can I identify the current count?

My thinking is that I could do one of several things if there are records
affected by the DELETE SQL above. For instance, I could use a SELECT
statement to return childless records, and load a form with that SELECT as
the record source so the user can handle the childless records one at a
time. Whatever exactly the plan, I think I will need to identify the number
of childless records. I may have other questions once I figure out how to
get the count, but for now I keep tripping over that obstacle.