Problem with "Dual List Box"

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

All:

I'm working on an "Add/Delete" (Move Right/Move Left) dual list listbox.
The example I'm using is based on Sandra Daigle's work.

Right now, I'm stuck on "converting" Sandra's example SQL into mine in order
to reflect my tbl and field naming conventions. This is something which I
thought would be very simple; however, I get one and the same error in qry
SQL view over and over again. The error indicates: "Syntax error in
FROM clause".


Sandra's Mine
======================
tblClasses tblQuestions
ClassId IndexId
ClassName Question

tblPersonClasses tblQuestionsMatrix
PersonNbr CredentialId
ClassId IndexId

tblPersons tblOrganisationAndCredentials
PersonNbr CredentialId
PersonName Division

frmPersonClasses frmQuestionDivisionMatrix


Sandra's SQL code for query "qryAvailClasses"
==============================
SELECT c.ClassId, c.ClassName, pc.ClassId
FROM tblclasses AS c LEFT JOIN [SELECT tblPersonClasses.PersonNbr,
tblPersonClasses.ClassId FROM tblPersonClasses WHERE
tblPersonClasses.PersonNbr=[forms]![frmPersonClasses]![personnbr]]. AS pc ON
c.ClassId = pc.ClassId
WHERE (((pc.ClassId) Is Null));

My converted code resulting in "Syntax error in FROM clause"
======================================
SELECT c.IndexId, c.Question, pc.IndexId
FROM tblQuestions AS c LEFT JOIN [SELECT tblQuestionsMatrix.CredentialId,
tblQuestionsMatrix.IndexId FROM tblQuestionsMatrix WHERE
tblQuestionsMatrix.CredentialId=[forms]![frmQuestionDivisionMatrix]![credentialid]].
AS pc ON c.IndexId = pc.IndexId
WHERE (((pc.IndexId) Is Null));


Sandra's SQL code for query "qryAvailClasses"
==============================
SELECT p.PersonNbr, p.PersonName, cp.PersonNbr
FROM tblpersons AS p LEFT JOIN [SELECT tblPersonClasses.PersonNbr,
tblPersonClasses.ClassId FROM tblPersonClasses WHERE
tblPersonClasses.ClassId=[forms]![frmClassPersons]![classid]]. AS cp ON
p.PersonNbr = cp.PersonNbr
WHERE (((cp.PersonNbr) Is Null));


My updated code resulting in "Syntax error in FROM clause"
======================================
SELECT p.CredentialId, p.Division, cp.CredentialId
FROM tblOrganisationAndCredentials AS p LEFT JOIN [SELECT
tblQuestionsMatrix.CredentialId, tblQuestionsMatrix.IndexId FROM
tblQuestionsMatrix WHERE
tblQuestionsMatrix.IndexId=[forms]![frmQuestionDivisionMatrix]![indexid]].
AS cp ON p.CredentialId = cp.CredentialId
WHERE (((cp.CredentialId) Is Null));


I did a one-by-one replacement of all components... this is really weird.
Any ideas what I might be missing here?

Tom

P.S. If you feel comfortable downloading the sample db (ConvertCode.zip)
with the errors, please check it out @ http://tombock2004.i8.com/Test
 
has been fixed... before after subselect, parenthesis always changed to
square brackets. Also when making a change, the additional "." was added to
closing square bracket.

--
Thanks,
Tom


Tom said:
All:

I'm working on an "Add/Delete" (Move Right/Move Left) dual list listbox.
The example I'm using is based on Sandra Daigle's work.

Right now, I'm stuck on "converting" Sandra's example SQL into mine in
order to reflect my tbl and field naming conventions. This is something
which I thought would be very simple; however, I get one and the same
error in qry SQL view over and over again. The error indicates:
"Syntax error in FROM clause".


Sandra's Mine
======================
tblClasses tblQuestions
ClassId IndexId
ClassName Question

tblPersonClasses tblQuestionsMatrix
PersonNbr CredentialId
ClassId IndexId

tblPersons tblOrganisationAndCredentials
PersonNbr CredentialId
PersonName Division

frmPersonClasses frmQuestionDivisionMatrix


Sandra's SQL code for query "qryAvailClasses"
==============================
SELECT c.ClassId, c.ClassName, pc.ClassId
FROM tblclasses AS c LEFT JOIN [SELECT tblPersonClasses.PersonNbr,
tblPersonClasses.ClassId FROM tblPersonClasses WHERE
tblPersonClasses.PersonNbr=[forms]![frmPersonClasses]![personnbr]]. AS pc
ON c.ClassId = pc.ClassId
WHERE (((pc.ClassId) Is Null));

My converted code resulting in "Syntax error in FROM clause"
======================================
SELECT c.IndexId, c.Question, pc.IndexId
FROM tblQuestions AS c LEFT JOIN [SELECT tblQuestionsMatrix.CredentialId,
tblQuestionsMatrix.IndexId FROM tblQuestionsMatrix WHERE
tblQuestionsMatrix.CredentialId=[forms]![frmQuestionDivisionMatrix]![credentialid]].
AS pc ON c.IndexId = pc.IndexId
WHERE (((pc.IndexId) Is Null));


Sandra's SQL code for query "qryAvailClasses"
==============================
SELECT p.PersonNbr, p.PersonName, cp.PersonNbr
FROM tblpersons AS p LEFT JOIN [SELECT tblPersonClasses.PersonNbr,
tblPersonClasses.ClassId FROM tblPersonClasses WHERE
tblPersonClasses.ClassId=[forms]![frmClassPersons]![classid]]. AS cp ON
p.PersonNbr = cp.PersonNbr
WHERE (((cp.PersonNbr) Is Null));


My updated code resulting in "Syntax error in FROM clause"
======================================
SELECT p.CredentialId, p.Division, cp.CredentialId
FROM tblOrganisationAndCredentials AS p LEFT JOIN [SELECT
tblQuestionsMatrix.CredentialId, tblQuestionsMatrix.IndexId FROM
tblQuestionsMatrix WHERE
tblQuestionsMatrix.IndexId=[forms]![frmQuestionDivisionMatrix]![indexid]].
AS cp ON p.CredentialId = cp.CredentialId
WHERE (((cp.CredentialId) Is Null));


I did a one-by-one replacement of all components... this is really weird.
Any ideas what I might be missing here?

Tom

P.S. If you feel comfortable downloading the sample db (ConvertCode.zip)
with the errors, please check it out @ http://tombock2004.i8.com/Test
Click to expand...
 
Back
Top