Re: Continuous Subform / Form - Orphan records

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

From: Van T. Dinh (VanThien.Dinh_at_discussions.microsoft.com)
Date: 10/08/04 

Date: Fri, 8 Oct 2004 21:21:23 +1000

As I suspected, your Tables are not structured correctly. You would have
problems later with this structure. For example, someone may decide that
the database needs to store 7 items rather than 6 and with this structure,
you will need to re-design the Tables and other objects that are based on
the Tables, e.g. Queries, Forms, Reports (and in turn, VBA codes).

Suggest you check out the Relational Database Design Theory and the Database
Normalization techniques and re-structure the Tables accordingly. This will
save you heaps of problems later. Note also that Access is designed to work
efficiently with correctly-structured Tables so once you get it right, most
of the later steps in database design & development will follow logically. 

-- 
HTH
Van T. Dinh
MVP (Access)
"Rich J" <RichJ@discussions.microsoft.com> wrote in message
news:E5AF403C-C3F8-43F5-BA2A-31FB82E84FA5@microsoft.com...
> The main form has one primary field
> tDiaryID
> other fields are:
> fgnEngrID
> ItemNumber1
> .
> .
> ItemNumber6
> DescriptionItem1
> .
> .
> DescriptionItem6
> + other fields that the user or VBA fill in such as weather or text
>
> The subform has three primary fields
> tDateID
> fgnWkrID  < -- links to table with worker names
> fgnEngrID <-- person writing diary
> other fields are:
> tItemNumber1
> tHrs1
> .
> .
> tItemNumber6
> tHrs6
>
> Each record of the subform records a worker and the hours he worked on a
> particular item.  Up to 6 entries of various tasks for the day.
>
> Link Child Fields  tDateID;fgnEngrID;tItemNumber1;...tItemNumber6
> Link Master Fields tDiaryDate;fgnEngrID;ItemNumber1;..ItemNumber6
>
> The data entry works great. I enter the Item Numbers on the main form and
> when hours are placed in the subform and a worker selected all the Item
> Numbers that have been entered in the main form appear in the subform.
Then a
> new subform record is automatically generated awaiting new data entry.
But
> if you make a typo or change the Item Number entered in say ItemNumber1 of
> the main form, then a new subform record is generated.  The previous
entries
> are still in the subform table but need their Item Number updated with the
> main form to work correctly.  Since the main form was changed there is no
> main form corresponding to the subform records and it leaves orphan
records
> in the subform table.
>

Relevant Pages

  • Re: Continuous Subform / Form - Orphan records
    ... > Suggest you check out the Relational Database Design Theory and the Database ... >> The subform has three primary fields ... >> main form corresponding to the subform records and it leaves orphan ...
    (microsoft.public.access.forms)
  • Re: access crashes when form is filtered
    ... Compact the database to get rid of this junk: ... Uncheck any references you do not need. ... >> form has a subform, the crash could be due to this bug. ...
    (microsoft.public.access.forms)
  • Re: #Error - Bookmark Invalid
    ... I'm assuming you have split this database so that everyone has an ... > it helps to set the Format property of all calculated text boxes that are ... If your subform has no records and no new records can be added (e.g. ... > calculations keep retriggering. ...
    (microsoft.public.access.forms)
  • Re: update query
    ... structure of the database. ... I made a subform, using the tools on the left side, to make a subform ... pointing to the Transaction data base, bookbarcode, studentID, date- ... barcode - primary key ...
    (microsoft.public.access.queries)
  • Re: Main form with 2 subforms - Error: Method requery of object _Subform failed
    ... Reginald, these things can be quite difficult to trace. ... Compact the database to get rid of this junk: ... subform control if you don't yet have a text box in the subform. ... Still in the code window, choose Compile from the Debug menu. ...
    (comp.databases.ms-access)