Results 1 to 13 of 13
  1. #1
    CaliberGX is offline Novice
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Posts
    15

    Forms, Error - The expression is typed incorrectly, or it is too complex to be evaluated

    One last problem on my forms creation!



    I have created a main form with 4 - 5 smaller subforms.

    Everything seemed to be working fine. I even managed to fill in one full form including both main and subforms. However since then whenever I open the mainform I get the error message as typed in subject and all of the subforms are blank.

    Is there a way to make the subforms open up after I finish with my main form?. If yes will the Foreign Keys of the subforms linking to the mainform still be autofilled or will I need to link them again?

    Thank you for your time guys !

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    SubForms open *before* the MainForm. The only way I know around this aspect of MS Access is to leave the RecordSource of each SubForm blank and set them from the MainForm after it is loaded.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    RecordSource of each SubForm
    ummm - that would be SourceObject then?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    NO! The SourceObject is for the SubFormControl. We would need to change the RecordSource of the SubForm that is being displayed by the SubFormControl.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    but if the subform is trying to work with the mainform before the mainform is loaded, removing the recordsource isn't going to stop that happening unless that interaction is a recordsource that uses a query which references the mainform as a parameter - and that would normally be done through the linkchild/master properties. Leaving the subform control sourceobject property blank and populating after the mainform is loaded, will get round that problem

  6. #6
    CaliberGX is offline Novice
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Posts
    15
    So I should leave it blank and for example as soon as I fill in the first field of the table have an after update event to fill in the sourceobject ?

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    depends on the requirement - my own view is don't populate the subform until it is needed - so if for example it is on a tab, and the tab is not visible, don't populate the sourceobject until the tab is selected. See this link for order of events

    https://support.office.com/en-us/art...7-ce86553682f9

    If you want to see what events are triggered and when, you can do the following

    for each main form/subform open, load and current event put in the code something like

    debug.print me.name & " - Open"

    - change Open for the name of the event

    you can do this for any event regardless of whether you are using it or not at the moment, just to see the flurry of activity. But as a minimum, just those events you are using. You will be surprised how many events get called more than once, and perhaps not in the order you think they will be.

    And if you want to see when a form becomes visible, change debug.print to msgbox so you can step through.

    Also see this link for what events actually do https://msdn.microsoft.com/en-us/lib.../jj249049.aspx

    you will see for example that the form recordset becomes available on the load event, but a specific record not until the current event.

  8. #8
    CaliberGX is offline Novice
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Posts
    15
    Thank you for your detailed reply I will check it and see how it goes.

    I tried doing it as per what I said above however when the sourceobject is filled in correctly and the link between parent and child is ready the subform still remains as if it was unbound :/.

    I will keep on trying and get back to you when I find a solution Thanks a lot

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    the link between parent and child
    by this do you mean the relationships? or the linkchild/master properties of the subform control?

    Note that for forms being used for data input the rule is one form, one table

  10. #10
    CaliberGX is offline Novice
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Posts
    15
    I mean the properties in the subform control not the relationships. I have one main form and like 3-4 subforms each representing a separate table. But I want them in a way so that the Primary key from the mainform is passed on the foreign key of the subform and be easy for the user using it. That is why I want to put all 4 subforms in one main form . If you have other suggestions on how to achieve that I am open for it

    Thanks !

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    still not clear what you mean- set the linkchild property to the name of the foreign key in the subform and the linkmaster to the primary key in the mainform. If you have defined these in your relationships they will be populated automatically - you may need to do this with code as part of setting the sourceobject in code. And if these properties are set, then when you add a new record in the subform the family key will be automatically populated for that new record

  12. #12
    CaliberGX is offline Novice
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Posts
    15
    The relationships were defined properly and the fields are populated automatically. The problem is the error I am getting. You have advised to leave the sourceobject blank however if I do that the subform becomes unbound. Thus I need to put an event at some point that will fill in the subform sourceobject and bound it again to the main form correct?!

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Your original question was 'Is there a way to make the subforms open up after I finish with my main form?.'. populating the source object as suggested does that.

    The assumption from your post is that you have determined that the error in your post header was cause by opening subforms 'too early'

    So the answer to this
    Thus I need to put an event at some point that will fill in the subform sourceobject and bound it again to the main form correct?!
    is yes. But I can't tell what point that is since I have no idea how your form works. It might be the form current event (but that will trigger every time you move to another record in the main form). It might be a button that the user clicks. It might be when the main record is saved. It might be some code that runs after some or all controls are updated to check if it is now 'safe' to open one or more subforms. It might be something else.

    The error in your title is usually generated due to trying to run a query which comes across unexpected nulls or incomplete data in some way which is what Rural Guy was suggesting - remove the recordsource from your subform. However in the meantime your subform would be displaying #Name in all the controls. And you still need to know when to put it back and it isn't actually what you asked for.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-10-2017, 09:51 AM
  2. Replies: 3
    Last Post: 12-21-2015, 09:53 AM
  3. Replies: 2
    Last Post: 06-07-2012, 02:59 PM
  4. Replies: 1
    Last Post: 05-24-2012, 12:34 PM
  5. Replies: 3
    Last Post: 12-30-2011, 12:43 PM

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