Results 1 to 9 of 9
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2018
    Posts
    202

    Check if subform is open

    I have a from that is several navigation form deep. There is a list on the form that I would like refreshed/requery once the popup form has closed to update the list for any further action needed. (I am attempting to: upon close of popup form, check if form is open. If open, then requery a list.)


    Problem: my code below will not look at subforms.
    Code:
    Private Sub cmdClose_Click()    DoCmd.GoToRecord , , acNewRec
        'check if form is open
        If Application.CurrentProject.AllForms("frmInvoiceDataDetail_MR").IsLoaded = True Then
            'if true, then requery/refresh list
            [Forms]![frmNavMain]![frmNavMain].[Form]![frmNavInvoiceDetail].[Form]![frmNavInvoiceDetail_MR].[Form].lstManual.Requery
         End If
        'close popup form
        DoCmd.Close
    End Sub
    Here is the list location
    [Forms]![frmNavMain]![frmNavMain].[Form]![frmNavInvoiceDetail].[Form]![frmNavInvoiceDetail_MR].[Form].lstManual

    the list is set to vba code: ctllstManual.RowSource = "qryManualLost_Invoice"

    I have tried multiple variations without success. Also not sure if my thinking is appropriate. Another approach?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your issue may be that subforms are not loaded, therefore they are not part of the forms collection. You can, however, test for a property such as Name.
    Try this: open the form and try to retrieve the form Name property (or the listbox if you prefer) in the immediate window. You should get a valid result if your syntax is correct.
    Then close the form, go back to the immediate window and at the end of the same line you used, hit return again. You should get a message that the object cannot be found.

    So perhaps alter your code to get a property (e.g. Name) and deal with the result. It's not clear to me if there could be a different form open or just none so how to code it depends on that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is [frmNavMain] really a Navigation form, or is that the name you have given a "regular form"?
    Since you have 120+ posts, you're probably familiar with this form stuff.

  4. #4
    mlrucci is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2018
    Posts
    202
    I probably deserve that. Just got in a habit. Thanks for pointing that out!

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I found some code here to identify subforms within forms that may offer some insight. Another option is to post a copy of your database with explicit insructions to highlight the issue.

    Based on the code I found.

    Identifying subforms:

    frmNavMain Has subform(s) ***
    frmNavInvoice
    frmNavInvoice_MR
    frmInvoiceData_MR

    frmNavInvoice Has subform(s) ***
    frmNavInvoice_MR
    frmInvoiceData_MR

    frmNavInvoice_MR Has subform(s) ***
    frmInvoiceData_MR

    frmNavInvoice_HR Has subform(s) ***
    frmInvoiceData_HR


    Last edited by orange; 08-26-2021 at 11:47 AM. Reason: ran code against your database

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Won't matter if you figure out how to reference any subform if you stay with the posted test. It isn't loaded - unless nav forms behave differently than regular subforms in that regard.

    typical navigation > control syntax is
    Forms![main navigation form].[NavigationSubform].Form...
    NavigationSubform is the default name Access gives to the nav subform IIRC.

    I would agree that a db sample is likely the best option. Speaking for myself, I'm too lazy to build something that resembles what you seem to have. Besides, there's a good chance I'd get it wrong.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    mlrucci is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2018
    Posts
    202
    I have attached a very basic db. In the real db, I could have one of many subforms open. I would like to:
    1. click on add product
    2. complete entry
    3. Click close on the popup:
    Code:
    Private Sub cmdClose_Click()
        'Identify if the form [Forms]![frmNavMain]![frmNavMain].[Form]![frmNavInvoice].[Form]![frmNavInvoice_MR]is open.
        'There can be several subforms open and the requery would depend on which form is open as to refresh the list.
        'Requery [Forms]![frmNavMain]![frmNavMain].[Form]![frmNavInvoice].[Form]![frmNavInvoice_MR].[Form].lstProduct
        DoCmd.Close
    End Sub
    Database1_ Requery subform.zip

    I have attempted
    If CurrentProject.AllForms("frmInvoiceMR").IsLoaded = True
    which understandably does not see the subform.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The answer to the syntax is in post 6:
    typical navigation > control syntax is
    Forms![main navigation form].[NavigationSubform].Form...
    NavigationSubform is the default name Access gives to the nav subform IIRC.
    However, I should have said "nav subform control".

    so
    Code:
    [Forms]![frmNavMain]![navigationsubform].[Form].[navigationsubform].[form].[navigationsubform].[form].lstProduct
    Add .name to that in the immediate window when the form is open in form or design view and it should return the listbox name.

    I don't think that will be the end of your problem though.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It is as I said. The subform is not loaded so you cannot directly check if it is open.
    Code:
    MsgBox Application.CurrentProject.AllForms("frmInvoiceData_MR").IsLoaded
    MsgBox Application.CurrentProject.AllForms("frmInvoiceData_HR").IsLoaded
    Both return False in the "File and Close" button click event.

    You could check if the navigation control holds any particular form by name and act accordingly (post #2)?
    Last edited by Micron; 08-26-2021 at 03:05 PM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-14-2019, 05:02 PM
  2. Replies: 0
    Last Post: 12-06-2016, 11:28 AM
  3. Replies: 4
    Last Post: 04-01-2016, 09:14 AM
  4. How to check if a form is open.
    By ismith in forum Forms
    Replies: 2
    Last Post: 01-26-2012, 08:10 AM
  5. Replies: 2
    Last Post: 02-26-2010, 08:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums