Solved

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

Posted on 2011-09-11
5
238 Views
Last Modified: 2012-06-27
Hello,

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.

Forms!updatecomponents.SVRmsg.refresh
Forms!updatecomponents.refresh

As always, your help is appreciated.

0
Comment
Question by:pcalabria
  • 2
  • 2
5 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
in form2, place the codes

Forms!NameOfForm1.requery
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
0
 

Author Comment

by:pcalabria
Comment Utility
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.

Thansk,
PC
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
Comment Utility
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)
0
 

Author Closing Comment

by:pcalabria
Comment Utility
Thanks,

I can't believe that was so easy!

PC
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now