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

Posted on 2009-12-29
Medium Priority
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
  • 3
  • 2
LVL 85
ID: 26140015
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

ID: 26140273
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

ID: 26140371
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 85
ID: 26142098
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

KCL earned 0 total points
ID: 26306928
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

578 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