Solved

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

Posted on 2016-10-03
5
23 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

772 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

9 Experts available now in Live!

Get 1:1 Help Now