Increment table no by one, but don't add new record

[mikeyl62]

Hi,
I have a table that contains my system numbers that I want to start
from
the table contains fields such as OrderID, InvoiceID, JobID, etc...
a single record

for example,
InvoiceID is 100000

Everytime I create an invoice, I go to tblSystemNo and retrieve
InvoiceID

I then will like to run an sql statement to increment this Number in
the same record.

I do
strsql = "INSERT INTO tblsystemno (InvoiceNo)" & _
" Select InvoiceNo+1 "
" From tblSystemNo Where ID = 1;"

This code increments the InvoiceNo field, but adds a new record. I
would like it to over write the 100000 and put i 100001,

I do it this way, so first I don't create such a huge database filled
with numbers only and second I would like the option in the future
where I can just change one field and I can start my Invoice Numbers at
whatever number I choose.

 

[David F Cox]

you need an UPDATE query.

 

[John Vinson]

I then will like to run an sql statement to increment this Number in
the same record.

Then use an Update query:

strsql = "UPDATE tblsystemno " & _
" SET InvoiceNo = InvoiceNo+1 "
" Where ID = 1;"


John W. Vinson[MVP]