Using a command button to open another form to add a record to a subform.

[moneil]

Following up on my question from July 21, 2018 (which June7graciously helped me with) about opening a form to add a new record ….


That situation was pretty straight forward as I was simplyadding a new record to a table with no “parent relationship” above it. This time I want to add a new record to atable that has a “child relationship” and that relationship link needs to bepart of the add new record procedure.
There is an Events table (primary key EvtID) which storesinformation about a food drive event. There is a Collections table (primary key ColID) which storesinformation about food collections from a food drive event. The Events table has a one-to-manyrelationship with the collections table as there may be more than one food drivepick up during an event. The collectionstable has a foreign key named EvtID which establishes the relationship betweenthe two tables.
There is an Event Detail form (form view) with a Collectionssub form (datasheet view), which contains only the ColID primary key number,the linked EvtID number (hidden), collection date, and pounds collected. Ifthere are already collections records one can click on the ColID field of aparticular record and a Collection Detail form opens up in form view. This form contains all the fields from theCollections table and is what one uses to record all the information about thatfood drive collection.
If a new collection record for an event needs to be added,regardless if it is the first collection record for the event or an additionalone, rather than going to the (New) line of the collection datasheet sub form,putting in a date, saving the record (ctrl + s), then clicking on the ColID ofthe new record to bring up its Collection Detail form, this is what I wouldlike to do, but can’t figure out:
I would like to have a command button labeled “Add NewCollection Record”. When clicked I wantthe button to open a new collections table record in form view so that a newcollection associated with the linked event can be added.
I don’t know if I should use a macro, or some other type of “code”for this. I have used macros before toopen a form, and to create a new record, but in this situation the newcollection record has to also have an association with an event … that is, whena new blank Collection Detail form opens its EvtID field (the Collections table’sforeign key which links it to the Events table) needs to be populated with thesame number showing in the Event Detail form’s EvtID field (the Events table’sprimary key).

[ranman256]

If you open a form,that starts with a new record, I would disable the subForm until the new record is saved, then Enable the subForm.
otherwise user will get an error trying to enter subForm recs before the master rec is saved.

[ssanfu]

I tried to follow you narrative, but I might have gotten lost - not enough names (fields, forms, etc).

This will probably need VBA code. (I never use macros). And I think it would be better to use continuous forms for the sub form instead of datasheet (but I haven't seen your dB).


You can/could use the "Open Arguments" argument of the "Open Form" method to get the PK of the selected record in the parent form when the "New Collection" button is clicked.

Which form do you want the "New Collection" button on?

Maybe you would post a copy uf your dB? Delete all but a couple of records (change any sensitive data), do a "Compact and Repair", then zip it.

[moneil]

Thank ssanfu for your help. I have attached a copy of the database, and hopefully I am explaining its structure and what I hope to do below.

This is a database to manage food drives for a food bank warehouse.

There are three tables: Organizations (primary key OrgID), which has a one to many relationship with Events (primary key EvtID), and Events has a one to many relationship with Collections (primary key ColID).

Each table has two forms based on it:
A datasheet form which contains only a few of the table’s fields and its purpose is to provide a list view of organizations, events, or collections, and to be a “conduit” to open a more detailed form for each record.
A detail form, in form view, which contains all the fields from the table.

From the menu one opens the Organization List form. It has the Organizations Datasheet inserted as a sub form. One can either click on an organization’s OrgID field to open that organization’s
Organization Detail form, or click the Add New Organization command button, which opens a blank Organization Detail form in which a new organization’s information can be added.

Within the Organization Detail form the Events Datasheet is inserted as a sub form, showing all the food drive events associated with an organization. One can click on an event’s EvtID number to open the Event Detail form for that event. Within the Event Detail form a Collections Datasheet is inserted as a sub form, showing all the food drive collections associated with an event. One can click the ColID number to open a Collection Detail form for the collection.

On the Organization Detail and Event Detail forms I included instructions for when a user wants to add a new event or collection, by going to the (New) line of the events or collections datasheet sub form. Because the events and collections datasheet forms are inserted as sub forms in the organization and event detail forms respectively, their relationship with the parent form is established.

I would like to replace these instructions with a command button that would open a blank event or collection detail form to be filled out. That command button process would also need to include code that would establish the link / relationship between the new event and its parent organization, or the new collection and its parent event. This is what I can’t figure out.

[ssanfu]

My apologies... I got really carried away.

So I removed the spaces and special characters in the object names.
And this is where I went off the deep end.


What you want to look at is the button "Add Collections". When you click on the button, code runs that opens a form with the "EvtID_PK" as an "openArgs" parameter. Then code in the form open event executes that stuffs the "EvtID_FK" control.

The other two "Add" buttons do the same thing.....