none
Can I use BrowseTo from a report to show a subform in another form?

    Question

  • I've searched and can't find an answer that can help me solve my path syntax.

    I have a form (frmMain) from which users do all their work. It only has a Navigation control with 3 tabs, each with a single subform. The third tab creates a report. On that report, I've added a cmd button that is supposed to navigate back to the first tab and display the record displayed in the report. Here is what I have in the button's click event.

    Private Sub cmdOpenWR_Click()
        DoCmd.BrowseTo acBrowseToForm, "[Forms]![frmWorkRequests]", "[Forms]![frmMain].NavigationSubform", "[Work_Request]=" & Me.Work_Request
    End Sub

    I've tried several versions to specify the form and path, but always get the same error. I also, tried setting focus to frmMain immediately before this line. "frmWorkRequests is the subform that normally is displayed on the first tab. I'm not trying to change what subform is displayed, just navigate to it from the report which is opened separately apart from frmMain. NavigationSubform is the name of the subform control on frmMain. [Work Request] is a field in the report. That value is getting built properly at run time. I just can't get it to refer to the other form.

    Thanks

    Ed

    • Edited by Ed57gmc Wednesday, December 12, 2012 7:57 PM
    Wednesday, December 12, 2012 7:53 PM

Answers

  • Hi Ed,

    Let's give it another try - please add this code to your report:

    Option Compare Database

    Dim frm As Form_frmMain

    Private Sub cmdOpenWR_Click()
        DoCmd.Close acForm, "frmMain"   ' to make sure that only one instance is open
        Set frm = New Form_frmMain
        DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
        ObjectName:="frmWorkrequests", _
        PathToSubformControl:="frmMain.NavigationSubform", _
        WhereCondition:="[Work_Request]='" & Me.Work_Request & "'", _
        Page:="", _
        DataMode:=acFormEdit

        frm.Visible = True

    End Sub

    Nadia

    • Marked as answer by Ed57gmc Thursday, December 20, 2012 4:05 PM
    Tuesday, December 18, 2012 2:12 AM
  • I just noticed that you don't even need the frm variable. This is the simplest version.

    Option Compare Database
    
    Private Sub cmdOpenWR_Click()
        'need to close/reopen so that frmMain is the active form.
        DoCmd.Close acForm, "frmMain"
        DoCmd.OpenForm "frmMain"
    
        DoCmd.BrowseTo acBrowseToForm, "frmWorkRequest", "frmMain.NavigationSubform", "[Work Request]='" & Me.Work_Request.Value & "'"
    
    End Sub
    


    Ed

    • Proposed as answer by Dummy yoyoModerator Thursday, December 20, 2012 6:17 AM
    • Marked as answer by Ed57gmc Thursday, December 20, 2012 4:06 PM
    Wednesday, December 19, 2012 5:05 PM

