Updating/adding problem

[Janet]

Hello.

We have two tables: 'Customers' and 'CustNotes'

Both have a unique and related identifier called CUSTNO.

Our problem stems from the fact that the 'Customers' table is periodically
replaced in its entirety with an import procedure, and it contains no field
suitable for our use for notes. Hence our creation of a second table to
contain the notes. We can relate the two tables using the common field
CUSTNO and produce useful information from a simple query in forms
and reports. But we're stymied on how to add new notes where they
didn't exist before.

Let's say we have this query-based form:

Customers.Custno: 123
Customers.CustName: Acme
CustNotes.Custno:
CustNotes.NoteData:

If we enter information into NoteData, the record won't save properly
because CustNotes:Custno is empty (zero). If we manually type it in,
it's OK.

Is there some way to have CustNotes.Custno prepopulated with the
value of Customers.Custno to avoid this?

Thanks.

 

[Tom van Stiphout]

Yes. This is a classic one-to-many situation, which in Access is
implemented with a form and subform. The form is bound to the
Customers table (NOT your query), and the subform to the CustNotes
table (NOT your query), and the subform control's LinkMasterFields and
LinkChildFields are set to CUSTNO. That should do it.

-Tom.

 

[Janet]

DOH!

Thanks.

Yes. This is a classic one-to-many situation, which in Access is
implemented with a form and subform. The form is bound to the
Customers table (NOT your query), and the subform to the CustNotes
table (NOT your query), and the subform control's LinkMasterFields and
LinkChildFields are set to CUSTNO. That should do it.

-Tom.