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

 

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

 

[Pat Hartman]

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?

That is why the FK is getting populated. I don't recommend this method because it unnecessarily ties the two forms together. If you ever want to call the popup form from a different form the code will fail.

I don't use this method at all but not because it is wrong. I don't use it because it is less obvious and therefore harder for a new developer to discover.

If you want to continue to use this method, you should remove the hard coupling between these two forms. To do that remove the expression from the unbound control and add one line of code to the form's Open event.

Me.TheFKField.DefaultValue = Me.OpenArgs

Using this technique, the two forms are no longer bound by name. The necessary ID value is passed from the calling form using the OpenArgs and you set the default value in real time as the form opens.

PS naming all the PK's ID is seriously poor practice. It makes it very difficult for others looking at tables to identify relationships without opening the relationships diagram. Either use the identical PK name as the FK name OR use the PKName_FK using "_FK" as a suffix. The suffix method absolutely clarifies which columns are FK's.

 

[Sheridan]

PS naming all the PK's ID is seriously poor practice. It makes it very difficult for others looking at tables to identify relationships without opening the relationships diagram. Either use the identical PK name as the FK name OR use the PKName_FK using "_FK" as a suffix. The suffix method absolutely clarifies which columns are FK's.

Yeah, I've been meaning to fix that but at this point I already have dozens of queries and forms. Will Access update all the field references automatically?

My background comes from Salesforce. In Salesforce all the tables' primary keys by default are labeled 'Id' and can't be changed; at first I continued that practice in this Access project just because it felt familiar to me.

 

[Pat Hartman]

Yeah, I've been meaning to fix that but at this point I already have dozens of queries and forms.

And next week, you'll have more. Access has built in tools that will help with some things but not all and once you've split the database into FE and BE, they are not useful at all for this particular task since changing a column name on a table in the BE has no impact on anything in the FE. You'll just get errors as the name is not found.

There are various tools out there that you can use to add in to Access that will perform find & replace and they all work pretty well. But given that all the PK's are "ID", you wouldn't have any way of specifying which "ID" you wanted to change so this might have to be a manual effort.

You may end up having to do a combination of things.

First, delete all the links from your FE and reimport the tables temporarily. Use the Change Auto Corrupt (sic) feature to change the column names. Make sure to check ALL THREE boxes. You want to be able to see the log of changes that were made. I'm going to attach a document regarding how CHC actually works so you will understand the why and how of these instructions. I'll also include a PP I made which is a summary of the report.. You might get the gist of what is going on from that or you might need the details in the MS document. At this point, I would open and save every single query in your database to ensure that the change got propagated to the queries. Then go through and open and save every form/report/macro you have. Obviously, fix any errors that pop up. The problem with Change Auto Correct is that nothing is propagated until an object that uses the changed object is opened. It is only at that point that Access realizes something is amiss. Essentially, EVERY object has a unique internal name and that is what Name Auto Correct uses to propagate changes but never loose sight of the fact that the change does NOT propagate until some affected object is opened, hence the "Corrupt" comment. If you don't ensure that everything that could have potentially changed gets processed, you may end up months down the road with some strange breakage because something you haven't used in forever has now required updating.

Second, test everything logically to see if you can find disconnects. You know your apps and so you can probably know intuitively which parts are likely to be affected by this.

Third, and now the timing gets tricky. If the app is already in production with production data, you have both the FE and the BE to update. Updating a BE, especially with this many changes is not something you should ever do while any user is potentially logged in and working so unless I'm doing this in the dead of night, I actually rename the live production BE so that if anyone logs in, they will get an error. Then I make all the column name changes and hope I don't make any typos to make them not identical with the test data I have been working with.

Fourth, delete the embedded tables from the modified FE and link to the renamed copy of the BE.

Fifth, test again, and I mean everything!!!

Sixth, If you're happy with everything, rename the BE or leave it as the new name, which is what I do. I version my BE database name for just this purpose since you don't want someone using an old FE to ever be able to get in and make changes to the updated BE.

Final step is to deliver the new FE and let everyone back in.

This process has not changed any embedded SQL strings and If your testing didn't identify any errors that this might have generated, hopefully, there aren't any. But somehow you probably need to search all your macros and VBA for "ID" which will be near to impossible.

Good Luck