none
VBA procedures on navigation forms RRS feed

  • Question

  • Hello,

    This is my first time posting on this site so any info or guidance is appreciated. I apologize in advance for the wordiness but the issue is kind of difficult to explain. I have a navigation form that has 5 tabs, four of which open various forms and reports without issue. The fifth tabs opens a dashboard form that is comprised of 6 subforms. The dashboard form has VBA that sizes each subform height based off the number of records pulled in a record count and works just fine when opened separate from the navigation form. When the dashboard form is opened in the navigation form the dirty events that trigger the sizing on the subforms all generate errors stating that object method is not supported in access. I am able to carry out other events (such as requery) using the VBA form paths for the navigation form and subforms. but the dirty events triggered from the dashboard form error out every time. Its my understanding this has to do with navigation subform control and the fact that it does not assume the subform control name. I am at a loss if there are any workaround for this. I have been on more forums and sites than I can count and have tried numerous combinations using BrowseTo, SourceObject, etc... commands but have not been able to get any combo to work. I am not sure why some events work and others do not, for example:

    the following code will requery the daily log subform on the dashboard form after a user makes an entry using the entry form.

    Forms![Navigation Form]![NavigationSubForm]![frm_DailyLog].requery

    however the following code in the Mission form (the dash board form that contains the subforms) works just fine when opened by itself but not when opened using the tab on the navigation form.

    Forms![Navigation Form]![NavigationSubForm]![frm_Mission].Form_Dirty (intCancel)

    I have tried more combos of control paths than I can count and am not sure if this is just an inherent problem with navigation forms and control references available for the forms that are loaded while using VBA or I am just missing something.

    Again, any help is appreciated.

    • Edited by ehammer321 Tuesday, August 25, 2015 8:47 AM
    Tuesday, August 25, 2015 8:16 AM

All replies

  • I am surprised your first code sample works. Typically the syntax for sub-subforms is:

    forms!myform!mysubformcontrol.form!mysubsubformcontrol.form

    Also, if you want to call an event procedure from outside the form, make sure it is Public.


    -Tom. Microsoft Access MVP

    • Proposed as answer by André Santo Tuesday, August 25, 2015 11:41 AM
    Tuesday, August 25, 2015 9:25 AM
  • Thanks for your response, I was surprised as well. I had previously tried the syntax you provided and it works fine for the single forms that are opened by the navigations form tabs. The problem is with the dashboard form and its multiple subforms that comprise it. The dirty events for the subforms on the dashboard form are contained in public events. The triggers for the dirty events on the dashboard do not work with any combination of syntax that I have tried (which is a lot). I get the "Object does not support this property or method". These errors only occur when opened from the navigation form. Let me know if you need any additional info, any further assistance is appreciated.
    Tuesday, August 25, 2015 5:01 PM
  • I went back and tried the syntax you posted and played with it a little bit.

    The following generates "Object does not support this property or method". I made sure they were in public procedures.

    Forms![NavigationForm]![NavigationSubform].Form![frm_Mission].Form.Form_Dirty (intCancel)

    However, the same syntax with requery instead of Form_dirty works just fine.

    Forms![NavigationForm]![NavigationSubform].Form![frm_Mission].Form.Requery

    There must be something I am missing in calling procedures while using a navigation form since the mission form works fine outside of the navigation form.


    • Edited by ehammer321 Tuesday, August 25, 2015 9:18 PM
    Tuesday, August 25, 2015 9:17 PM
  • I have tried more combos of control paths than I can count and am not sure if this is just an inherent problem with navigation forms and control references available for the forms that are loaded while using VBA or I am just missing something.

    Again, any help is appreciated.

    May I suggest that you consider a better naming convention for your controls as well as your forms.  If I were a developer coming behind you to work on this system, I would be quite confused.

    By your examples here, it looks as though you are trying to requery a subform within a subform.  Correct?

    As a suggestion, your naming conventions for your sub-form controls should be more like the following:

    Forms!frmNavigation!sfmFormName1.Form!sfmFormName2.Requery

    Tuesday, August 25, 2015 9:38 PM
  • I do plan on cleaning up the naming conventions. I am able to requery the subform within a subform. The problem I am having is the Form_Dirty events will not operate off the syntax listed above or any other combo that I have tried. They work just fine on the form when it is separate from the navigation form.
    Wednesday, August 26, 2015 12:28 AM
  • One thing I noticed about your examples is that the way you are referencing the subforms by name is different than how I have made this work in my applications.

    You must bear in mind that the subform control that you reference in your path is distinct from the form that it hosts. Generally, you want to reference the controls by name, while their subforms get referenced via the .Form property.

    So, for example, to reference the name of a nested subform several levels deep, syntax like this should work, where Forms(0) is your top-level navigation form, and you use 'mySubformControl' consistently as the name of the control hosting your subforms:

    Forms(0).[mySubformControl].[Form].[mySubformControl].[Form].[mySubformControl].[Form].[Name]

    Hope this helps.

    Wednesday, August 26, 2015 1:35 AM
  • I take note of the fact you never confirmed my suggestion to make sure Form_Dirty is a public procedure.


    -Tom. Microsoft Access MVP

    Wednesday, August 26, 2015 2:04 AM
  • They were public procedures initially, just forgot to put that comment in the first reply to your post, on the second reply to your post I commented to that fact. I was able to get the events working by placing them in the Form_Load event vs. Form_Dirty event of each subform. With out calling the dirty event from the mainform it works fine after getting the syntax straightened out. I am still trying to figure out why the call from the main form to the dirty event on the subforms would not work since the syntax that is used works with other events but not the dirty event. I was able to achieve the functionality of the form that I was looking for albeit not the way I thought it would be. I will continue to mess with it and post it if figure out the original method I was trying.
    Wednesday, August 26, 2015 3:46 AM
  • Hi ehammer321,

    >> however the following code in the Mission form (the dash board form that contains the subforms) works just fine when opened by itself but not when opened using the tab on the navigation form.

    I am not sure how do you use the code, did you mean that you use the code in an event or other place? In my option, the dirty event occurs when the contents of the specified control changes. I do not know why you use code to call this method, or do you mean that the Form_Dirty did not work when you change the controls. If it did not work when you change the controls, I suggest you check the forms whether it is bound, the event will not work if it is an unbound form or report. You could refer the link below for more information.

    # Form.Dirty Event (Access)
    https://msdn.microsoft.com/en-us/library/office/ff835655.aspx?f=255&MSPPError=-2147217396

    >> I was able to achieve the functionality of the form that I was looking for albeit not the way I thought it would be.

    Do you mean that you could achieve your requirement with other ways? If you insist on your original issue, it would be helpful if you could share us a simple demo through OneDrive and details steps to reproduce your issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, August 27, 2015 7:20 AM