DoCmd.BrowseTo statement produces strange PathToSubformControl error

[hansendl]

Background: I'm working on a project that relies on 21 different lookup tables for basic data used throughout the rest of the application. As part of my user interface, I intend to expose these lookup tables to the user through forms to allow for modification of the data. I have created a standard form template ("frmListTemplate") to display the data in each table in a list box, with controls (and vba code) that allows the user to filter and sort the data as desired, as well as to add, edit, or delete records as may be necessary. Rather than create 21 copies of the template and slightly modifying each to display the data in each of the 21 tables, I am planning to use the form template as a class module, creating an instance of the form as needed in code, and modifying the various exposed properties of the class to appropriately display the data for the selected table.



I am developing the application in Access 2010, and am using NavigationForms as the primary means to navigate the app. Because I am planning to build the lookup-table forms on the fly using the form template as a class module, I obviously can't use the NavigationTarget property of the NavigationButton. Instead, I need to add code behind the NavigationButton_Click event where I instantiate the form, set its properties, then use the DoCmd.BrowseTo command to display the form in the NavigationSubform control.

In order to test the concept, I added a simple NavigationControl group to a form ("frmTest"), which consisted of one NavigationButton and a NavigationSubform control. I declared a module level variable for my frmListTemplate form:

 Private frm as Form_frmListTemplate

Then, in the NavigationButton_Click event, I added the following code:

Private Sub NavigationButton_Click()    
Set frm = New Form_frmListTemplate
    With frm
        'set some class properties here to display the lookup table data
    End With

    DoCmd.BrowseTo acBrowseToForm, frm.Name, "frmTest.NavigationSubform"
End Sub

I saved the code and clicked the NavigationButton on the test form and received the error: "Run-time error 6054: The macro action BrowseTo requires a valid Path argument. A valid Path argument is of the form: MainForm1.Subform1>Form1.Subform1"

After verifying the path (frmTest is a top level form, i.e., it is not being opened as a subform), my first thought was that the frm.Name argument was somehow screwing it up. So I changed the ObjectName argument to refer to a form that was already built and saved as part of the project. When I saved and ran it again, I got the same error. So I began a fairly thorough search of MSDN and Google to make sure I wasn't missing something on the PathToSubform argument. The example in MSDN was for a top-level form like mine, and the path in the example looked the same as mine, so I was baffled.

Then I commented out the "Set frm = New Form_frmListTemplate" statement (for no particular reason other than the BrowseTo statement no longer referred to the frm variable), and much to my surprise the NavigationButton worked as intended!

Wha? It turns out that the "Set frm = ..." statement, when executed prior to the DoCmd.BrowseTo action, causes Access to generate a PathToSubform error when executing the BrowseTo action. I have no idea why. I was able to move the "Set frm = ..." statement to the Form_Load() event, and the BrowseTo action worked as intended. It only seems to mess up when the form is instantiated in the same procedure as the BrowseTo action. I tried instantiating other objects (Set db = CurrentDb, Set rst = db.OpenRecordset, etc.) and none of them caused the error, it was only the form.

Has anyone else experienced the same issue? Does anyone know why this error may be popping up in these conditions and/or how I might fix it?

Thanks!

[ItsMe]

I have only read up on the BrowseTo method. So I do not have production experience with it yet. I do not know if this is a good or bad thing. I have simply been avoiding it and the Navigation Control.

However, after reading your post, I sense the issue is with DoCmd. It seems that Docmd does not know what it is a member of, or rather, we do not expect it to change what it is a member of when you instantiate the Form object. It appears the instantiation of the form object moves focus, although in a hidden fashion, to the form object that is instantiated.

So maybe set focus back to the Navigation Control?
Forms!NavigationFormName.NavigationControlName.Set focus
DoCmd.BrowseTo acBrowseToForm, frm.Name, "frmTest.NavigationSubform"

