Subform needs to be refreshed from Modal form after update.

[Phred]

I have a parent form called Property. Contains details about a property.



I have a second parent form called Entity. Contains details about a person.

I have a Join form that connects People to Property. This form is a SubForm to the Property form. You can select a property on the Parent Property form and then attach people to the Property. So one property can have many people joined to it.

The join form is a continuous form. I wanted to place two cascading combo boxes on the Join form but they don't work well in continuous forms. So I moved the cascading combo boxes to a modal form and they work fine. So you click a button on the Join Form screen and it opens the Modal screen with the combo boxes. You select attributes about the person that are specific to the Property. Form example

Combo Box 1 "EntityType" options include choices like:
Attorney
Client
Co Owner Non Active
Owner of record

Combo Box 2 "Relationship Type includes choices like:

Partner
Spouse
Domestic Partner
Trust Beneficiary

After you select your option, lets say Client, the Combo Box has an after update event that executes a SQL Update query and writes the choice to the proper record in the underlying table for the Join Form.

Then you select Relationship Type from the second combo box and after update it runs an identical UPDATE SQL statement and also writes the choice to the proper record in the underlying table for the Join Form.

All of this works fine. Modal Form fine, Cascading Combo Boxes work fine. It writes to the underlying table fine.

Here is my problem. The code writes the choices to the underlying table but I can't get the SubForm to refresh showing the new choices that are now written in the table. The only way I can get the subform to refresh is to close the Modal form and then press F5 to manually refresh. My understanding is that you cannot refresh anything until the Modal form is closed.

I have tried many different ways to no avail. My problem is to get the SubForm to automatically refresh immediately after the choice has been made or after the Modal form is closed. I simply cannot find an automated method to refresh the SubForm with the new data while the Modal form is open. I could close the Modal but I am not sure where to place a me.refresh command that fires after the Modal Form is closed.

I am not including code as all the code runs fine. I am not including Cascading Combo boxes because they work fine. It is only the refresh methodology I need to come up with.

I am attaching some screen snap shots that show the forms.

This snap shows the main form Property with its subform Joined Entities.



When you click on the Pick Type button it opens the modal form Select Relationship type.



When you pick a selection it is immediately written to the underlying fields in the Join Table but it does not refresh to the Sub Form.

I hope this is enough information. It would be a tremendous amount of work to explain everything leading up to where the refresh needs to happen since all that code works fine. I cannot post the database as the back end is SQL Server.

Thanks,

Phred

[pbaldy]

If you are using acDialog to open the form, immediately after the line opening the form put

Me.Requery

or

Me.Refresh

That line won't execute until the form opened with acDialog is closed.

[Phred]

Paul:

I can't find any information on acDialog what it is, how it functions, correct syntax. Can you point me to anything?

I tried placing it in my code in the same position of my other docmd.openform. It seems to stop my code dead in the water.

Private Sub BtnSelTyp_Click()

Dim intAttID As Integer  'used to hold the primary key of the dbo_PropertyEntity table.

intAttID = Me.AttachedID 

DoCmd.SelectObject acForm, "FrmSelTyp", True

Forms!FrmSelTyp.AttID = intAttID              'Grabs Primary Key from Sub Form FrmSelTyp

If Me.CBOSelectEntity = "" Or Null Then                    
Call MsgBox("The Entity Name field is empty." _
& vbCrLf & "" _
& vbCrLf & "You must select an Entity Name before selecting an Entity Type or Entity Relationship." _
vbCritical, "Entity Name Not Selected.")
                       
Exit Sub

End If

DoCmd.OpenForm "FrmSelTyp", , , , , acDialog

Me.Refresh  'Code seems to stop here.

Call MsgBox("You must select the Entity Type AND Entity Relationship." _
            & vbCrLf & "" _
            & vbCrLf & "If you do not select both, your changes will be discarded." _
            , vbInformation, "Entity and Relationship Type")

DoCmd.SelectObject acForm, "FrmSelTyp", True

Forms!FrmSelTyp.AttID = intAttID

End Sub

[Phred]

I found information on acDialog it seems it loads a non-modal form as a modal form. But the form is already modal. It seems that stopping the code from running is normal for the acDialog.

I have some code that needs to run from the click of the BTN, and then open the form. After the selections are made in the Combo boxes and updated to the table via SQL Update, close and then refresh the child form. I have tried placing code to refresh the child form in various events of the modal form with no luck.

Is it possible that I have the acDialog in the wrong place? Is it possible to place a refresh command somewhere from outside of the modal form that detects when the form is no longer open and refreshes the subform?

Thanks
Fred

[pbaldy]

It looks like you have it in the right place. I would expect the refresh line to run after FrmSelTyp is closed or hidden. Does it work if you take the modal properties off the form and just use acDialog?

[Phred]

I took the modal properties off, Pop Up=No, Modal=No.

It didn't seem to make any difference.

The code stops running on the third line: Forms!FrmSelTyp.AttID = IntAttID. With the error: 2450 Can't find the form 'FrmSelTyp' referred to in Visual Basic Code.

[pbaldy]

You're trying to push a value to the modal form? Because you open it modal with acDialog, that line isn't going to run until it's closed or hidden. If you want to open it and send a value to it, you'll probably need to use OpenArgs.

[Phred]

Yes I am pushing the PK of the record you are adding to from the SubForm to the Modal form. When you click on the Combo box the After Update Event VB grabs the PK from the form and incorporates it into the SQL Update query so that the correct record is updated. I never used OpenArgs before but will look it up. I'd like to keep this post open as I am sure I will have questions on it.

Thanks Fred

[pbaldy]

No problem, post back if you get stuck.

[Phred]

I can find lots of minutia about OpenArgs but little overview or basics on what it does and how to use it.

Here is my best guess on what you are suggesting:

DoCmd.OpenForm "FrmSelTyp", , , , acAdd, acDialog,_NewData & ";" & Me.FrmSelTyp.attachedID

If so, how does the AttachedID get placed into the variable intAttID? and where does the me.refresh go?

Am I even close?

Thanks Fred

[pbaldy]

More like:

DoCmd.OpenForm "FrmSelTyp", , , , acAdd, acDialog, intAttID

which passes the value in intAttID, and then in the open or load event of the form being opened,

Me.Whatever = Me.OpenArgs

which places that value where you want it.

[Phred]

Holy Guacamole, it worked! Shazam.

I would never have come up with this. Now I gotta figure out what I did.

You folks who answer these posts so diligently are a godsend.

Thanks so much,

Fred

[pbaldy]

Happy to help Fred!