Access Sql insert subquery not working

Question

I'm designing a web form to publish to a SharePoint webpage in Microsoft Access 2010. I have a form that uses a combo box to select a team name. I need to enter the team id corresponding to that team name into the employee table. This is what I have so far:

INSERT INTO Employee ( Employee_Name, Team_ID )
VALUES ([Forms]![Add Employee]![txtName], (SELECT MAX(Team.Team_ID)
FROM Team, Employee
WHERE [Team]![Team_Name]=[Forms]![Add Employee]![cmbxTeam]));

It gives me an error saying

Query input must contain at least one table or query.

How do I fix this?

Answer 1

You can either use the VALUES clause to specify hard coded values, or you can use a SELECT statement to generate the stuff that will be inserted. Here you are combining the two. Instead:

INSERT INTO Employee ( Employee_Name, Team_ID )
SELECT 
    [Forms]![Add Employee]![txtName], 
    MAX(Team.Team_ID)
FROM Team, Employee
WHERE [Team]![Team_Name]=[Forms]![Add Employee]![cmbxTeam];

Slightly unrelated, why are you cross joining employees and teams, when you don't need the Employee table in your SELECT?

This should be equivalent and much much faster:

INSERT INTO Employee ( Employee_Name, Team_ID )
SELECT 
    [Forms]![Add Employee]![txtName], 
    MAX(Team.Team_ID)
FROM Team
WHERE [Team]![Team_Name] = [Forms]![Add Employee]![cmbxTeam];