Solved

How to Reload/Refresh an Open Form when it's Selected

Posted on 2016-10-03
5
31 Views
Last Modified: 2016-10-08
I have a form that has a lot of Form_Load() vba that alters which buttons are visible, etc.

The problem is, this form is fed from another form.  So when information is changed in the other form, I need this first form to update what's visible, etc.

Example:  

The ORDERS form is open
a dropdown on the ORDERS form is selected for a [WorkOrder]
The DETAILS form is opened, and shows buttons, labels, etc based on the [WorkOrder] selected

With both forms open, you can change the [WorkOrder] selected, but the DETAILS form won't update unless it's closed and opened again.

I've tried Got Focus

Private Sub Form_GotFocus()

'Make sure the form is up to date depending on Sample Login Form data
Me.Requery
Me.Refresh
Me.Repaint
End Sub

Open in new window


I've tried on Click
Private Sub Form_Click()

'Make sure the form is up to date depending on Sample Login Form data
Me.Requery
Me.Refresh
Me.Repaint

End Sub

Open in new window


I've tried being more specific about which form should refresh
Form!DETAILS.requery

Open in new window


I've even tried taking some of the Form_Load() logic and just put it directly into the on click and got focus subs  - but it still does nothing.  The form remains unchanged unless it's closed and opened again.

What am I missing here?
0
Comment
Question by:BugHugger
5 Comments
 
LVL 20

Assisted Solution

by:Russ Suter
Russ Suter earned 250 total points
ID: 41826796
If you have one form that is dependent on another you should probably be opening it modally. Allowing changes to the parent form when the form is dependent on it isn't a good idea. However, if you must do this you can create a public sub on the child form that sets all the data and just call it from the parent form whenever one of the relevant controls receives a new value. I would consider this a hack. The proper design approach is to open the form modally.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 41826817
Also - why not use a Form-Subform relationship instead of two separate forms? If you do that, Access will manage the Details records for you, and if you change the Order form in the parent section, Access will load the Details for that Order.

If you simply must use two distinct forms, then you should be able to use the Current event of the Orders form to manage your Details form. You would first determine if that Form is open, and if so, you'd use this type of syntax to manage it:

Forms!YourDetailsForm.SomeControlOrProperty
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41826820
I you have the forms set up correctly you should not need to refresh or requery.  When the WorkOrder changes on the main form from a combo box you would move the record pointer to the WorkOrder and the detail transactions on a sub-form would reflect accordingly.  A basic parent/child relationship between the main form and sub-form would resolve your issue.

ET
0
 

Accepted Solution

by:
BugHugger earned 0 total points
ID: 41826989
Hey Everyone,

It looks like I left out some important information in my original question.

The main form - the ORDERS form - is part of the built-in software we purchased from the vendor.  I can't (shouldn't) change it because it would mean we'd lose the tech support contract we have with them.  The only thing I can do is create a second form that has the additional functionality we want.

I ended up solving my own problem with this:

The second form - the DETAILS form - is just a series of buttons that load other forms and functions.  Having the buttons toggle visibility was important because I wanted to artificially limit when the built-in functions of the software are available.  

All buttons are set to default Visible = FALSE.  The form load VBA looks at the ORDERS form to see what stage the selected [WorkOrder] is in and toggle the visibility of the buttons based on that.

If Forms![Orders]![Orders_Subform].Form![WorkOrder] = 0 Then
    CopyBtn.Visible = True
    CancBtn.Visible = True
End If

Open in new window


Then the buttons were all built with extra on-click code which looked to see if the current [WorkOrder] was still in that correct stage.  If it wasn't - if the button clicked should not be visible at this stage - the DETAILS form closed and immediately reopened with a small message box telling the User that the form was refreshed.

'Is it in prelog?
If Forms![Orders]![Orders_Subform].Form![WorkOrder] = -1 Then
    DoCmd.Close acForm, "DETAILS", acSaveNo
    DoCmd.OpenForm "DETAILS"
    MsgBox "Form Updated"
    Exit Sub
End If

Open in new window

0
 

Author Closing Comment

by:BugHugger
ID: 41834915
This was a sloppy work around.  But the situation I was in was complicated and at least it worked.  There are ways to 'game the system' with my solution, but I won't let perfection be the enemy of good.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

820 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