Automatically generate a new record in a second table?

  • Thread starter Thread starter John.Humble
  • Start date Start date
J

John.Humble

In my Access 2000.DB, Table_Main (primary key "Key" (auto-number))
contains details of transactions (date, category, amount, receipt
number. etc ). The table is accessed via Form_Main. For all but one
of the 20-odd categories the information stored in Table_Main is
sufficient.

For the remaining category, "Special" (which only occurs a couple
of times a year), another seven fields of information are required. It
would be misleading to have the additional fields visible in Form_Main
or stored in Table_Main, so they live in Table_Extra, keyed by
"Reference" (integer). Table_Main.Key and Table_Extra.Reference
are joined in a 1:1 relationship.

Could someone please suggest how I can get a new record to be opened
automatically in Table_Extra, with the correct value of
"Reference", whenever a new record with category = "Special" is
established in Form_Main and without intervention by the DB user? The
various categories, including "Special" are user selected from a
list box. I'm quite happy to have the user subsequently populate the
remaining fields of Table_Extra via another Form, Form_Extra..

With thanks

John
 
For the remaining category, "Special" (which only occurs a couple
of times a year), another seven fields of information are required. It
would be misleading to have the additional fields visible in Form_Main
or stored in Table_Main, so they live in Table_Extra, keyed by
"Reference" (integer). Table_Main.Key and Table_Extra.Reference
are joined in a 1:1 relationship.

Could someone please suggest how I can get a new record to be opened
automatically in Table_Extra, with the correct value of
"Reference", whenever a new record with category = "Special" is
established in Form_Main and without intervention by the DB user?
Click to expand...

The simplest way might be to have a Subform on the form, using
Reference as the master/child link field. Have the subform invisible
normally.

In the AfterUpdate event of the category combo box, simply make the
subform visible and set focus to it. When the user enters data it will
fill in the Reference value.

If you also use the Form's Current event to check the value of the
category, you can make existing Special records display or hide the
special data.

John W. Vinson[MVP]
 
Back
Top