Update to Office 2007, Append query no longer works

[kalebson]

Hello,

I know I am behind the times but since I updated my Access database to 2007 my append query no longer works. The query is taking values form an unbound form and appending them into a simple customer table. Did something change that ([Forms]![AddCustomer]![LastName].[Value]) no longer works in Access 2007? I get the popup that acts like it cannot see the form. Any help would be great, Thanks.

 

[SOS]

What is the SQL of the query?

 

[kalebson]

Here is the SQL. This worked perfect until I switched to Access 2k7. I have a button on the form that simply runs this query.

INSERT INTO CUSTOMER_LIST ( First_Name, Last_Name, Zipcode, Phone_No, Email, Referred_By, Referrer )
SELECT ([Forms]![AddEmployee]![First_Name].[Value]) AS First_Name, ([Forms]![AddEmployee]![Last_Name].[Value]) AS Last_Name, ([Forms]![AddEmployee]![Zipcode].[Value]) AS Zipcode, ([Forms]![AddEmployee]![Phone_No].[Value]) AS Phone_No, ([Forms]![AddEmployee]!.[Value]) AS Email, ([Forms]![AddEmployee]![Referred_By].[Value]) AS Referred_By, ([Forms]![AddEmployee]![Referrer].[Value]) AS Referrer
FROM CUSTOMER_LIST
GROUP BY ([Forms]![AddEmployee]![First_Name].[Value]), ([Forms]![AddEmployee]![Last_Name].[Value]), ([Forms]![AddEmployee]![Zipcode].[Value]), ([Forms]![AddEmployee]![Phone_No].[Value]), ([Forms]![AddEmployee]![Email].[Value]), ([Forms]![AddEmployee]![Referred_By].[Value]), ([Forms]![AddEmployee]![Referrer].[Value]);

 

[SOS]

Here is the SQL. This worked perfect until I switched to Access 2k7. I have a button on the form that simply runs this query.

INSERT INTO CUSTOMER_LIST ( First_Name, Last_Name, Zipcode, Phone_No, Email, Referred_By, Referrer )
SELECT ([Forms]![AddEmployee]![First_Name].[Value]) AS First_Name, ([Forms]![AddEmployee]![Last_Name].[Value]) AS Last_Name, ([Forms]![AddEmployee]![Zipcode].[Value]) AS Zipcode, ([Forms]![AddEmployee]![Phone_No].[Value]) AS Phone_No, ([Forms]![AddEmployee]!.[Value]) AS Email, ([Forms]![AddEmployee]![Referred_By].[Value]) AS Referred_By, ([Forms]![AddEmployee]![Referrer].[Value]) AS Referrer
FROM CUSTOMER_LIST
GROUP BY ([Forms]![AddEmployee]![First_Name].[Value]), ([Forms]![AddEmployee]![Last_Name].[Value]), ([Forms]![AddEmployee]![Zipcode].[Value]), ([Forms]![AddEmployee]![Phone_No].[Value]), ([Forms]![AddEmployee]![Email].[Value]), ([Forms]![AddEmployee]![Referred_By].[Value]), ([Forms]![AddEmployee]![Referrer].[Value]);[/QUOTE]

See if this works instead:

INSERT INTO CUSTOMER_LIST ( First_Name, Last_Name, Zipcode, Phone_No, Email, Referred_By, Referrer )
VALUES ( [Forms]![AddEmployee]![First_Name], [Forms]![AddEmployee]![Last_Name], [Forms]![AddEmployee]![Zipcode], [Forms]![AddEmployee]![Phone_No], [Forms]![AddEmployee]![Email], [Forms]![AddEmployee]![Referred_By], [Forms]![AddEmployee]![Referrer] )
FROM CUSTOMER_LIST
GROUP BY ([Forms]![AddEmployee]![First_Name], [Forms]![AddEmployee]![Last_Name], [Forms]![AddEmployee]![Zipcode], [Forms]![AddEmployee]![Phone_No], [Forms]![AddEmployee]![Email], [Forms]![AddEmployee]![Referred_By], [Forms]![AddEmployee]![Referrer];

(For an Append query you don't need the parens and aliases you had and also you should probably leave off the .Value part because in 2007, that has to do with multi-valued fields when in a query.

 

[kalebson]

Is says Missing Semicolon ( at end of SQL statement. But its there. Never saw that error before.

 

[SOS]

Did you change this database from an mdb file to an accdb file? If so, how did you do it? The best way I've seen is to import everything into a brand new database shell and to not use the save as feature.

 

[kalebson]

That is what I did. I simply created a blank database and imported everything from the old 2003 database.

 

[CJ_London]

If you are using values, you don't need anything after the values closing bracket

INSERT INTO CUSTOMER_LIST ( First_Name, Last_Name, Zipcode, Phone_No, Email, Referred_By, Referrer )
VALUES ( [Forms]![AddEmployee]![First_Name], [Forms]![AddEmployee]![Last_Name], [Forms]![AddEmployee]![Zipcode], [Forms]![AddEmployee]![Phone_No], [Forms]![AddEmployee]![Email], [Forms]![AddEmployee]![Referred_By], [Forms]![AddEmployee]![Referrer] )

should be sufficient

 

[kalebson]

That unfortunately gives me the same error I was getting before:

Enter Parameter Value
Forms!AddEmployee!First_Name

 

[kalebson]

Sorry that worked CJ, I had the form in design view when I tried your code. Thank you both very much.