Create new child record with modal dialog? (1 Viewer)

[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.

 

[Sheridan]

Thanks for the assistance everyone. I got it working finally! My initial mistake was using the wizard to create the button I suppose. I made a new button without the wizard, set its on click event to embedded procedure, then wrote this:

Private Sub New_Log_Record_Click()

    DoCmd.OpenForm "Log Modal", acNormal, , , acFormAdd, acDialog, Me.ID
    Me.Refresh

End Sub

"Me.Refresh" refreshes the parent record page after closing the modal dialog.

Under the button, I added a subreport that shows the logs related the parent record. I used a report instead of a subform because I wanted the text fields to wrap. Then I added an edit function to the report that opens the log record in a dialog window when you click 'Edit' on a row.

Private Sub Edit_Click()
   
     DoCmd.OpenForm "Log Modal Edit", , , WhereCondition:="[ID]=" & Me!ID, WindowMode:=acDialog
     Me.Requery

End Sub

"Me.Requery" refreshes the report when the dialog window is closed.

 

[Pat Hartman]

That is insufficient. I'm surprised you think this is working. Just passing in the FK to the modal form is not enough, You need code to copy the FK from Me.OpenArgs to the FK field on your record. Please reread my instructions in #9 carefully as to where this code goes and why it goes there.

Also, Me.Refresh is NOT the same as Me.Requery. It will show updates to records that were previously read but it will NOT show any record that was created after the recordset was initially created. When your main form opens, a recordset is opened in memory for the main form and also for the subform. When you open the modal form a separate recordset is created which creates a NEW record in the underlying table when the form closes. That new record is NOT in the recordset the mainform's subform is bound to and so the new record will not show. To show the new record, you MUST rerun the subform's query to fetch the new record from the table..

 

[Sheridan]

That is insufficient. I'm surprised you think this is working. Just passing in the FK to the modal form is not enough, You need code to copy the FK from Me.OpenArgs to the FK field on your record. Please reread my instructions in #9 carefully as to where this code goes and why it goes there.

Also, Me.Refresh is NOT the same as Me.Requery. It will show updates to records that were previously read but it will NOT show any record that was created after the recordset was initially created. When your main form opens, a recordset is opened in memory for the main form and also for the subform. When you open the modal form a separate recordset is created which creates a NEW record in the underlying table when the form closes. That new record is NOT in the recordset the mainform's subform is bound to and so the new record will not show. To show the new record, you MUST rerun the subform's query to fetch the new record from the table..

Well, it's definitely working somehow. The correct FK is saved to the child record every time. I've tested it dozens of times from different parent records. I had before set the FK form field's default value to "=[Forms]![Members Form]![ID]". Could it be getting the FK ID from the open args that way? I don't know how else it's retrieving it. Anyway, your BeforeInsert suggestion is better as it would allow the subform to be called from other forms I think so I'll try it next. (I've been in such a rush to finish this that I overlooked your BeforeInsert advice. )

I tried Me.Requery at first. The parent form uses a combobox for member record navigation. Me.Requery was taking the page back to the first member record, requiring you to use the combobox to navigate back to the record you were on. I switched to Me.Refresh and it stopped that. It still makes the logs subreport reload so I can see the new log record I created instantly upon refresh. Maybe it's because I'm using a subreport, and not a subform to show to the child log records that the Me.Refresh seems to work here?