subform field linker error: "object variable or With block variable not set"

Posted on 2009-12-29
Last Modified: 2013-11-29
I have a problem similar to a previously posted question in 2003 ( id: 20799795), but I have not found a working solution.

In short, I have three tables tblA, tblB, and tblC.
tblA and tblB have a defined one to one relationship. tblB and tblC are related as one (tblB) to many (tblC) relationship.

I have created a frmA, fsubA, fsubB, fsubC.
frmA and fsubA use the same record source: tblA. (the purpose of this is to allow another level of Tab Control within a specific page on the top level Tab Control in frmA).
fsubA has a nested subform fsubB, which uses the record source of tblB.
fsubB in turn has a nested subform fsubC, which uses the record source tblC.

the PROBLEM arises when I try to define the subform linker field in fsubC, which I get the error message dialog "object variable or With block variable not set".

this problem only occurs when I do this from frmA. HOWEVER, even if I successfully define it from within the subforms (fsubA or fsubB). fsubC is not still not linked when I open frmA (the records are not linked/filtered and all records in tblC are shown).

what is wrong with this and how do I solve it?
I appreciate your help and expertise!

Question by:KCL
    LVL 84
    I would suspect either (a) an issue with an object in your database (i.e. one of your forms) or (b) an issue with the installation of Access or (c) (and somewhat remote) and issue with the installation of Windows or one of it's dependencies.

    Make sure you've update Access/Office fully, and also perform maintenance on the database (Office Button - Manage - Compact). Also try to Compile your database - from the VBA Editor, click Debug - Compile, fix any errors, then Compile again. Continue doing this until the menu option is disabled. Then Compact again.

    If this doesn't work, try recreating the forms one by one. Sometimes a corrupt object will cause this issue.

    Make a backup before doing any of this.

    Author Comment

    I have tried the above and also created the forms and tables in a new database. I still get the same error.
    I have a feeling that Access does not allow too many levels of nested subforms when there are nested forms that use the same record source (frm1 and fsub1). This is because the error only arises in fsub3. fsub1 and fsub2 are fine. Are there workarounds for this problem? Thanks!

    Author Comment

    Actually, the error arises in the third-level subform no matter what. I just created 4 tables:
    Table 1: ID1
    Table 2: ID1, ID2
    Table 3: ID1, ID2, ID3
    Table 4: ID1, ID2, ID3, ID4

    Form 1 uses Table 1, Form 2 uses Table 2 and so on.
    Form 1 has a nested subform using Form 2. Form 2 has a subform Form 3 and Form 3 has a subform Form 4. The subform linker field has problem in the third-level subform Form 4. I thought Access allows up to 7 (or 10?) levels of subforms?
    LVL 84
    Access allows up to 7 "levels" of nested forms, but some of this depends on what view you're using ... for example, are these ALL datasheets? Or All "Normal" views? You can't really mix the two ...

    Accepted Solution

    Actually the linker works if you manually type in the field names in the property window, it's just the linker wizard not working when it is beyond the 3rd level in subforms.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now