Trouble with INSERT statement

Hi all,

I'm trying to execute an insert statement in VBA into an access database. Basically, I have a UserForm that takes an ID, Last Name, Middle Name, and First Name from the user. When the data is submitted, the insert statement will run. One of the values of the insert should be a "NICKNAME". The important part to mention is that there is a table called NAMEMASTER that is guaranteed to have the columns "FirstName", "MiddleName", "FirstName", and a "NickName" that will be unique to every unique combination of a user's First Name, Middle Name, and Last Name (this data is pre-loaded into the table, so it is guaranteed to be there). Below is my code:

insert into TABLE_A
(
ID,
LASTNAME,
MIDDLENAME,
FIRSTNAME,
NickName
)
VALUES
(
123,
'Smith',
'Andrew',
'Joseph',
(
SELECT NAMEMASTER.NickName
FROM NAMEMASTER
WHERE
NAMEMASTER.LASTNAME = 'Smith' AND
NAMEMASTER.MIDDLENAME = 'Andrew' AND
NAMEMASTER.FIRSTNAME = 'Joseph'
)
)




When I execute this code, I get the error message: "Query input must contain at least one table or query." If I remove the nested select from the "values" section of the insert (as well as the "NickName" column from the "insert" section, the error doesn't occur. The select works perfectly on its own if i run it in the access database, and it might be equally important to add that the NickName column in the database is Text.

I know this was wordy, but I wanted to make sure I didn't leave anything out.


Any help on this at all would be GREATLY appreciated.

Thanks so much!!!