CODEWITHSUNDEEP
sqlms-accesssubqueryms-access-2003
Wyatt Gillette
Wyatt Gillette

Reputation: 348

SQL in Access 2003: INSERT INTO and multiple SELECT queries

I'm using Access 2003 (forced to do it due to retrocompatibility) to modify a 10 years old project, not made by me. I encounter errors in executing this query:

INSERT INTO ClientiContratto ( ID, CLIENTE, DATA, PERIODO, IMPORTO, FATTURATO )
SELECT [Forms]![InserisciContratto]![Cliente] AS Espr1, (SELECT Nome from TAnagrafica WHERE TAnagrafica.IDAnagr = [Forms]![InserisciContratto]![Cliente]) AS Espr2, [Forms]![InserisciContratto]![Data] AS Espr3, [Forms]![InserisciContratto]![Periodo] AS Espr4, [Forms]![InserisciContratto]![Importo] AS Espr5, False AS Espr6;

That returns errors due to 

 (SELECT Nome from TAnagrafica WHERE TAnagrafica.IDAnagr = [Forms]![InserisciContratto]![Cliente]) AS Espr2

If I execute this query standalone, it works like a charm but when it comes to inserting the query into the INSERT INTO...SELECT statement, it returns (translated from italian):

Runtime error '3000': Reserved error (-3025): there are no messages for this error.

The aim is to insert in a table some new values based on values found in the active form, and the part of code which isn't working should search into a table a value linked to the [InserisciContratto]![Cliente] actual value.

What am I doing wrong? Maybe is that because I cant execute a SELECT subquery in a previous SELECT query?

Any help would be appreciated.

Upvotes: 0

Views: 563

Answers (1)

Erik A
Erik A

Reputation: 32682

You can work around the problem using a DLookUp instead of a subquery:

DLookUp("Nome", "TAnagrafica", "TAnagrafica.IDAnagr = [Forms]![InserisciContratto]![Cliente]")

Note that you can either use the DLookUp on a form control, or in a query. Both are valid. In the query, it'd look like this:

INSERT INTO ClientiContratto ( ID, CLIENTE, DATA, PERIODO, IMPORTO, FATTURATO )
SELECT [Forms]![InserisciContratto]![Cliente] AS Espr1, DLookUp("Nome", "TAnagrafica", "TAnagrafica.IDAnagr = [Forms]![InserisciContratto]![Cliente]") AS Espr2, [Forms]![InserisciContratto]![Data] AS Espr3, [Forms]![InserisciContratto]![Periodo] AS Espr4, [Forms]![InserisciContratto]![Importo] AS Espr5, False AS Espr6;

An alternate, common source of these kind of errors is that Access behaves finicky when using subqueries and not querying from a real table. You can easily work around that by using the subquery as the main query. Note that this does require the subquery to always return a result, else no row will be inserted:

INSERT INTO ClientiContratto ( ID, CLIENTE, DATA, PERIODO, IMPORTO, FATTURATO )
SELECT [Forms]![InserisciContratto]![Cliente] AS Espr1, Nome AS Espr2, [Forms]![InserisciContratto]![Data] AS Espr3, [Forms]![InserisciContratto]![Periodo] AS Espr4, [Forms]![InserisciContratto]![Importo] AS Espr5, False AS Espr6
FROM TAnagrafica
WHERE TAnagrafica.IDAnagr = [Forms]![InserisciContratto]![Cliente]

Upvotes: 1

Related Questions