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

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?
BugHuggerNoobAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
BugHuggerConnect With a Mentor NoobAuthor Commented:
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
 
Russ SuterConnect With a Mentor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
BugHuggerNoobAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.