Add new record at Main Form

That's not the way Access works because that isn't the way relational databases work.  Which doesn't mean that you can't do it.  It just means that you have to do it with code and you can't do it exactly the way you described.

Assuming the tables have relationships established in the Relationship diagram, the database engine will NOT allow you to add a record to a child table when there is no corresposding record in the parent form.  The parent record MUST be added to the table first so that its autonumber can be assigned because the child record needs the autonumber to use as its foreign key.

By your description, it sounds like there are no data fields in the subform table.  Just the PK.  What is the point?  If you explain your schema and what you are trying to accomplish, we can offer alternatives.

Hello Pat,
Thanks for your reply.  Here's the code on the subfrom (derived from a previous post at EE).

Private Sub Form_BeforeInsert(Cancel As Integer)

    If Me.Parent.NewRecord Then
        Me.Parent!CreationDate.Value = Date
        Me.Parent.Dirty = False
    End If
 
 End Sub

The specific details of “why” I need this fix are too lengthy to explain – put simply…I need fewer mouse click to accomplish task.

Prior to code above, I had to click on a date field to create Parent record, then select value from drop down, to create record in sub form, and then enter qty into sub form.  I might have to do this 100 times over to fulfill a single order.  So, I expedited process with code above.

Now, I’m just trying to take it to next level.  Please let me know if I can answer any additional questions.

Thanks,
Ken

I'd agree with Pat - you're working against the way relational databases work. As Pat mentioned, before you can add a Child record (or a Grandchild record) you have to have a Parent record (or Grandparent record). 

That said, if you must, you can go back "up the chain" to the Grandparent:

If Me.Parent.Parent.NewRecord Then
      Me.Parent.Parent!CreationDate.Value = Date
      Me.Parent.Parent.Dirty = False
End If

I believe that's the right syntax, but quite honestly I haven't had much opportunity to do things like this.

But I'd sure like to know why you have to create grandhildren records before the Parent or Grandparent records are created. This generally indicates a problem with structure.

If you had to pick the same value 100 times, then that field was in the wrong table.  It should be in the parent table.  It makes absolutely no sense to have an intermediate table with NO columns.  It also makes no sense to insert a row in a table with only the autonumber populated but no other data values.  It looks like you are autopopulating the date but perhaps the date belongs in the subsubform record and you don't need the subform at all.

You're explaining your technical solution and asking us to fix it but to me, it looks like you have a different problem.  WHY do you have this intermediate table with NO columns other than a key?

When the mainform opens, what do you see in the subform?  Do you see a date?  What if the subsubform records are for a different date?  Do you then have to scroll the subform to a "new" record?  

Please post your schema and explain to us the purpose of the subform's table.

Here's the description of why I need this fix

Let’s say you want to order 1000 widgets, and you need for me to maintain traceability for when each widget was made.  Further, you specify a bag qty of 250 pcs per bag.  So, I’m going to need to build a packing slip t-PackingSlip w/ key [PackingSlipID], and I’m going to then associate 4 bags to that packing slip t-Bag Label w/ key [BagID].  I’ll do this with a form – subform set-up.  

Over on the inventory side - let’s also imagine I have 1000 parts in stock, made over 5 days (200 widgets from 5 different days).  So I have 5 separate inventory records t-Inventory w/ key [InventoryID].

So, to ship your order, I'll need to create a [PackingSlipID] with 4 [BagID] (250 each) from 5 different inventory records [InventoryID].  

Packing Slip Record
Bag 1 Record - 250 pcs total
200 pieces Inv Record A, 50 pieces Inv Record E  (so, two bags w/ unique labels, packed inside a larger 250 pcs bag)  
Bag 2 Record – 250 pcs total
200 pieces Inv Record B, 50 pieces Inv Record E
Bag 3 Record – 250 pcs total
200 pieces Inv Record C, 50 pieces Inv Record E
Bag 4 Record – 250 pcs total
200 pieces Inv Record D, 50 pieces Inv Record E

Build Bag
To build a bag, I enter a date to generate the Bag record @ f-Bag Label, then select an inventory # in the sub-form and enter a qty.  Minimum three keys clicks per bag (+ 2 more / additional inventory numbers are used).  Large order = lots of bags = lots of clicks.  Hence the earlier code.

Add Bag to Packing Slip
Packing Slips are associated with orders.  PackingSlipLineItemDetails are Associated with OrderLineItemDetails.  After a bags are created, I have to associate the bags with a line item OrderLineItemDetails.  All the clicks just add up.

Sorry for abandoning conventional procedures here.  10 years ago when I started my business, I wasn’t sure how this order fulfillment process is supposed to work, I also didn’t know the first thing about Access.  Thanks to EE, I’ve made great progress and I have learned a lot, but there are legacy parts of the database that just seem to big for me to tackle and undo.  So, I hope this “inappropriate approach” doesn’t cause the purist too much angst.  One of these days, I sure would like to hand this over to someone to review, improve, and do right!   Thanks

It's not that we're purists, it's just that we've all been down that road before, and eventually that road ends, or loops back on itself (to infinity), or gets so broken up you're forced to fix it before you can keep driving on it!

You can do what you ask like this, in the Dirty event of your SubSubForm:

Me.Parent!Field1 = "some value"

This will force Access to either (a) write a new record or (b) update the existing record in the Parent form. If you have your subforms linked correctly, Access will write the correct values in the SubSubform's linking field, and your records will be correctly related.

If you also need to write a record in the Grandparent form, I'd suggest you do something similar in the Parent form (i.e. your Subform). In that form's dirty event, you would use the same code to write a control/field in the Grandparent form.

But I would strongly encourage you to think about reworking your process. Eventually you'll find the road to broken to fix!

