Results 1 to 14 of 14
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    Errors Trying to Access a Subform

    I have a form containing orders which are submitted via email. The purpose of this form is to collect the orders emailed in and validate them then import the correct ones into the "real" tables. So the data this form is bound to is an "import orders" table.

    Important: Sometimes I need to create a customer record for this order. Sometimes the customer already exists, so I don't need to create them, just import a new order for them.

    To help decide if this is an existing customer, I created a subform by dragging the Customers table onto my form in design mode. The subform is automatically named "Customers subform". I deliberately leave the link fields blank so the subform won't be filtered down, but always keep a full list of customers.

    I have code in the subform (a public sub) which causes it to scroll to the proper area when called. The code never gets executed, because I'm getting an error trying to access the subform at all. Here is my main form code:

    Code:
    Private Sub Form_Current()    
        Dim frm As Form
        On Error Resume Next
        Set frm = Me.Controls("Customers subform").Form
        If Err.Number = 0 Then
            frm.LocateCustomer LastName, FirstName
            If Err <> 0 Then
                Debug.Print Err, Error
                Stop
            End If
        End If
        Set frm = Nothing
    End Sub
    First time I run this code (after opening the form the Form_Current event is fired), I get this error:


    Code:
    Error 2455:  You entered an expression that has an invalid reference to the property Form/Report.
    This is expected, as the subform may have not yet been loaded. This is why I put the On Error Resume Next statement in the code.
    Subsequent times invoking the code by moving to a new record causes
    Code:
    Error -2146500594:   Automation error
    The error is encountered during the statement
    Code:
    Set frm = Me.Controls("Customers subform").Form
    This code works properly in other databases, so I'm at a loss as to what is happening here. Any ideas? Thanks...

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This is expected, as the subform may have not yet been loaded.
    Subforms load first, so that's not it. Might have to post a compacted and zipped db copy here as I don't see anything wrong with the syntax of that line. Another way of writing it doesn't involve .Controls("nameHere").Form, but what you have should work.
    EDIT
    However, this is a mystery: LocateCustomer
    What is 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
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Micron View Post
    Subforms load first, so that's not it. Might have to post a compacted and zipped db copy here as I don't see anything wrong with the syntax of that line. Another way of writing it doesn't involve .Controls("nameHere").Form, but what you have should work.
    EDIT
    However, this is a mystery: LocateCustomer
    What is that?
    LocateCustomer is a public subroutine on the subform which actually causes it to scroll to the proper record (it's a continuous form). However, my code won't even get that far.

  4. #4
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Also, notice the error is sometimes different. Now it's saying:
    -2146500594 Method 'Form' of object '_SubForm' failed

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Could try
    Set frm = Forms!MainFormNameHere![customer subform].Form
    However, that last error message suggests you have the wrong name for customer subform (i.e. you're confusing the name of the control that holds the subform with the name of the subform) or there's a spelling error between the two. If I use a wizard for any type of form, I never let the wizard defined names stand, and that includes what the wizard does when you do things like drag a query or table onto a form.

    I don't think you can call a sub in a form from another form sub - I think the call would have to be made from a standard module?
    As per post 2, might have to post a compacted and zipped db copy here as I don't see anything wrong with the syntax of that line.

    Often it is stated that code works in other places when it doesn't work in one particular place. Difficult to pinpoint those as the reasons are varied; e.g. a reference is no longer valid, or perhaps sloppy code that worked in older versions fails in newer versions, or who knows what.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Micron View Post
    Could try
    Set frm = Forms!MainFormNameHere![customer subform].Form
    However, that last error message suggests you have the wrong name for customer subform (i.e. you're confusing the name of the control that holds the subform with the name of the subform) or there's a spelling error between the two. If I use a wizard for any type of form, I never let the wizard defined names stand, and that includes what the wizard does when you do things like drag a query or table onto a form.

    I don't think you can call a sub in a form from another form sub - I think the call would have to be made from a standard module?
    As per post 2, might have to post a compacted and zipped db copy here as I don't see anything wrong with the syntax of that line.

    Often it is stated that code works in other places when it doesn't work in one particular place. Difficult to pinpoint those as the reasons are varied; e.g. a reference is no longer valid, or perhaps sloppy code that worked in older versions fails in newer versions, or who knows what.
    I found another article with the same problem, and one of the answers said this will happen if the subform is corrupted. So I tried deleting the subform out of the form, then deleting the actual subform in the explorer. Then dragged the table back to the main form, and it works fine now. So I guess it WAS corrupted. The wizard-created form ends up having the same name as the subform control in the main form when you create it by dragging a table onto the form. Perhaps a bit confusing but it does work fine.

    Regarding your other points:

    There was no spelling error, and the automatically named subform object was being correctly called. Other than the strange sounding form names ("Customer subform"), it works fine.

    You can definitely call a method of the subform from a method inside the parent form. Just need to make sure the subform method is Public.

    So now it works properly pending aesthetic changes on the subform. When I load a record in the parent form, it scrolls the subform to the same customer if it exists in the subform's recordset. If it doesn't exist, it scrolls the subform to the place where it would be if it existed. This is based on the sort order of the subform of course.

    Thanks for all of the suggestions...

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad you got it working. Re
    You can definitely call a method of the subform from a method inside the parent form
    Can you give an example of that? I think you mean event procedure or other sub or function. AFAIK, you can't "call" methods at all but would be glad to learn something.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Micron View Post
    Glad you got it working. Re
    Can you give an example of that? I think you mean event procedure or other sub or function. AFAIK, you can't "call" methods at all but would be glad to learn something.
    Hey Micron,
    Yes, when I say "method" I mean a subroutine or function
    In this case, when I move from one record to another on the main form, I want the subform (which is a continuous form listing all customers) to scroll to that customer (the one mentioned on the order in the main form).

    If you have a public Sub in the subform which takes arguments of first name and last name, you can execute this method by putting code in the Form_Current event of the main form. So if the subform subroutine is Public Sub FindCustomer(LastName As String, FirstName as String), then in the Form Current event of main form, put this:

    Code:
    Me.subformName.Form.FindCustomer Me!LastName, Me!FirstName
    Then that subroutine will be executed on the subform. It's up to you what you put in there, but what I did was to find the record with that name:

    Code:
    Public Sub FindCustomer(LastName As String, FirstName As String)
    
        Dim rs      As Recordset
        Dim bkMk    As Variant
        
        Application.Echo False
        Set rs = Me.RecordsetClone
        With rs
            .FindFirst "LastName='" & LastName & "' AND FirstName='" & FirstName & "'"
            If .NoMatch Then .FindFirst "LastName='" & LastName & "'"
            If .NoMatch Then .FindFirst "LastName > '" & LastName & "'"
            If .NoMatch Then .MoveLast
            bkMk = .Bookmark
        End With
        With Me.Recordset
            .Bookmark = bkMk
            If .AbsolutePosition > 0 Then
                .MovePrevious
                .MoveNext
            End If
        End With
        Application.Echo True
        Set rs = Nothing
    
    
    End Sub
    This code just scrolls the subform to that customer (or to the next customer alphabetically if the customer from the main form is not really a customer on file).

    Also, I believe you are wrong about the subforms being loaded first. When the main form's Form_Current fires for the first time, there is an error accessing Me.subformName.Form object. It's not there. Therefore, the subform does not scroll to the customer indicated in the first record of the main form. If I move to the second record, it works, and if I move back to the first record, that works also. It's only the very first Form_Current event which seems to be firing before the subform is properly loaded.

    My work-around for that is to put code in the subform's Form_Load event explicitly calling the FindCustomer method. That forces everything to work properly.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    No, it is you that is wrong.
    Just one link. Look for a few yourself.
    https://www.tek-tips.com/viewthread.cfm?qid=1754981
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    RMittleman, you are trying to teach an old dog old tricks.
    If you have a public Sub in the subform which takes arguments of first name and last name, you can execute this method
    I say again, you do not execute methods. As for being wrong about subform vs main form sequence, I copied this from M$ for you, just in case you're less inclined to believe some other poster:
    NOTE - the bold font isn't my doing. It's from the site page and I can't seem to un-bold it in my post without invoking Notepad.

    Working with subforms

    When you open a form that contains a subform, the subform and its records are loaded before the main form. Thus, the events for the subform and its controls (such as Open, Current, Enter, and GotFocus) occur before the events for the form. However, the Activate event does not occur for subforms. Therefore, opening a main form triggers an Activate event only for the main form.
    Last edited by Micron; 04-26-2022 at 01:38 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Micron View Post
    RMittleman, you are trying to teach an old dog old tricks.
    I say again, you do not execute methods. As for being wrong about subform vs main form sequence, I copied this from M$ for you, just in case you're less inclined to believe some other poster:
    NOTE - the bold font isn't my doing. It's from the site page and I can't seem to un-bold it in my post without invoking Notepad.

    Working with subforms

    When you open a form that contains a subform, the subform and its records are loaded before the main form. Thus, the events for the subform and its controls (such as Open, Current, Enter, and GotFocus) occur before the events for the form. However, the Activate event does not occur for subforms. Therefore, opening a main form triggers an Activate event only for the main form.
    I feel like we are splitting hairs here. This article (https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/call-statement) says you can use the Call keyword to transfer control to a subroutine or function (what I referred to as a method), or simply invoke the subroutine or function without the Call keyword. Maybe I shouldn't be using words like Execute or Method, because they appear to be vague. But when I did that, it was basically shorthand for efficiency's sake when posting the question and replies.

    I totally agree with you about the order of events on subforms and forms. I wasn't trying to argue that point. But even if the events are firing in a particular order (subform events first), something is definitely going wrong. When I code the parent form's On_Current event, it executes this bit of code, among other things:
    Code:
    Me.Orders_subform.Form.SelectOrder Me.OrderID
    The very first time the parent form's On_Current event fires (which seems to be when the form loads), it tries to execute the above code, then gets this error:
    Code:
     Error 2455:  You entered an expression that has an invalid reference to the property Form/Report.
    But after that, everything works just fine. So even though the subform's events have fired already, I suggest that something on the subform is not quite "ready" to be referred to, and the error is raised.

    This isn't the first time I've noticed objects not being "ready" during an event in Access. Sometimes, in the Form_Load event of a form, if I'm addressing certain data controls such as grids or other types of objects that should be filled, I have seen errors thrown saying the object is invalid or has no value. I've taken to using the following code, and moving certain Form_Load code to the Form_Activate event, like so:
    Code:
    Dim isLoading As Boolean
    
    Private Sub Form_Activate()
    
        If isLoading Then
            ' do some object loading stuff here...
        End If
        isLoading = False
    
    End Sub
    
    Private Sub Form_Load
    
        IsLoading = True
        ' do stuff not involving objects here...
    
    End Sub
    This always seems to work. By the time the Form_Activate event fires, all objects on the form have been properly instantiated / loaded.
    I suspect something similar is happening in my project here. The subform control's .Form property is not yet "there" for some reason.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your issue might be due to how the form is referenced although I don't really see what the problem is (however, I'm not sure you identified the actual line that is raising any error). You're using a collection-based reference, and I can tell you that subforms are not added to the collection of open forms. You can prove that by opening your main form & let it load the subform. In the immediate window type
    ?CurrentProject.Allforms"(nameOfMainFormHere").IsL oaded and hit enter at the end of that line. Result should be True.
    Then do the same for the subform name. The result should be False. This is because open subforms are not part of the loaded forms collection. Not saying that's your problem, but it's one reason why I tend to use the Forms! syntax rather than Me.

    If you want to pursue, post a zipped copy of your compacted db with instructions on how to replicate the issue. Maybe seeing more code and following events will reveal the cause.

    Suggest try Forms!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Micron View Post
    Your issue might be due to how the form is referenced although I don't really see what the problem is (however, I'm not sure you identified the actual line that is raising any error). You're using a collection-based reference, and I can tell you that subforms are not added to the collection of open forms. You can prove that by opening your main form & let it load the subform. In the immediate window type
    ?CurrentProject.Allforms"(nameOfMainFormHere").IsL oaded and hit enter at the end of that line. Result should be True.
    Then do the same for the subform name. The result should be False. This is because open subforms are not part of the loaded forms collection. Not saying that's your problem, but it's one reason why I tend to use the Forms! syntax rather than Me.

    If you want to pursue, post a zipped copy of your compacted db with instructions on how to replicate the issue. Maybe seeing more code and following events will reveal the cause.

    Suggest try Forms!
    This is getting fun...and confusing.
    I did mention the exact error statement. In this method of my parent form,
    Code:
    Public Sub SyncCustomer(LastName As String, FirstName As String)
    
        Dim frm As Form
        
        On Error Resume Next
        Set frm = Me.Customers_subform.Form
        If Err.Number = 0 Then
            frm.LocateCustomer LastName, FirstName
        End If
        If Err <> 0 Then
            Debug.Print Err, Error
        End If
        Set frm = Nothing
    
    
    End Sub
    This subroutine is called from my parent form's Form_Current event.
    The error is encountered trying to set the Frm object, but as mentioned before, only the very first time the Form_Current event fires. Putting the On Error Resume Next prevents the error from stopping my code. As you can see, the parent form is trying to call a public method in the subform called LocateCustomer. That On Error Resume Next statement handles the error, but then the subform does not refresh and present the proper customer because of the Resume Next. Work-around for this is to call the subform's LocateCustomer method directly from its own Form_Load event. This causes the first-time locating of the subform record. Thereafter, the method above does not throw an error at all.

    I'm confused with your description of how the subform is not part of the parent form's controls collection. While that may be true about the actual form object of the subform, I don't think it's true about the Subform control itself (the subform control, not the form that gets displayed in it). If you put a stop at the top of the parent form's Form_Current event, the issue can be recreated. If you query the form's controls collection, you will see the subform control(s) of any subforms contained in the parent form.

    I like to use this in the immediate window:
    Code:
    for i = 0 to Me.Controls.Count-1:?i,Me.Controls(i).Name:next
    You get a list of control names, including the names of the subform control(s).

    You are entirely correct about what happens when you use the AllForms property. That subform never appears.

    But if you stop at the first Form_Current event and print this in the immediate window:
    Code:
    ?me.Soldiers_subform.Form.Name
    You get the error I'm referring to.

    BUT, if you hit F5 to continue past the error, then deliberately switch to the next record, it's different. When the code stops and you type the above into the immediate window, then you get the actual form name of the subform (turns out it has the same exact name as the subform control, probably because I dragged a table into the form in design mode to create the subform).

    Anyway, making progress. Thanks for the feedback...

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm confused with your description of how the subform is not part of the parent form's controls collection.
    I did not say that.

    subforms are not added to the collection of open forms.
    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. Margins Errors - Always with MS Access
    By FriQenstein in forum Reports
    Replies: 3
    Last Post: 09-03-2020, 10:28 AM
  2. Errors from an Access Query
    By gquery in forum Queries
    Replies: 10
    Last Post: 10-08-2018, 04:19 PM
  3. Replies: 9
    Last Post: 06-28-2018, 06:41 AM
  4. Access database errors
    By NejcZ in forum Access
    Replies: 7
    Last Post: 10-06-2014, 11:11 AM
  5. Replies: 4
    Last Post: 06-21-2014, 05:43 AM

Tags for this Thread

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