SQL Incremental numbers

[Mike Green]

Is there a syntax to use SQL to insert a record and give it the next
available number in a table? I have tried incorperating DMAX into the
statement to get the correct number from the table but I cannot seem to get
the syntax right or am I going about it the wrong way?
The basic SQL is in my previous post, I have one more field in TblInvoice
that I would like to assign the next available number to, If at all possible
I would then like to automatically format it with some text so that it would
become the Invoice referece number that is displayed on the finished
invoice.

Any help is appreciated

Mike

 

[John Spencer]

The following will work in a database. The only problem would be if you
have multiple users and they are trying to do an insert at the same
exact time.


Assumption: Your field in tblInvoice is a number field.

Dim NextInvoice as Long

Dim strSQLCreateInvoice As String
Dim RecordNo As String


NextInvoice = Nz(DMax("SomeInvoiceNumberField","tblInvoice"),0) + 1


RecordNo = Me.FdJobCardID


'Create the invoice
strSQLCreateInvoice = "INSERT INTO TblInvoice ( FdJobCardRef," & _
"FdInvoiceJCNo, FdInvoiceJobCardClosedDate," & _
" FdInvoiceCustomerOrderNo, YOURNEWFIELD ) " & _
"SELECT TblJobCard.FdJobCardID, " & _
"TblJobCard.FdJobCardNumber, " & _
"TblJobCard.FdJobCardCloseDate, " & _
"TblJobCard.FdCustomerOrderNo, " & NextInvoice & " " _
"FROM TblJobCard " & _
"WHERE (((TblJobCard.FdJobCardID)=" & RecordNo

CurrentDb.Execute strSQLCreateInvoice

 

[Mike Green]

Sorry to be a pain but I have tried this and I keep getting a syntax error
in statement! Could someone please set me right on the syntax please.

Thanks in advance


*******************code************************
Dim NextInvoice As Long
Dim strSQLCreateInvoice As String
Dim RecordNo As String

NextInvoice = Nz(DMax("FdInvoiceCounter", "tblInvoice"), 0) + 1


'Create the invoice
strSQLCreateInvoice = "INSERT INTO TblInvoice ( FdJobCardRef, FdInvoiceJCNo,
" & _
"FdInvoiceJobCardClosedDate, FdInvoiceCustomerOrderNo, FdInvoiceCounter,
FdCustomerID, FdVRN, ) " & _
"SELECT TblJobCard.FdJobCardID, " & _
"TblJobCard.FdJobCardNumber, " & _
"TblJobCard.FdJobCardCloseDate, " & _
"TblJobCard.FdCustomerOrderNo, "" & NextInvoice & "" & _
"TblJobCard.FdCustomerID, " & _
"TblJobCard.FdVRN, "& _
"FROM TblJobCard " & _
"WHERE (TblJobCard.FdJobCardID)= " & RecordNo


CurrentDb.Execute strSQLCreateInvoice

**********************************end***********************************

 

[John Spencer]

Your statement seems to have an extra comma at the end of the insert
fields list and at the send of the SELECT clause in the source query

'Create the invoice

strSQLCreateInvoice = "INSERT INTO TblInvoice ( FdJobCardRef,
FdInvoiceJCNo,
" & _
"FdInvoiceJobCardClosedDate, FdInvoiceCustomerOrderNo, FdInvoiceCounter,
FdCustomerID, FdVRN ) " & _
"SELECT TblJobCard.FdJobCardID, " & _
"TblJobCard.FdJobCardNumber, " & _
"TblJobCard.FdJobCardCloseDate, " & _
"TblJobCard.FdCustomerOrderNo, " & NextInvoice & " " & _
"TblJobCard.FdCustomerID, " & _
"TblJobCard.FdVRN "& _
"FROM TblJobCard " & _
"WHERE (TblJobCard.FdJobCardID)= " & RecordNo

Add these two line immediately after the above

Debug.Print StrSQLCreateInvoice
Stop



A nice technique of handling this type of problem is to use debug.print
to print the derived SQL statement to the immediate window. Then you
can copy the statement to a query (New, don't choose any table, switch
to SQL view and paste)

Then you try to execute the query (Query: Run). If it errors, you will
get a better message and may get a better feel for exactly the source of
the syntax error.

Once things are working correctly delete the Debug.Print and the stop
from your code.

 

[Mike Green]

Thanks for you answer it worked a treat!!!

Mike Green