Thanks, Scott.  My "purist" comment was really a tongue-in-cheek comment; I really appreciate your and everyone's level of knowledge and expertise. So, just wanted to make sure that my comment wasn't misinterpreted!  

Okay, so I've tried this a couple of different ways, but every time I type a value into the [Qty] field in the subsubform and hit enter, a new record is created on the subsubform,  Instead, what I want is to force a new record at the subform, and then position focus to [Qty] in the subsubform.

To address one issue that both you and Pat bring up - there are multiple columns of data on the records behind these subforms + subsubforms. Yet, I still feel that this request meets the criteria for good DB practice....but, perhaps I have not described my scenario well enough.

Here are some details on the tables behind the forms.

Form:  InventoryID (AutoNumber), PartNumber, (plus additional unrelated columns)

SubForm:  BagID (AutoNumber), InventoryID, PartNumber, CreationDate

SubsubForm:  InventoryRemoveaID (AutoNumber), BagID, InventoryID, Qty

Master/Child links:  
F:SF- InventoryID, PartNumber,
SF:SSF - BagID, InventoryID


Bag labels are created at the SubForm Level, and I require a unique BagID record for every printed label.  With my regular form, I have the flexibility to generate multiple SSF records for each SF record, and this is good.  

Every now and then, however, I have a situation whereby I need to create ~100 labels with the same SF data…I just need to enter a Qty value (probably the same number) onto SSF…..say100 times.  So, I just wanted to build a special form that allowed me to expedite the generation of 100 BagLabelIDs at the SF level whereby the InventoryID, PartNumber, & CreationDate were the same.  For this, I need to enter a value into the SSF and then have that “Data Enter” function force a new record at the subform, and then position focus to [Qty] in the subsubform.

I hope I’m explaining my request sufficiently.  Please let me know if I need to better clarify, and thanks in advance.

Okay, so I've tried this a couple of different ways, but every time I type a value into the [Qty] field in the subsubform and hit enter, a new record is created on the subsubform,

If you use the code I suggest, do you still get that situation? I tried this, and it created a new record each time in the Subform.

As mentioned, if you want to force the creation of a new record on the "grandparent" form, you'll have to use the Me.Parent.Parent syntax.

Hello Scott,
I've attached a a simplified version of database and form.  The Sub and SubSub are located on f-Inventory (far right tab).  Can you look at the OnDirty Event and let me know what I'm missing?  

Thanks again for continued support,
Ken
Database10.accdb

I didn't test this but I don't think Scott's solution will work.  When you leave the subsubform to dirty the subform and force it to save, Access will want to save the subsubform first but it can't because the parent record has not yet been created and so the foreign key is not available..

The only way I see this working is if the middle form is unbound that that opens a whole raft of other issues such as how you scroll it.  Then, you could insert the parent record from the On Insert event of the subsubform.  That event runs ONCE for each new record as soon as the record is dirtied.  The dirty event runs for existing records also.  The other thing you need to do is to capture the autonumber of the subform when that record is manually added and use it to populate the foreign key on the subsubform.  You won't be able to use the master/child links and all that does for you with the middle form being unbound.

When i look at the relationship diagram, the "subform" is actually the grandchild and the subsubform is actually the child so this is even more confusing.

I can't run your form. The Inventory combo on the SubSubForm is set to use a query named "q-Form-Inventory Listing(Sub)II (Tyler)", and that query doesn't exist in the database.

That said, you can achieve what you want like this in the Dirty event of your SubSubform:

If Not Me.Parent.Dirty Then
    Me.Parent!Field1 = "some value"
    Me.Parent.Parent!Field2 = "another value"
End If

That would "dirty" both the Parent and the GrandParent forms, and get you where you need to be (at least for the moment).

Of course this brings other challenges as well - for example, what happens if you're adding a Bag to an existing record, and the values in the Parent!Field1 or Parent.Parent!Field2 already exist, you don't want to overwrite them? You should probably include some form of checks to determine the validity of your data before you overwrite anything.

I also reviewed your relationship diagram and don't quite get the relationships. What is t-Inventory (Remove), and why would it be between Inventory and BagLabel (which, presumably, stores the inventory you're putting into a specific Bag). This would be critical to understanding how everything is supposed to work.

FWIW, the overwhelming majority of my work these days is directly involved with some of the largest ERP/MRP systems out there (M1, Macola, SAP, Sage, SalesForce, Jobboss, Epicor, etc). In regard to their Inventory measures, all of those are built around the concept of Transactions of Materials - like receiving finished goods inventory into your system (i.e. buying nuts and bolts, ready to use), transferring finished goods inventory into your system from a Job or other Process you perform, receiving  returned items back into inventory, etc etc. Unless you're working with this sort of transactional processing you'll eventually hit some of those fully damaged roads I wrote about earlier (and I'm telling you this from a place of <shudder> real life experience with another home grown MRP system I worked with years ago).

I realize you said this is a home-grown solution, so this is just food for thought down the road.

Thanks, Scott.  Let me answer your question about the t-Inventory(Remove).

We make over 100 different parts here.  Parts are made and checked into inventory by inventory number....not by part number.  We do this so we can maintain lot traceability (who made the parts, when were they made, material lot numbers, etc.).  When we get an order from a customer, we have to remove product from inventory, and we have to know the qty and inventory # of that pulled product.  The t-Inventory(Remove) serves that purpose, and this allows us to maintain traceability as we group those parts into a bag (of some quantity).  Bags then get grouped together to satisfy a specific order qty..  This information becomes a line item (perhaps one of many lines items) on a packing slip.    

Does this answer your structure question?

Thanks,
Ken

Try changing the hierarchy on the form to match the hierarchy of the tables.