Append & Sub Queries

[Johnny Emde]

Hey All (Again)

I'am trying to do this:

INSERT INTO tblTeams ( TeamName, FK_TeamLeader, TeamNotes, Created, Modified )
SELECT [p_TeamName] AS Expr1, (SELECT tblUsers.UserID FROM tblUsers WHERE tblUsers.UserIdent = [p_TeamLeaderName] ) AS Expr2, [p_TeamNotes] AS Expr3, Now() AS Expr4, [Expr4] AS Expr5;

Note: the Subquery - I'll recieve the error RESERVED ERROR (-3025) THERE IS NO MESSAGE FOR THIS ERROR

Any suggestion to solve the problem appr. ( NOT USING VBA )

Kind regards
Johnny Emde Jensen

 

[Chris2]

Hey All (Again)

I'am trying to do this:

INSERT INTO tblTeams ( TeamName, FK_TeamLeader, TeamNotes, Created, Modified )
SELECT [p_TeamName] AS Expr1, (SELECT tblUsers.UserID FROM tblUsers

WHERE tblUsers.UserIdent >= [p_TeamLeaderName] ) AS Expr2,
[p_TeamNotes] AS Expr3, Now() AS Expr4, [Expr4] AS Expr5;

Note: the Subquery - I'll recieve the error RESERVED ERROR (-3025)

THERE IS NO MESSAGE FOR >THIS ERROR

Any suggestion to solve the problem appr. ( NOT USING VBA )

Kind regards
Johnny Emde Jensen

Johnny Emde,

CREATE TABLE tblUsers
(UserID INTEGER
,UserIdent TEXT(25)
,CONSTRAINT pk_tblUsers
PRIMARY KEY (UserID)
,CONSTRAINT un_tblUsers_UserIdent
UNIQUE (UserIdent)
)

CREATE TABLE tblTeams
(TeamName TEXT(25)
,FK_TeamLeader INTEGER
,TeamNotes TEXT(255)
,Created DATETIME
,Modified DATETIME
,CONSTRAINT pk_tblTeams
PRIMARY KEY (TeamName)
,CONSTRAINT fk_tblTeams_tblUsers_FK_TeamLeader
FOREIGN KEY (FK_TeamLeader)
REFERENCES tblUsers (UserID)
)


Sample Data:

tblUsers

1, "Jay"
2, "Dee"
3, "Jeff"


Query:

PARAMETERS p_TeamName TEXT(25)
,p_TeamLeaderName TEXT(25)
,p_TeamNotes TEXT(255);
INSERT INTO tblTeams
( TeamName, FK_TeamLeader, TeamNotes, Created, Modified )
SELECT [p_TeamName]
,DLookup("UserID", "tblUsers", "UserIdent = " & """" &
[p_TeamLeaderName] & """")
,[p_TeamNotes]
,Now()
,Now();

Parameters:

Eagles
Jay
Winners!

Results:

tblTeam

Eagles, 1, Winners!, 12/04/2005 5:34:26 AM, 12/04/2005 5:34:26 AM


Sincerely,

Chris O.

 

[Amy Blankenship]

Your problem is that you have not FROM in your subquery. There is no SELECT without FROM.

HTH;

Amy
Hey All (Again)

I'am trying to do this:

INSERT INTO tblTeams ( TeamName, FK_TeamLeader, TeamNotes, Created, Modified )
SELECT [p_TeamName] AS Expr1, (SELECT tblUsers.UserID FROM tblUsers WHERE tblUsers.UserIdent = [p_TeamLeaderName] ) AS Expr2, [p_TeamNotes] AS Expr3, Now() AS Expr4, [Expr4] AS Expr5;

Note: the Subquery - I'll recieve the error RESERVED ERROR (-3025) THERE IS NO MESSAGE FOR THIS ERROR

Any suggestion to solve the problem appr. ( NOT USING VBA )

Kind regards
Johnny Emde Jensen