Create new child record with modal dialog?

[Sheridan]

I'm trying to implement a simple communication log feature. I have two tables, Members and Logs in a one to many relationship.

I want to add a button to the Members form called "Create New Log." When clicked, it opens a modal dialog window where the user can fill out the fields for the Logs table, click 'Save', and Access appends a new record to the Logs table with the information the user filled in, and most importantly, with its foreign key id set to whatever Member record the user was viewing just before clicking "Create New Log".

It's this last bit I'm stuck on. I can't figure out how to bring in the ID of the Member record from the Member form over to the "Create New Log" form.

The "Create New Log" form is based on a SELECT query that just selects the fields of the Logs table.
Data entry is set to 'Yes'.
On the "Create New Log" form, I set the default value of the foreign key field to this: "=[Forms]![Members Form]![ID]"
The button on the Member form was made with the 'Open Form' button wizard option.

The button opens the form, and I can save a Log record, but the foreign key field is saved as '0' instead of the member ID.

 

[MajP]

Pass the ID in the openargs argument of the docmd.openform
In the forms beforeInset event set the foreign key to your openargs value.

 

[theDBguy]

Is your modal form bound or unbound?

 

[MajP]

but the foreign key field is saved as '0' instead of the member ID

This is annoying, but for some reason Access sets the default value property in the table properties for all numerics to 0 instead of simply leaving it null. Make sure to remove this in the table properties. Especially for a foreign key which will never be 0.

 

[LarryE]

I'm trying to implement a simple communication log feature. I have two tables, Members and Logs in a one to many relationship.

I want to add a button to the Members form called "Create New Log." When clicked, it opens a modal dialog window where the user can fill out the fields for the Logs table, click 'Save', and Access appends a new record to the Logs table with the information the user filled in, and most importantly, with its foreign key id set to whatever Member record the user was viewing just before clicking "Create New Log".

It's this last bit I'm stuck on. I can't figure out how to bring in the ID of the Member record from the Member form over to the "Create New Log" form.

The "Create New Log" form is based on a SELECT query that just selects the fields of the Logs table.
Data entry is set to 'Yes'.
On the "Create New Log" form, I set the default value of the foreign key field to this: "=[Forms]![Members Form]![ID]"
The button on the Member form was made with the 'Open Form' button wizard option.

The button opens the form, and I can save a Log record, but the foreign key field is saved as '0' instead of the member ID.

Just make the "Create New Log" form a Sub-form of the Members form and use the Link Master Fields and Link Child Fields properties to link the forms together. Then, ACCESS will complete the foreign key entries for you automatically and the form doesn't need to be opened independent of the Members form.

By the way, change the Members table Primary Key from just ID to MemberID. Never use just ID as a Primary Key name.

 

[Sheridan]

Pass the ID in the openargs argument of the docmd.openform
In the forms beforeInset event set the foreign key to your openargs value.

Can you elaborate please? Sorry, it sounds simple but I'm new to Access and I can't find where I would pass "Pass the ID in the openargs argument of the docmd.openform". I suspect this involves doing something in VBA? I'm currently reading the chapters on VBA from the Access Bible 2019 but I haven't seen anything yet that might help me here. Thanks!

 

[Gasman]

I have Google on this phone.

Form.OpenArgs property (Access)

Office VBA reference topic

learn.microsoft.com

 

[Sheridan]

Just make the "Create New Log" form a Sub-form of the Members form and use the Link Master Fields and Link Child Fields properties to link the forms together. Then, ACCESS will complete the foreign key entries for you automatically and the form doesn't need to be opened independent of the Members form.

This would use a datasheet view for the subform, correct? I've done that with some other subforms before. Here though, there are a few text fields on the log form where the user might need to enter a paragraph worth of text. I thought doing that might be cumbersome to view in datasheet view since it doesn't wrap text, right?

 

[Pat Hartman]

As MagP suggested, pass in the PK using the OpenArgs

If you pay attention to the intellisense as you type the OpenForm instruction, when you get to the OpenArgs part just use Me.MyPKName.

Then in the BeforeInsert event of your Modal form, add --
Me.MyFK = Me.OpenArgs

This method does not dirty the Modal form before the user does so if he cancels without typing anything, no empty record gets created and this method allows you to add multiple records if that makes sense and get them all properly linked with a FK that points to the main form record.

If you were using a subform (Larry's suggestion), you would use the master/child links to tell Access how the main/sub forms are linked and Access properly populates the FK as you add each new record.

It probably makes more sense to use a Modal form if you don't want to have a subform to show all the log records. However, If you do have a list type subform, you will need one additional line of code to make the newly added log record show up. As the statement FOLLOWING the OpenForm method, add --

Me.Requery

Notice that if you define the popup form as Model, execution of code in the first form stops immediately after the OpenForm method runs. Then when the Model form closes, execution continues on the next line and so your Requery will run and the new log record will be visible on the subform.

PS, just in case you haven't figured this out yet, you MUST save the current record BEFORE you open any form or report or any unsaved data will not be available to whatever form/report you are opening.

 

[LarryE]

This would use a datasheet view for the subform, correct? I've done that with some other subforms before. Here though, there are a few text fields on the log form where the user might need to enter a paragraph worth of text. I thought doing that might be cumbersome to view in datasheet view since it doesn't wrap text, right?

Makes no difference which form view you use for the sub-form. I never use datasheet view anyway. If you need a larger text field to accommodate more text, then just design it wider or higher or both. If it needs to be more than 255 characters, then use a Long Text data type. Text boxes can use scroll bars, so if it absolutely can only be a fixed height, then turn on the vertical scroll bar property.

 

[Pat Hartman]

The advantage of DS view is you can expand the row/columns on the fly to see more data. In any case you can use Shift-F2 to open a dialog to see a larger version of a single field and use it for data entry if you prefer.