I am trying to create an SQL statement in code to insert a record with
30 fields into another table with the same properties. The problem is
that both tables have an autonumber field so the statement
Currentdb.Execute "INSERT INTO tblCustomer.* SELECT tblCustImport.*
WHERE tblCustImport.fldCustID =" & varCustID & ";" produces an error.
Is there a way to exclude the autonumber field without having to
specify all the other 29 fields of each table in the statement? As a
work around I am using a saved query (without the autonumber field)
with the docmd.openQuery procedure but this requires the setwarnings
false/true switches which I'm trying hard to avoid.

On Oct 26, 2:14 pm, Lou O <lgeastw... at (no spam) gmail.com> wrote:

I am trying to create an SQL statement in code to insert a record with
30 fields into another table with the same properties. The problem is
that both tables have an autonumber field so the statement
Currentdb.Execute "INSERT INTO tblCustomer.* SELECT tblCustImport.*
WHERE tblCustImport.fldCustID =" & varCustID & ";" produces an error.
Is there a way to exclude the autonumber field without having to
specify all the other 29 fields of each table in the statement? As a
work around I am using a saved query (without the autonumber field)
with the docmd.openQuery procedure but this requires the setwarnings
false/true switches which I'm trying hard to avoid.

Create a (saved) Insert Query using parameters, fill the fields out
with what's needed, execute when you're done.  This is similar to the
way you'd do it if working with a SQL Server database of some kind,
where you'd be sending the parameters to Stored Procedure to do the
actual insert.

Another way to do this is to create a recordset without the AutoNumber
field, and use that as your source for the insert.  Remember that you
can use the autonumber in the source table as an Order By if needed.