I didn't say it would pop up the string, I said it would print it out to the VBA Immediate window. That's normally displayed at the bottom of the screen in the VBA editor.
I just noticed you have a FROM clause, which isn't a appropriate for a VALUES clause (it would be for a SELECT clause). For the VALUES method the syntax is:
INSERT INTO TableName(Field1, Field2,...)
VALUES('ABC', 123,...)
I've also never tried the subqueries in a VALUES clause, but take everything out from the FROM and see where we are.