?
Solved

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

Posted on 2016-10-03
5
Medium Priority
?
36 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 20

Assisted Solution

by:Russ Suter
Russ Suter earned 1000 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses
Course of the Month10 days, 4 hours left to enroll

762 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