You would do what you want by specifying all the other fields in a VALUES()
clause. Sorry, but there is no shortcut for omitting a field.

If you want to use this as a "generic" method, then your code will need to
identify which field is an autonumber field, and then build a string that
contains all field names except that one, and then concatenate that string
into your SQL statement that you're building.

short answer yes.

before we get to far.
you are using an SQL server. it has what is know as Stored proceedures.
you can write a stored procedure that
take the input vars and inserts them into the the table.
You can open the SQL servers Books online for examples.
the stored proceedure has the insert with (tbl fields) values(@var1, @var1)
you only have to define the fields in the table you have vars for.
so in the vba you
can
(disclamer- this is not recommended way, but the fastest execution)
                   CurrentProject.Connection.Execute ("UpdtTable1 '"  &
Trim(strvar) & "',6,'100'")
note: " = string delimiters in vba
'=denotes string delimiters in SQL.

to do this your way you would need a recordset for stagging table so the
select is a join
trick (you can use the view to recreate the SQL then copy the sQL over to
the VBA.)

use the insert into strCurrentTable
( list the fields in strCurrentTable )
values
(staggingtblrs.field1)

You mentioned in another post in this thread that you are "only running
Access, not SQL Server". In that case, you are actually in the wrong
newsgroup, as this group is intended for discussion of issues involving ADPs
and SQL Server.

That said, however, in an MDB, the following would work ...

Public Function ExcludeField(ByVal strFieldName As String, _
   ByVal strTableName As String) As String

   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim flds As DAO.Fields
   Dim fld As DAO.Field
   Dim strReturn As String

   Set db = CurrentDb
   Set tdf = db.TableDefs(strTableName)
   Set flds = tdf.Fields
   For Each fld In flds
       If fld.Name <> strFieldName Then
           strReturn = strReturn & fld.Name & ","
       End If
   Next fld
   strReturn = Left$(strReturn, Len(strReturn) - 1)

   ExcludeField = strReturn

End Function

Example of use in Immediate window (TestID is the name of the AutoNumber PK
field in tblTest)

? excludefield("TestID","tblTest")
TestText,TestNumber,OldField,NewField

Brendan & Ken,

Thank you both very much for your help

Sorry I posted on the wrong forum - this seemed to be the only forum in the
access section referring to SQL - Which forum would have been the right one?