or since I do not have experience with Browse to, maybe it is the button that needs focus....
Forms!NavigationFormName.NavigationControlName.Nav igationButton.Setfocus
DoCmd.BrowseTo acBrowseToForm, frm.Name, "frmTest.NavigationSubform"

[hansendl]

Thanks for the response. You pose an interesting theory. I wonder if it really is the DoCmd object, though, since I don't get the error when I instantiate other objects (e.g., db and rst), only when I instantiate a form object. And why would it give me a PathToSubform syntax error?

Probably not worth a whole lot of time investigating since I think I've found a viable work around, but I wanted to get the issue out there in case anyone else has run into it.

Thanks!

[ItsMe]

...PathToSubform syntax error...

I believe this to be a common error message. Perhaps it is not very generic, covering many possible error scenarios, but I have read about developers encountering this error often.

I will guess the error can occur if the current object does not have the named object/control in scope, therefore, it does not understand the path. The argument for the BrowseTo method is not valid because it does not find the path. It probably is looking for the path first and not looking to validate the argument you provided as being a valid Named Object within its scope. In your case the Form named frmListTemplate is not a control that has the BrowseTo method as one of its members.

As for losing focus instantiating one object vs. another object, not sure... Can only guess that since the object type is a form and the form has a .Focus property, Access assumes it is a good idea to focus on it. Whereas a recordset does not have a .Focus property.

The only other thing I can dream of is how you instantiate your object
Set frm = New Form_frmListTemplate

I have seen the use of the underscore before but I do not use it or fully understand it. At least not in this context. So I normally do this...
Dim frm as New Form
Set frm = Forms!frmListTemplate

Perhaps you can teach me something here

[hansendl]

The only other thing I can dream of is how you instantiate your object
Set frm = New Form_frmListTemplate

I have seen the use of the underscore before but I do not use it or fully understand it. At least not in this context. So I normally do this...
Dim frm as New Form
Set frm = Forms!frmListTemplate

Perhaps you can teach me something here

In the VB editor view of access, you'll notice the module names for all forms include "Form_" before the form name. These modules are in fact class modules, and can be used as you would any other class module in Access. Accordingly, I can instantiate the form as a class module in the same way I would any other class module, by setting a variable equal to a new instance of the class module. There's a very good discussion on the usefulness of this approach in Chapter 8 of "Microsoft Access 2010 Programmer's Reference." You can preview it here, and it explains the concept much clearer than I ever could!

Cheers,

[hansendl]

I believe this to be a common error message. Perhaps it is not very generic, covering many possible error scenarios, but I have read about developers encountering this error often.

You are correct that this is a very common error; but in my research, it is because most people don't understand the format required for the path argument, especially when it comes to the ">" character and subform control names. That was not my issue, however, since I know my path argument is correct. You might be right that the form instantiation line perhaps throws something out of scope, so the DoCmd object doesn't understand the argument. Seems like a bug to me!

Thanks for the response!

[ItsMe]

One thing I noticed by instantiating a form object that way is it creates a brand new instance of the Form.

In other words...

Opening an object named Form1 in form view then, using another Test Form....

Placing the following in the header
Private frm As Form_Form1

Placing the following in a click event
Set frm = New Form_Form1
frm.SetFocus

Then firing the code, creates a new form object in memory, opens the object, and then sets focus on the object. Ignoring the previous instance of Form1


verses

Dim frm As New Form
Set frm = Forms!Form1
frm.SetFocus

Simply sets focus on the Form1 object that was already open.


First approach, two forms open named Form1. Second approach, one form open named Form1.

I believe this information is cause to believe that your click event is losing focus of the original control. Click something and it has focus. Click something and then have VBA create a new instance of something else, something else may have the focus. Bring the focus back to your Navigation control or the button that initiated the click event, then your DoCmd line.

[hansendl]

I'll give that a shot. Thanks again for continuing to think about this!

[ItsMe]

It is interesting to me. Now to see if I can muster the strength to read the book you recommended. The only problem is I have at least three other books I want/need to read.