Run-time error 2465 when opening subform

[Lyn]

Hi, this has been driving me nuts. I'm on Office 2003 SP1, Win XP SP1.

I am opening a form with a number of subforms based on various tables. The
subforms are populated via the main form's Current event. I am using
similar code to open each of the subforms successfully -- except for this
one case which gives the above error.

I have simplified the SQL to its most basic level. It runs just fine as a
query. I just can't make it work in VBA. Here is the code snippet:

mySQL = "SELECT * FROM MiscRecs" 'simplified
Me.[sfmEducation].Form.RecordSource = mySQL
Me.[sfmEducation].LinkMasterFields = ""
Me.[sfmEducation].LinkChildFields = ""

The subform is sfmEducation. It contains several fields bound to table
MiscRecs in tabular format. When the error occurs (opening the main form),
the second line of the above code is highlighted in debug mode.

The full error meesage is:

Run-time error '2465':
Microsoft Office Access can't find the field '|' referred to in your
expression.

I had the '|' occur once before and I posted a query on it. Someone
answered but I have lost the reply. I think it meant that Office could not
identify the field (ie, '|' is a placeholder for the unidentified field).

I am sure that I am missing something very basic, but I'm hanged if I can
see it! All help greatly appreciated.

--
Cheers,
Lyn.

[Salad]

Lyn wrote:

Hi, this has been driving me nuts. I'm on Office 2003 SP1, Win XP SP1.

I am opening a form with a number of subforms based on various tables. The
subforms are populated via the main form's Current event. I am using
similar code to open each of the subforms successfully -- except for this
one case which gives the above error.

I have simplified the SQL to its most basic level. It runs just fine as a
query. I just can't make it work in VBA. Here is the code snippet:

mySQL = "SELECT * FROM MiscRecs" 'simplified
Me.[sfmEducation].Form.RecordSource = mySQL
Me.[sfmEducation].LinkMasterFields = ""
Me.[sfmEducation].LinkChildFields = ""

I haven't tested or looked at the code but...
Me.LinkMasterFields = ""
Me.LinkChildFields = ""
would appear to make sense since you are attempting to sent the
relationship in the subform from the main form.

[Lyn]

Hi, thanks for the quick response.

The format "Me.<subform>.LinkChildFields = " is what was advised to me by
someone else in this group for a similar situation, and it certainly works
for all the other subforms in this main form for which I am using the same
type of coding. It is only since I have been trying to extend the form with
an additional subform that I have experienced this problem.

In any case, I don't think that the code is actually reaching these last two
statements. Debug mode always highlights the previous statement in yellow
(ie, "~RecordSource = mySQL"). Once this problem is resolved, I should be
able to see if the last two statements work or not. I will keep your advice
in mind.

Thanks again.

--
Cheers,
Lyn.

"Salad" <oi*@vinegar.com> wrote in message
news:2S*****************@newsread1.news.pas.earthl ink.net...

Lyn wrote:

Hi, this has been driving me nuts. I'm on Office 2003 SP1, Win XP SP1.

I am opening a form with a number of subforms based on various tables.
The subforms are populated via the main form's Current event. I am using
similar code to open each of the subforms successfully -- except for this
one case which gives the above error.

I have simplified the SQL to its most basic level. It runs just fine as
a query. I just can't make it work in VBA. Here is the code snippet:

mySQL = "SELECT * FROM MiscRecs" 'simplified
Me.[sfmEducation].Form.RecordSource = mySQL
Me.[sfmEducation].LinkMasterFields = ""
Me.[sfmEducation].LinkChildFields = ""

I haven't tested or looked at the code but...
Me.LinkMasterFields = ""
Me.LinkChildFields = ""
would appear to make sense since you are attempting to sent the
relationship in the subform from the main form.

[Salad]

Lyn wrote:

Hi, thanks for the quick response.

The format "Me.<subform>.LinkChildFields = " is what was advised to me by
someone else in this group for a similar situation, and it certainly works
for all the other subforms in this main form for which I am using the same
type of coding. It is only since I have been trying to extend the form with
an additional subform that I have experienced this problem.

In any case, I don't think that the code is actually reaching these last two
statements. Debug mode always highlights the previous statement in yellow
(ie, "~RecordSource = mySQL"). Once this problem is resolved, I should be
able to see if the last two statements work or not. I will keep your advice
in mind.

Thanks again.

Maybe you can try something. Put this code just before the current code
that blows up.

Dim s As String
s = Me.[sfmEducation].Form.RecordSource 'check my spelling of subform
MsgBox s

This should tell you what the current subform recordsource is. It you
get an error here, you know you are making an incorrect reference.

Since you supplied a simplified SQL string, also add this line.
Debug.Print mySQL

Now go to the debug window. Scan the line and see that it looks
correct. If it does, copy it to the clipboard then create a new query...
Queries/New/Design/(click close on AddTables), and from the menu select
Edit/SQL or View/SQL and paste the line from the debug window. It will
either run or blow up. If it blows up, you should be able to determine
what the error is.

[Lyn]

Thanks for the follow-up. I had already tried the "debug.print mySQL" which
produced normal-looking SQL, and I had also tried plugging this into a query
and running it (as per the original post). All of this worked OK, even with
the original SQL (ie, before simplification).

Inserting the string "s" as per your suggestion did not display the Record
Source, because the same '2465' error occurred again, this time with the
debug highlight on the "s = Me. ..." statement.

However, your suggestion did help me find the cause of the problem and
enabled me to fix it at last! When I was typing in your code, I got as far
as:

s = Me.sfm

At that point, the interactive help system displayed a list of all objects
starting with "sfm" -- and I noticed that "sfmEducation" was not there! So
I went back to the subform properties and compared with the other subforms.
The difference was immediately obvious.

When you select the "All" tab, the top two properties are:

Name
Source Object

In all the other subforms, both values were the same (ie, "sfmSubFormName").
But for sfmEducation, Name was set incorrectly to the Caption text
("Education History") and Source Object to "sfmEducation". Setting both to
"sfmEducation" resolved the problem.

I knew it would be something simple and silly in the end -- and it was!
Thanks again for your help.

--
Cheers,
Lyn.

"Salad" <oi*@vinegar.com> wrote in message
news:yf*****************@newsread1.news.pas.earthl ink.net...

Lyn wrote:

Hi, thanks for the quick response.

The format "Me.<subform>.LinkChildFields = " is what was advised to me by
someone else in this group for a similar situation, and it certainly
works for all the other subforms in this main form for which I am using
the same type of coding. It is only since I have been trying to extend
the form with an additional subform that I have experienced this problem.

In any case, I don't think that the code is actually reaching these last
two statements. Debug mode always highlights the previous statement in
yellow (ie, "~RecordSource = mySQL"). Once this problem is resolved, I
should be able to see if the last two statements work or not. I will
keep your advice in mind.

Thanks again.

Maybe you can try something. Put this code just before the current code
that blows up.

Dim s As String
s = Me.[sfmEducation].Form.RecordSource 'check my spelling of subform
MsgBox s

This should tell you what the current subform recordsource is. It you get
an error here, you know you are making an incorrect reference.

Since you supplied a simplified SQL string, also add this line.
Debug.Print mySQL

Now go to the debug window. Scan the line and see that it looks correct.
If it does, copy it to the clipboard then create a new query...
Queries/New/Design/(click close on AddTables), and from the menu select
Edit/SQL or View/SQL and paste the line from the debug window. It will
either run or blow up. If it blows up, you should be able to determine
what the error is.

[Salad]

Lyn wrote:

I knew it would be something simple and silly in the end -- and it was!
Thanks again for your help.

You are welcome.