Passing value from a closing form to another open form (1 Viewer)

[elguapo]

Hi all

long time lurker now needing some help! Firstly I have to say thanks for all the help I've received but not asked for but now I find I need to ask a question and hope someone can help.

I have created a database that uses a menu system and pop up forms and all is working nicely, now I am trying to enhance the experience by enabling new records to be created that requires other forms to open that previously required you to return to the menu.

This is working nicely using global variables and conditions so forms know if they were opened from the main menu or from a form. All works appart from one scenario.

I have a form that has a linked subform:

form = risk
subform = exception

for the subform to have data it has to link to the risk, I do this by the risk ID field. This works

Waht I now have is a button on the risk form to enable a user to create a new exception and automatically link the two together. This works nicely but requires the risk form to close and reopen for the data to all link.

What I do is this:

user clicks new exception
a global variable records the current risk ID and passes this to the new open exception form
the exception is created and passes the exception ID back to a combo box on the risk form
this combo box records a value into the table exceptions

What my problem is is this, if I use the combo box directly it updates the record in the table and the subform maps to the new link

when I pass the value to the combo box from the new exception form it updates the combo box when it closes, but the combo box does not record the value until the form is closed or record is changed

so the simple question is this, how do I get the combo box to update/save its new value from the close event of the exceptions form?

I have the combo box requery to update its list so it can see the new exception so that works but how do I get the form to save the updated value when passed from a vba macro?

 

[elguapo]

Sorry some more background info

There are actually 3 forms in reality


form 1 = Risk form
form 2 = exception subform
form 3 = exception pop up form if a new exception is created

Form 1 has a combo box which records the exception ID, if an exception already exists the form displays the exception subform so the full linked data can be seen

When form 3 is opened a new exception is raised, when the form is closed it passes the new exception ID back to the combo box on the risk form. This works but the combo box despite showing the value does not update the table as I guess it doesnt realise its been updated.

Thats the bit I need to address

 

[elguapo]

I think I have found what I needed to do, I have used an SQL update to update the field where certain conditions are met and it is getting closer.

How do I get the sql update to be saved without prompting or when I close the form to say records have been changed by someone would I like to paste to clipboard?

 

[vbaInet]

You shouldn't need to use global variables. Use an unbound form for the pop up exceptions form and then (as you've figured out), and when it comes to saving the new exception execute an SQL query in code to update the table. Then, Requery the combo box in the Risk form. You're getting the "save to clipboard" error because you've got two open forms bound to the same table.