Re: Form/Subform

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sprinks" <Sprinks@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5FE3EA16-D6D9-464A-BF44-D17AACEBD5B8@xxxxxxxxxxxxxxxx
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" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sprinks" <Sprinks@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:457DFE03-3A3A-4A71-A4B7-75D938C29C91@xxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • Re: Form/Subform
    ... The issue is that users don't think in terms of "related records" and "what ... so detail records before entering a project number, ... Better to cancel the BeforeInsert event of the subform if the main form is ... I recently discovered that if the user enters detail records without first ...
    (microsoft.public.access.forms)
  • Re: Form/Subform
    ... then moving the focus to the subform. ... value if they ultimately get around to entering one somewhere. ... The issue is that users don't think in terms of "related records" and ... I recently discovered that if the user enters detail records without ...
    (microsoft.public.access.forms)
  • Re: Form/Subform
    ... Allen Browne - Microsoft MVP. ... subform". ... value if they ultimately get around to entering one somewhere. ... I recently discovered that if the user enters detail records without ...
    (microsoft.public.access.forms)
  • Re: query wont return results
    ... record in that table before you can enter a related record in tblHoles. ... your data entry process first needs to focus on entering data ... Create a form that is based on a query that uses tblCourses as its data ... LinkChildFields properties of the subform control) where the subform is ...
    (microsoft.public.access.queries)
  • Re: I want my subform to stay "locked" to my main form.
    ... and/or main form data with incorrect subform data. ... For some reason, I ... to the related record shown in the parent form. ... Proper relationships would prevent you from entering a child record without ...
    (microsoft.public.access.forms)