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.