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:
Code:
Private frm as Form_frmListTemplate
Then, in the NavigationButton_Click event, I added the following code:
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!