"New Record" methods (not Access "methods")

[Monty]

I'm having some trouble programmatically inserting a new record into a
table. The table contains one indexed field of the type AutoNumber.
I'm tried using the SQL INSERT statement, but I run into problems doing
an insert because I fail to include a value for the autonumber field in
the VALUES portion of the INSERT statement. I haven't discovered a
syntax that will allow me to do the insert while leaving the autonumber
field alone. I've tried using a comma as a placehold and using the
NULL value as a placeholder, to no avail.

Any suggestions?

I'm also looking into the DoCmd.GotoRecord,,acNewRecord (I may have
this syntax wrong right here), but I'm not able to find the syntax for
accessing (within VB) that new record. Any pointers to the right
information?

Thanks in advance

 

[Dirk Goldgar]

I'm having some trouble programmatically inserting a new record into a
table. The table contains one indexed field of the type AutoNumber.
I'm tried using the SQL INSERT statement, but I run into problems
doing an insert because I fail to include a value for the autonumber
field in the VALUES portion of the INSERT statement. I haven't
discovered a syntax that will allow me to do the insert while leaving
the autonumber field alone. I've tried using a comma as a placehold
and using the NULL value as a placeholder, to no avail.

Any suggestions?

I'm also looking into the DoCmd.GotoRecord,,acNewRecord (I may have
this syntax wrong right here), but I'm not able to find the syntax for
accessing (within VB) that new record. Any pointers to the right
information?

What's the purpose of this table, containing as it does only an
autonumber field? It's a bit odd, and the answer to that question might
suggest a better method.

You might be able to extract the current last autonumber value in the
table, increment it, and insert it in the table. I'm thinking that
would probably be unreliable in a multi-user scenario, but the SQL might
look like this:

INSERT INTO YourTable (YourIDField)
SELECT Max(YourIDField) + 1 FROM YourTable;

Are you trying to do this on a form, as your second question suggests,
or was that just grasping at straws?

 

[Guest]

If the field is an AutoNumber the field DOES NOT need to be mentioned in the
Insert statement ...

INSERT INTO tblMyTable (OneField, TwoField, ThreeField)
VALUES ('TextValue1', 'TextValue2', 'TextValue3')

tblMyTable
-------------
RecordID (Primary Key, AutoNumber)
OneField (Text)
TwoField (Text)
ThreeField (Text)

In this situation, RecordID will fill in with a number generated by the db
engine.

HTH ...

 

[Monty]

What's the purpose of this table, containing as it does only an
autonumber field? It's a bit odd, and the answer to that question might
suggest a better method.

You might be able to extract the current last autonumber value in the
table, increment it, and insert it in the table. I'm thinking that
would probably be unreliable in a multi-user scenario, but the SQL might
look like this:

INSERT INTO YourTable (YourIDField)
SELECT Max(YourIDField) + 1 FROM YourTable;

Are you trying to do this on a form, as your second question suggests,
or was that just grasping at straws?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Sorry, the actual number of fields in this table is six: record key,
hardware, make, model, serial number, and description. The record key
exists to make the individual record uniquely selectable because info
in the remaining fields can be repeated throught the database.

 

[Monty]

If the field is an AutoNumber the field DOES NOT need to be mentioned in the
Insert statement ...

INSERT INTO tblMyTable (OneField, TwoField, ThreeField)
VALUES ('TextValue1', 'TextValue2', 'TextValue3')

tblMyTable
-------------
RecordID (Primary Key, AutoNumber)
OneField (Text)
TwoField (Text)
ThreeField (Text)

In this situation, RecordID will fill in with a number generated by the db
engine.

HTH ...

That's what I thought at first, but trying to use an SQL insert
statement generated an error stating that the number of fields were
mismatched (not enough insert values for the record definition). I
counted and counted again and the number of values were correct, minus
the autonumber value. I'm stumped.

 

[Dirk Goldgar]

That's what I thought at first, but trying to use an SQL insert
statement generated an error stating that the number of fields were
mismatched (not enough insert values for the record definition). I
counted and counted again and the number of values were correct, minus
the autonumber value. I'm stumped.

datAdrenaline was correct: an append query does not need to name the
autonumber field; however, as in the example posted, the number of
fields and the number of values needs to match. If this doesn't seem to
be working for you, please post the code or SQL statement you are using.

 

[John Vinson]

That's what I thought at first, but trying to use an SQL insert
statement generated an error stating that the number of fields were
mismatched (not enough insert values for the record definition). I
counted and counted again and the number of values were correct, minus
the autonumber value. I'm stumped.

You *are* explicitly naming the target fields, aren't you? You cannot
use the syntax

INSERT INTO mytable
VALUES ("x", "y", 3, #8/1/2006#);

you must specify the fieldnames:

INSERT INTO mytable (Text1, Text2, Numfield, Datefield)
VALUES ("x", "y", 3, #8/1/2006#);


John W. Vinson[MVP]

 

[Monty]

You *are* explicitly naming the target fields, aren't you? You cannot
use the syntax

INSERT INTO mytable
VALUES ("x", "y", 3, #8/1/2006#);

you must specify the fieldnames:

INSERT INTO mytable (Text1, Text2, Numfield, Datefield)
VALUES ("x", "y", 3, #8/1/2006#);


John W. Vinson[MVP]

John, that is exaclty what I was doing wrong. I assumed (from lack of
experience) that the SQL would presume the field values would be listed
in order and not need explicit mention.

Thanks to everyone

 

[John Vinson]

John, that is exaclty what I was doing wrong. I assumed (from lack of
experience) that the SQL would presume the field values would be listed
in order and not need explicit mention.

That's exactly the *problem* - it DOES make that assumption, but in
this case you wanted to explicitly omit the autonumber.

John W. Vinson[MVP]

 

[aaron.kempf]

yeah.. but in Access; is it possible to over-ride the fact that it is
an autonumber?

In an Access Data Project; it would be as easy as

SET IDENTITY_INSERT tblName ON

and then

SET IDENTITY_INSERT tblName OFF
when you're done with your code.

Yet another reason that everyone should be using Access Data Projects
lol

-Aaron
ADP Nationalist