|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
"Monty" <dale.schmitz[ at ]offutt.af.mil> wrote in message
news:1159213359.127817.157040[ at ]i3g2000cwc.googlegroups.com
[Quoted Text] > 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?
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
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 ...
--
Regards,
Brent Spaulding
datAdrenaline
"Monty" wrote:
[Quoted Text] > 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 wrote:
[Quoted Text] > "Monty" <dale.schmitz[ at ]offutt.af.mil> wrote in message
> news:1159213359.127817.157040[ at ]i3g2000cwc.googlegroups.com
> > 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?
>
> --
> 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.
|
|
datAdrenaline wrote:
[Quoted Text] > 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 ...
>
> --
> Regards,
> Brent Spaulding
> datAdrenaline
>
>
> "Monty" wrote:
>
> > 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
> >
> >
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.
|
|
"Monty" <dale.schmitz[ at ]offutt.af.mil> wrote in message
news:1159223838.986083.193650[ at ]h48g2000cwc.googlegroups.com
[Quoted Text] >
> 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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
On 25 Sep 2006 15:37:19 -0700, "Monty" <dale.schmitz[ at ]offutt.af.mil>
wrote:
[Quoted Text] >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]
|
|
John Vinson wrote:
[Quoted Text] > On 25 Sep 2006 15:37:19 -0700, "Monty" <dale.schmitz[ at ]offutt.af.mil>
> wrote:
>
> >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]
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
|
|
On 25 Sep 2006 17:28:41 -0700, "Monty" <dale.schmitz[ at ]offutt.af.mil>
wrote:
[Quoted Text] >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]
|
|
|