Subform not matching Form from vba open event (1 Viewer)

BJF

Registered User.
Local time
Today, 05:05
Joined
Feb 19, 2010
Messages
133
Hello,
I’m having a problem when I put the following code on a DblClick event of a form that I open from my sales order form which organizes info for me:

DoCmd.OpenForm "frmSalesOrders", acNormal, , "[SO]=" & Me![SO]

It opens the form (actually the form is never closed because i am coming from the same form just on a different tab) and to the correct record, however the subform of that form is not matched to it.
Is there additional code that I can add to this that will remind the subform to open to the new Sales order I choose?

The subform is named: fsubProductNew and the master and child link field is [SO]

One more explanation scenario – I am starting off by opening my sales order form (which contains a subform and whichever record i move to the subform follows fine)

If the form opens to sales order #5 then the subform matches to sales order #5

Next I open a form from that form that lets me search sales orders customers and organize some other info for reporting.

On that form I would like the ability to double click the SO(sales order) field which would take me back to the original Sales order record, however when I do this with the code I posted above, the subform remains on whichever record the form originally opened to while the main form has gone to the Sales order selection that I just double clicked, so my code is doing half of what i want.

Thanks for any suggestions,
Brian
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:05
Joined
May 7, 2009
Messages
19,170
check first if the form is open and set the filter:

Code:
If Syscmd((acSysCmdGetObjectState, acform, "frmSalesOrders") <> 0 Then
    With Forms("frmSalesOrders").Form
        .Filter = "[SO]=" & Me![SO]
        .FilterOn = True
    End With
    Forms("frmSalesOrders").SetFocus

Else
   DoCmd.OpenForm "frmSalesOrders", acNormal, , "[SO]=" & Me![SO]

End If
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:05
Joined
Oct 29, 2018
Messages
21,358
Hi Brian. I didn't read your entire post, and Arnel may already have a solution for you. I just wanted to mention I think maybe the OpenForm method doesn't really apply any of its arguments if the form is already open. I think maybe it just sets focus to it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:05
Joined
Feb 19, 2002
Messages
42,981
I found that opening an already open form using this method didn't actually work correctly so I would use theDBGuy's suggestion. Check to see if the form is loaded and close it first.

PS - having multiple forms open at one time is poor practice unless the user actually needs to see multiple forms at once. Good practice is to close a form when the user leaves it. This is one of the reasons I never use the Access tab view. I always use the windowed view although I frequently have forms with tab controls on them to keep sets of data together and easily accessible.
 

BJF

Registered User.
Local time
Today, 05:05
Joined
Feb 19, 2010
Messages
133
Thanks for the responses everyone.
I still have not gotten this to work.
I will have to look into it further.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:05
Joined
Oct 29, 2018
Messages
21,358
Thanks for the responses everyone.
I still have not gotten this to work.
I will have to look into it further.
Hi. Did you try closing the form first as Pat suggested?
 

Users who are viewing this thread

Top Bottom