Using MAX from another table in a INSERT action.

[Fernando Morais]

Hi,

i was trying to do a query that is inserting values on a DB, and one of
the field should be the result from a query using MAX on antoher DB.
Something like this:

INSERT INTO tblSNew ( idEquipamento, idCliente, idSWDescrição )
VALUES ((SELECT MAX(idEquip) FROM tblHW), (Forms!frmAddPC!txtCli),
(Forms!frmAddPC!txtPC));

But this gives and undefined error in access, i've tried changing the
(SELECT MAX(idEquip) FROM tblHW) to (MAX(tblHW.idEquip)) and it says i
can't use aggregated functions.

And i'm stuck here, is it any mistake on that? Is that possible, or i
need to do it in another way?

Thanks.

 

[Steve Schapel]

Fernando,

Try a domain function...
DMax("[idEquip]","tblHW")

 

[John Spencer]

You might be able to use

INSERT INTO tblSNew ( idEquipamento, idCliente, idSWDescrição )
SELECT Max(IdEquip)
, Forms!frmAddPC!txtCli
, Forms!frmAddPC!txtPC
FROM tblHW
GROUP BY Forms!frmAddPC!txtCli
, Forms!frmAddPC!txtPC

Or use the DMax function as posted elsewhere.

 

[Fernando Morais]

Thank you,

It worked perfectly.

 

[Fernando Morais]

Thank you,

I'm going to try that, i've used DMAX and it worked.
But i'm going to try this one too.

In the mean time, until i had no answer i went for a longer route.
Using SELECT INTO, creating a temp table and then moving the data to the
correct table using a INSERT.