All replies

  • Hello,

    Try this:

    DoCmd.BrowseTo acBrowseToForm, "frmWorkRequests", "frmMain.NavigationSubform", "[Work_Request]=" & Me.Work_Request

    Nadia

    Wednesday, December 12, 2012 11:25 PM
  • Thanks, but I already tried that. I also tried "Form_frmWorkRequests", "Form_frmMain.NavigationSubform".

    Ed



    • Edited by Ed57gmc Thursday, December 13, 2012 12:17 AM
    Thursday, December 13, 2012 12:16 AM
  • "Form_frmWorkRequests" is incorrect.

    I have tested the statement:

    DoCmd.BrowseTo acBrowseToForm, "frmWorkRequests", "frmMain.NavigationSubform", "[Work_Request]=" & Me.Work_Request

    and it works, provided that Me.Work_Request is numeric. If it is text, then the statement shall include single quotes around the Me.Work_Request :

    DoCmd.BrowseTo acBrowseToForm, "frmWorkRequests", "frmMain.NavigationSubform", "[Work_Request]='" & Me.Work_Request & "'"

    What error are you getting?

    Nadia

    Thursday, December 13, 2012 2:13 AM
  • Here's what I have now:

    DoCmd.BrowseTo acBrowseToForm, "frmWorkRequest", "frmMain.NavigationSubform", "[Work_Request]='" & Me.Work_Request & "'"

    I still get error 6054. "The macro action BrowseTo requires a valid Path argument. The Path argument is of the form: MainForm1.SubForm1>Form1.SubForm1"

    I can leave off the filter argument and the error is the same. Note that I misspelled the target form. It is actually frmWorkRequest. I even tried creating a new main form using the wizard, but I still get the error. Its like it won't let me run the macro from the report. Is there another way to code the effect of the BrowseTo action? I started to work on that method, but couldn't find a way to activate a tab and make it current. From there I should be able to load a subform.

    Thanks again.


    Ed

    Thursday, December 13, 2012 5:09 PM
  • Hello,

    May be the reason is that your report opens apart from the main navigation form.

    Try adding this report to a tab (as a subform)  on the main form and see if it works.

    Nadia

    Thursday, December 13, 2012 8:05 PM
  • Yes, then it will navigate to frmWorkRequest. However, the filter doesn't get applied. It stays on the first record. This blog mentions this:"The first form in the path must be the form that is currently loaded directly in the Access window (or browser window if the application is running on the web.) This means that the Path parameter only allows you to change the contents of a currently-visible subform control."

    Either there must be a way to make the form visible before running the code, or there needs to be a way to navigate via code instead of the BrowseTo macro.


    Ed

    Thursday, December 13, 2012 10:42 PM
  • Is Work_Request numeric or text? What is the actual value that you want to find? Do you have that value in the frmWorkRequests rowsource?
    Thursday, December 13, 2012 11:27 PM
  • Thank you Nadia, but I didn't bother looking into that issue because that is secondary to getting the BrowseTo macro to work.

    Ed

    Friday, December 14, 2012 12:24 AM
  • "[Work_Request]='" & Me.Work_Request & "'" is part of your statement that does not work for you  - that's why I asked the questions.
    Friday, December 14, 2012 12:38 AM
  • True, but it is an optional argument and doesn't affect the main function's limitation.

    Ed

    Friday, December 14, 2012 12:41 AM
  • Yes, then it will navigate to frmWorkRequest. However, the filter doesn't get applied. It stays on the first record. This blog mentions this:"The first form in the path must be the form that is currently loaded directly in the Access window (or browser window if the application is running on the web.) This means that the Path parameter only allows you to change the contents of a currently-visible subform control."

    Either there must be a way to make the form visible before running the code, or there needs to be a way to navigate via code instead of the BrowseTo macro.


    Ed


    Hi Ed,

    So, when the report belongs to the Main form - it works, but you had a problem with filter and it is probably due to the error in the Where clause.

    If you want to use stand-alone report - then the BrowseTo method would not apply - you have to apply filter to the frmWork_Request as a subform of the Main.

    Saturday, December 15, 2012 10:46 PM
  • If you want to use stand-alone report - then the BrowseTo method would not apply - you have to apply filter to the frmWork_Request as a subform of the Main.

    Yes, I'm trying to find a way to do that, but there doesn't seem to be a way in vba to navigate to the right form. The navigation control and buttons don't have any navigation methods. The NavigationControl.Controls.Selected and Subform.Form properties are read-only. Unless NET has something available, I guess I'm out of luck.

    Ed

    Monday, December 17, 2012 4:30 PM
  • Hi Ed,

    Let's give it another try - please add this code to your report:

    Option Compare Database

    Dim frm As Form_frmMain

    Private Sub cmdOpenWR_Click()
        DoCmd.Close acForm, "frmMain"   ' to make sure that only one instance is open
        Set frm = New Form_frmMain
        DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
        ObjectName:="frmWorkrequests", _
        PathToSubformControl:="frmMain.NavigationSubform", _
        WhereCondition:="[Work_Request]='" & Me.Work_Request & "'", _
        Page:="", _
        DataMode:=acFormEdit

        frm.Visible = True

    End Sub

    Nadia

    • Marked as answer by Ed57gmc Thursday, December 20, 2012 4:05 PM
    Tuesday, December 18, 2012 2:12 AM
  • Hi Ed,

    Let's give it another try - please add this code to your report:

    Option Compare Database

    Dim frm As Form_frmMain

    Private Sub cmdOpenWR_Click()
        DoCmd.Close acForm, "frmMain"   ' to make sure that only one instance is open
        Set frm = New Form_frmMain
        DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
        ObjectName:="frmWorkrequests", _
        PathToSubformControl:="frmMain.NavigationSubform", _
        WhereCondition:="[Work_Request]='" & Me.Work_Request & "'", _
        Page:="", _
        DataMode:=acFormEdit

        frm.Visible = True

    End Sub

    Nadia

    Tuesday, December 18, 2012 2:12 AM

  • Hi Ed,

    Let's give it another try - please add this code to your report:

    Option Compare Database

    Dim frm As Form_frmMain

    Private Sub cmdOpenWR_Click()
        DoCmd.Close acForm, "frmMain"   ' to make sure that only one instance is open
        Set frm = New Form_frmMain
        DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
        ObjectName:="frmWorkrequests", _
        PathToSubformControl:="frmMain.NavigationSubform", _
        WhereCondition:="[Work_Request]='" & Me.Work_Request & "'", _
        Page:="", _
        DataMode:=acFormEdit

        frm.Visible = True

    End Sub

     

    Nadia

    Tuesday, December 18, 2012 2:13 AM
  • Hi Ed,

    Welcome to the MSDN forum.

    How is it going with the problem based on Nadia's suggestion?

    You may check this link as well:

    Access 2010 BrowseTo DoCmd and macro action
    http://blogs.office.com/b/microsoft-access/archive/2010/02/23/access-2010-browseto-docmd-and-macro-action.aspx

    Have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, December 19, 2012 6:53 AM
  • Sorry it took me a while to get back. We were doing an upgrade of Outlook.

    The strategy of closing the form and then reopening it does work. However, I made a couple of changes to the code.

    This version sets the report level var to private.

    Option Compare Database
    
    Private frm As Form_frmMain
    
    Private Sub cmdOpenWR_Click()
        'need to close/reopen so that frmMain is the active form.
        DoCmd.Close acForm, "frmMain"
        Set frm = New Form_frmMain
        
        DoCmd.BrowseTo acBrowseToForm, "frmWorkRequest", "frmMain.NavigationSubform", "[Work Request]='" & Me.Work_Request.Value & "'"
        frm.Visible = True
    End Sub

    This version limits the scope of the frm var to the procedure. But you have to use DoCmd because the var goes out of scope and the frmMain closes if you don't. Also, setting the frm.Visible is not necessary.

    Option Compare Database
    
    Private Sub cmdOpenWR_Click()
        'need to close/reopen so that frmMain is the active form.
        DoCmd.Close acForm, "frmMain"
        DoCmd.OpenForm "frmMain"
        Set frm = New Form_frmMain
        
        DoCmd.BrowseTo acBrowseToForm, "frmWorkRequest", "frmMain.NavigationSubform", "[Work Request]='" & Me.Work_Request.Value & "'"
    
    End Sub

    BTW, the reason the filter didn't work earlier was due to a copy/paste error. In the where expression, I had used "[Work_Request]=". I copied the name from another place in code, but the underscore isn't valid in this context.

    I appreciate you sticking with this subject.


    Ed

    • Edited by Ed57gmc Wednesday, December 19, 2012 5:03 PM
    • Proposed as answer by Dummy yoyoModerator Thursday, December 20, 2012 6:17 AM
    Wednesday, December 19, 2012 4:52 PM
  • I just noticed that you don't even need the frm variable. This is the simplest version.

    Option Compare Database
    
    Private Sub cmdOpenWR_Click()
        'need to close/reopen so that frmMain is the active form.
        DoCmd.Close acForm, "frmMain"
        DoCmd.OpenForm "frmMain"
    
        DoCmd.BrowseTo acBrowseToForm, "frmWorkRequest", "frmMain.NavigationSubform", "[Work Request]='" & Me.Work_Request.Value & "'"
    
    End Sub
    


    Ed

    • Proposed as answer by Dummy yoyoModerator Thursday, December 20, 2012 6:17 AM
    • Marked as answer by Ed57gmc Thursday, December 20, 2012 4:06 PM
    Wednesday, December 19, 2012 5:05 PM
  • Hi Ed,

    So, looks like the statement that I suggested on Dec13th was correct and you indeed had a problem with the filter.

    Another solution with using frm variable also works, I have tested it - so I am puzzled why it did not work for you.

    Nadia

    Wednesday, December 19, 2012 8:44 PM
  • Another solution with using frm variable also works, I have tested it - so I am puzzled why it did not work for you.

    Nadia

    It worked. I didn't say that it did not work. I just submitted some variations for the benefit of others who might read this.

    Ed

    Thursday, December 20, 2012 1:54 AM