Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-10-03
5
Medium Priority
?
38 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

650 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