Form/Subform

[Guest]

We have a continuous subform on which the user selects one or more
manufacturers who are specified on a certain project, in preparation for
printing an RFQ report to the local representatives. The subform is linked
to the main form by the projectnumber, which is selected by a combo box on
the main form.

I recently discovered that if the user enters detail records without first
entering the project number, and then returns to the main form and enters the
project number, the report returns no data because the detail records still
have a blank project number.

Requerying the subform in the project number's AfterUpdate event initiates a
new record with a blank subform. The original detail records remain in the
table with a blank project number.

I know I can force the user to enter the project number first, but I'd
rather just let them enter it before or after the detail records. Can anyone
tell me how to permit this?

Thank you.
Sprinks

 

[Allen Browne]

It makes no sense the enter the related record first, when there is no
record for it to be related to.

If you want to accept the related record with a null primary key into your
table, and then hope that the user might get around to entering the primary
record before they do something else (such as closing the form, creating
another record, closing the database, opening another form, ...), you have a
maintenance nightmare on your hands, and you have already allowed
inconsistent data into your tables.

Better to cancel the BeforeInsert event of the subform if the main form is
at a new record. Better yet to mark the foreign key field in the subform's
table as a Required field.

 

[Guest]

Allen,

I appreciate all you give to the Access community, and understand what
you're saying. In this case, however, the information is not mission
critical. The only purpose of the tables is to facilitate emailing RFQs to
potential vendors & printing a followup report. We don't use the information
after that, and I routinely empty the tables. The primary key of the subform
table is an independent AutoNumber field, so I don't see any maintenance
issues.

The issue is that users don't think in terms of "related records" and "what
they're related to". They just understand that they need to enter a project
number and each manufacturer. If they've made the effort of entering 20 or
so detail records before entering a project number, I'm just trying to save
them the effort of re-entering them or hitting them over the head first with
a "You must enter a project number first" message unless there's no other
way. I'd much rather accommodate users than force them to accommodate the
program.

Do you have any ideas on how this might be accomplished?

Thank you.
Sprinks

 

[Allen Browne]

You probably realize that you can populate the foreign key field with a
value if they ultimately get around to entering one somewhere.

But which records need updating with this value? Are the nulls left over
from a previous time? Or can you just:
dbEngine(0)(0).Execute "UPDATE Table1 SET MyForeignKey = 99 WHERE
MyForeignKey Is Null;", dbFailOnError

 

[Guest]

Allen,

I see; I don't have a way of specifying "those records down there in the
subform".

Since the form opens with the focus in an unbound lookup combo box (which
they use), the problem can be entirely avoided by setting the parent value in
the cb's AfterUpdate event, then moving the focus to the subform.

Thank you for your help.
Sprinks

 

[Allen Browne]

Guess you could loop through the records in the RecordsetClone of the
subform.

If the fk field IsNull(), Edit, set the value, and Update.