Solved

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

Posted on 2011-09-11
5
241 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
ID: 36520503
in form2, place the codes

Forms!NameOfForm1.requery
0
 
LVL 77

Expert Comment

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

Author Comment

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

Thansk,
PC
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 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)
0
 

Author Closing Comment

by:pcalabria
ID: 36555930
Thanks,

I can't believe that was so easy!

PC
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 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

24 Experts available now in Live!

Get 1:1 Help Now