Auto number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello.
I have a table with an auto-number field that is also primary key.
I have also a vba module that verifies certain conditions and if the
conditions are true the inserts another record on the table. I'm using an
Insert into SQL comand.
How can i increment the value of the auto-number, using this type of comand?
 
Luis said:
hello.
I have a table with an auto-number field that is also primary key.
I have also a vba module that verifies certain conditions and if the
conditions are true the inserts another record on the table. I'm
using an Insert into SQL comand.
How can i increment the value of the auto-number, using this type of
comand?
Click to expand...

Simply leave that field out of the insert statement and Access will
automatically insert the next number.

(that's why they call it "AutoNumber") :-)
 
Luis,

You don't; actually you don't include it in your SQL at all, Access will
take care of it as required.

HTH,
Nikos
 
It isn't working. I get an error message saying that the number of query
values and destination fields are not the same.
 
The table has 10 fields and i'm only inserting 9 values. The ID field is the
first of the table.

DoCmd.RunSQL ("INSERT INTO CALENDARIZACAO VALUES('" & matr & "','" &
Me.Data_Inicial & "','" & Me.Data_final & "',NULL,'" & Me.PERIODO & "','" &
Me.LOCAL_DESLOC & "','" & Me.UTILIZADOR & "','" & Me.OBSERVACOES & "',NULL);")
 
Luis said:
The table has 10 fields and i'm only inserting 9 values. The ID field
is the first of the table.

DoCmd.RunSQL ("INSERT INTO CALENDARIZACAO VALUES('" & matr & "','" &
Me.Data_Inicial & "','" & Me.Data_final & "',NULL,'" & Me.PERIODO &
"','" & Me.LOCAL_DESLOC & "','" & Me.UTILIZADOR & "','" &
Me.OBSERVACOES & "',NULL);")
Click to expand...

You have to explicitly list the fields of the table or Access will assume
you want to fill ALL of them.

INSERT INTO CALENDARIZACAO (Field1, Field2, etc...) VALUES(...
 
Ok, thanks a lot.

Rick Brandt said:
You have to explicitly list the fields of the table or Access will assume
you want to fill ALL of them.

INSERT INTO CALENDARIZACAO (Field1, Field2, etc...) VALUES(...
Click to expand...
 
Inserting the record, without the Autonumber field in the statement
should do it automatically.
 
That's a different problem. The INSERT statement has two sections to it,
the values being inserted and the fields into which they're going. It is
stricktly one for one. If you do a manual count of both you'll see that
you have an extra value that has no field to go into or an extra field
with no value. Your statement as it exists now is like Musical Chairs,
one less chair for the number of contestants.

David H
 
Back
Top