Auto number

[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?

 

[Rick Brandt]

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?

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

(that's why they call it "AutoNumber")

 

[Nikos Yannacopoulos]

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

 

[Guest]

It isn't working. I get an error message saying that the number of query
values and destination fields are not the same.

 

[Rick Brandt]

It isn't working. I get an error message saying that the number of
query values and destination fields are not the same.

Post your SQL.

 

[Guest]

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);")

 

[Rick Brandt]

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);")

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(...

 

[Guest]

Ok, thanks a lot.

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(...

 

[David C. Holley]

Inserting the record, without the Autonumber field in the statement
should do it automatically.

 

[David C. Holley]

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