Need to update a field on form1 when a field on form2 changes.

Posted on 2011-09-11
Medium Priority
Last Modified: 2012-06-27

I have a form (form 1) that includes a field called status.
When I open form 1 a text box names StatusMsg is set to either OPEN, IN-PROGRESS, or CLOSED
The code is executed in the ON Open Event.

I use another form (Form 2) to change the status.
However, when the status is changed from form 2, the new status will not show up in form 1 unless I close form 1 and then reopen it.

I've inserted both of the below lines into my code for form 2, however, however neither works.


As always, your help is appreciated.

Question by:pcalabria
  • 2
  • 2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36520503
in form2, place the codes

LVL 77

Expert Comment

ID: 36521296
I think we need a lot more info in order to answer this superficially simple question.

You say..
"When I open form 1 a text box names StatusMsg is set to either OPEN, IN-PROGRESS, or CLOSED
The code is executed in the ON Open Event."

If you are actually doing this it means that the Status field is not bound to any field in a table, otherwise you would not be able to use the Open event to set its value.  You would have to wait at least until the Form_load event.

SO that raises a question about the entire structure of form1 - is it a bound form or unbound form?

And why do you need to go to another form to change the value in Status, when there are only 3 fixed options- that immediately suggests the use of a combobox instead of a textbox.

If it turns out that these ARE bound forms then there are issues of what have you saved at each step - you cannot have two form altering the same table unless you ensure you are saving the record each time you move between forms.

Are form1 and form2 using the same table in their recordsource?

If so then

You need to be sure you have saved the record in form2

Author Comment

ID: 36527078
Thank you both, I am starting to gain a better understanding of how this stuff works, and your responses have already helped!

Form 1 is bound to a specific table, although the field that I am changing is unbound.  StatusMsg refers to the status field of another table.  Its' just a message to let the user know what's going on.  The user may open another form concurrently, and then take some action that causes the status message in form 1 to be no longer be accurate.  That's the problem.

I think what's going on here is now more clear to me, although the best solution is still unclear.  Form 1 runs code in the OnOpen even that determines what message to display in the unbound StatusMsg field.

Next, I allow the user to open another form, and take some action that changes the status.   The message in form 1 does not update because the code has already run.

After reading your response, I suspect the reason requery does not work because it is an unbound field and that my solution would be to run the same code and update the field again.

If this is true, my options would be to duplicate the code now in form 1 in form 2 and then execute the code as part of the exit routine of form 2.... but I hate this approach... I hate having two routines that do the same things, especially since one would use me. syntax and the other forms!.

Of course, I can put the code into a module but that also means rewritting the code to use forms!formname!fieldname syntax instead of me. syntax.

I'd rather create a subroutine and then call it at the appropriate time from both forms than duplicate the code, I was just hoping there was a simpler solution.  

Is there a way that I can cause the code in the OnOpen event of form 1 to run from a routine in Form 2 as if it were a public subroutine?  That might work.

I actually run into this problem often, so for me this is still important because I'll implement the solution many times.

LVL 77

Accepted Solution

peter57r earned 2000 total points
ID: 36527573
You can change a private sub to a public sub just by changing the word.
You can then call it using full Forms!formname.subname syntax (provided the form is open)

Author Closing Comment

ID: 36555930

I can't believe that was so easy!


Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question