Continuous Subform / Form - Orphan records

[Guest]

I have a continuous subform linked to the main form by Date, OriginatorID and
6 Item Number fields so that when an Item Number is entered on the main form
it automatically gets entered when a new record is generated in the subform.
I have 6 more fields that correspond to each item number field to enter a
quantity. This works great until one of the Item Numbers is changed in the
main form. This starts a new set of subform records with the new Item
Numbers. The other subform records are not seen unless the the item numbers
are changed back on the main form.
I can see two possible solutions but don't know the coding involved.

One is to be able to change the subform field when the corresponding main
form field is changed instead of creating a new subform record. This is
where the orphan records are being generated now.

The other is to just link the forms by Date and OriginatorID and then
somehow have VBA code enter the Item Number in the corresponding subform
field from the main form.
I've tried various VBA codes for each and can't figure it out. Having all 6
Item Number fields linked to the main form seems to work great except for the
need to be able to change the subform at the same time the main form is
changed instead of creating a new subform record.
Thanks for any advice.

 

[Van T. Dinh]

I am not sure of your description regarding the "6 ItemNumber Fields".
Perhaps you may like to post the details of the Table being used as the
RecordSource for the Subform as well as the details of the Subform. I have
a suspicion that your Table may be unnormalised here since it sounds like
you .

In fact, post also details of the Table being used as the RecordSource for
the main Form. Also, post how you "link" the Form and Subform.

I have a suspicion that your Table may be unnormalised here since it sounds
like you repeating groups of Fields (each group can consist of a single
Field).

 

[Guest]

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.

 

[Van T. Dinh]

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.

 

[Guest]

The reason for only 6 items is that the form is a database version of a hand
written diary. That diary has been used for years. It has 6 columns that
would each be headed by an Item No. if the column is filled in. The hours
are entered underneath and the name/s are on the right. There will never be
a need for more than 6.
There may be a list of over 100 items to choose from for the activity but
rarely do even all 6 on one diary get filled in for the day. In Excel it was
easy to create the grid but it has been a challenge in Access. The paper
diary has 17 lines for entry. The diary we are using now on Access has about
250 fields. Each of those 17 lines + the header line has 10 columns
including the 6 for manhours.
The subform meant that each line would become a record and the number of
fields is more manageable. Thanks for your interest in this problem.
Hopefully my attempt at showing an example below will help.

| 4 | 8 | | 16 | | 33 | <-Item No.
| 4 | 4 | Worker 1 Name
| | | | 8 | | | Worker 2 Name
| | | | | | 16 | Wkr 3, Wkr 4

 

[Van T. Dinh]

I avoid designing my database using "currently used printed forms" since
printed forms often have unnormalised data structure which is not suitable
for (electronic) database. I look at what are the results required at the
end which normally tell me what data I need to collect and the structure the
Tables for these data needs according to the relational database theory. I
remember one of my former clients asking for an on-screen data entry form
that looked exactly like a printed form of A3 size they were using at the
time. Luckily, I declined that job because it was a disaster for the
programmer who took the job.

Note that Access is designed to work efficiently and easily with
properly-structured Tables. When the Tables are not unnormalised, Access
tends not to have the features to work with unmormalised Tables or there
will be a lot of coding involved just to get things work out correctly.
This seems to be the case for your set-up, especially that you store the
(repeating groups) of [Item Number]s in both the Table that binds the Main
Form and the Table that binds the Subform.

I would again suggest you look into Relational Database Theory & Database
Normalisation and check out for yourself whether you should use the
Relational Database Design Principles and Database Normalisation and proceed
from there.

 

[Guest]

I have to make the data entry as user friendly as possible. The table now is
maxed out for fields due to (actually) 13 columns times 17 rows + other data.
Each of those 221 cells requires a field in the table now. With the subform I
was able to reduce the main form to about 30 fields and the subform has about
20. The reason for recording the Item Number in both the main form and
subform is because of the way the paper form is designed. Also, the data is
only recorded once in the main form and then with each record of the subform.
The form I have works except if the Item Number is changed in one of the six
fields on the main form.
I'll read up on the normalised tables and continue playing with various
coding and table changes.
I had an idea of putting the main DiaryID in each of the subform records
associated with that main record. Then searching for all records in the
subform with that and changing the Item Number in each of the 6 fields.
Haven't tried to code it yet though.
Thanks again for your interest in my question.
Rich J

I avoid designing my database using "currently used printed forms" since
printed forms often have unnormalised data structure which is not suitable
for (electronic) database. I look at what are the results required at the
end which normally tell me what data I need to collect and the structure the
Tables for these data needs according to the relational database theory. I
remember one of my former clients asking for an on-screen data entry form
that looked exactly like a printed form of A3 size they were using at the
time. Luckily, I declined that job because it was a disaster for the
programmer who took the job.

Note that Access is designed to work efficiently and easily with
properly-structured Tables. When the Tables are not unnormalised, Access
tends not to have the features to work with unmormalised Tables or there
will be a lot of coding involved just to get things work out correctly.
This seems to be the case for your set-up, especially that you store the
(repeating groups) of [Item Number]s in both the Table that binds the Main
Form and the Table that binds the Subform.

I would again suggest you look into Relational Database Theory & Database
Normalisation and check out for yourself whether you should use the
Relational Database Design Principles and Database Normalisation and proceed
from there.

--
HTH
Van T. Dinh
MVP (Access)

The reason for only 6 items is that the form is a database version of a hand
written diary. That diary has been used for years. It has 6 columns that
would each be headed by an Item No. if the column is filled in. The hours
are entered underneath and the name/s are on the right. There will never be
a need for more than 6.
There may be a list of over 100 items to choose from for the activity but
rarely do even all 6 on one diary get filled in for the day. In Excel it was
easy to create the grid but it has been a challenge in Access. The paper
diary has 17 lines for entry. The diary we are using now on Access has about
250 fields. Each of those 17 lines + the header line has 10 columns
including the 6 for manhours.
The subform meant that each line would become a record and the number of
fields is more manageable. Thanks for your interest in this problem.
Hopefully my attempt at showing an example below will help.

| 4 | 8 | | 16 | | 33 | <-Item No.
| 4 | 4 | Worker 1 Name
| | | | 8 | | | Worker 2 Name
| | | | | | 16 | Wkr 3, Wkr 4