Why subform won't populate additional records

Please see attached example database.

When you launch the d/b a form will open named frmJobSetup.  This form has two subforms.  The one I am trying to troubleshoot is the right-hand subform.  

I cannot figure out why the following is happening, (or not happening).

If you select "Equipment" from the Category drop-down in the subform and then select "Some kind of line item" from the LineItem drop-down, 4 additional records get entered which is exactly what should be happening.  If you open the form, "frmLineItems" you will see why.   "Some kind of line item" is a "master" item that has 4 related records tied to it which is why the query, "apndtblqryAdditionalLineItems" is adding the 4 records.  THIS IS GREAT!!!

But then please select "Equipment - Coils" from the Category drop-down and then select "Line item for equipment - Coils" from the LineItem drop-down and NO additional records get populated into the form.  One additional record is supposed to populate the form.  Again if you open the form frmLineItems and go to the 3rd record you will see why.  There is one related record.

Why does this work for "Equipment" - "Some kind of line item" but not work for "Equipment - Coils" - "Line item for equipment - Coils"


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
It's because you haven't control over the assigned JobiD.

If you go to job 2 and assign lines, they will be appended to job 1. If you then go to job 1 and assign lines, they'll be appended to job 2.

SteveL13Author Commented:
If I understand you correctly I have made a change in the new attachment.  But it is still not working.

The concept is:  Job > Units > Line Items.

There is only one job.  But with two units.  When I added  "Equipment" - "Some kind of line item" I got what I expected.  The master line item and three related line items.  But when I then added "Equipment - Coils" - "Line item for equipment - Coils" I got the selected "master" item but not the other 2 related items.

If you delete my line items and try to duplicate what I had selected I think you'll find the same thing.  Am I right?

Sill confused.

Gustav BrockCIOCommented:
It clearly doesn't behave. However, I'm not sure I understand at all what the concept is and what should be happening - even your description of the intended happening is unclear to me.

I have a strong feeling that you should rethink the concept and rebuild this part of the application completely - indeed to make it more clear for the user.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Can you tell us how to demonstrate the issue.
Attached your database, specified the subforms with blue and green borders.

Give a step by step instruction from opening the main form. Each step mention what you see and what to expect if different.

You don't need to include all the object in the demo database.
SteveL13Author Commented:
First of all the only thing to be concerned with is the green subform.  

If I select "Equipment" from the Category drop-down and then select "Some kind of line item" from the Line Item drop-down I get 3 additional line items populated into the subform.  This is exactly what I want to have happen.  The reason they get populated is because the selection, "Some kind of line item" has 3 related line items as defined in the maintenance form named frmLineItems.  If you open that form you will see what I mean.  (See record #1 in that form)

But here is the problem.  If I select "Equipment - Coils" from the Category drop down and then select "Line Item for Equipment - Coils" from the Line Items drop-down I only get the item I selected and not the other 2 records to populate..  Again if you open the form frmLineItems you will see that there are 2 related records that should populate the green form.  (See record #3 in that form)

I sure hope this helps.  I really don't know how else to explain the situation other than with some kind of online meeting.

SteveL13Author Commented:
To all.. I just resolved the issue and will post a file shortly in case anyone wants to see it.
SteveL13Author Commented:
Here it is.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
I found a solution that worked.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.