Learn when you want, where you want with convenient online training courses. Sign up now!
Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
DCount('ID', 'Table1', 'ID <= ' & [ID]) AS rn
Open in new window
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Public Sub CopyRecords()
Dim rstSource As DAO.Recordset
Dim rstInsert As DAO.Recordset
Dim fld As DAO.Field
Dim strSQLSource As String
Dim strSQLInsert As String
Dim lngLoop As Long
Dim lngCount As Long
Dim lngInvoice As Long
strSQLSource = "SELECT * FROM qryInvoiceNew" ' New invoices.
strSQLInsert = "SELECT TOP 1 * FROM tblInvoice Order By InvoiceNo Desc" ' Existing invoices.
Set rstInsert = CurrentDb.OpenRecordset(strSQLInsert)
lngInvoice = rstInsert!InvoiceNo ' Last used Invoice Number.
Set rstSource = CurrentDb.OpenRecordset(strSQLSource)
lngCount = .RecordCount
For lngLoop = 1 To lngCount
lngInvoice = lngInvoice + 1
For Each fld In rstSource.Fields
If .Attributes And dbAutoIncrField Then
' Skip Autonumber or GUID field.
ElseIf .Name = "InvoiceNo" Then
' Insert next Invoice Number.
rstInsert.Fields(.Name).Value = lngInvoice
' Copy field content.
rstInsert.Fields(.Name).Value = .Value
Set rstInsert = Nothing
Set rstSource = Nothing
Public Sub InsertInvoice()
Dim db As DAO.Database
Dim lngCount As Long
Dim strAppendID As String
strAppendID = GetRandomIdentifier
Set db = CurrentDb
' This should be your INSERT query, this is a simplified example using a customer field with a fix value only
db.Execute "INSERT INTO tblInvoice (Customer,AppendID) VALUES ('X','" & strAppendID & "')"
' This creates the desired unique sequential invoice no. in the table tblInvoiceNo
db.Execute "INSERT INTO tblInvoiceNo (AppendID) VALUES ('" & strAppendID & "')"
' Now update the invoice table with that no
db.Execute "UPDATE tblInvoice INNER JOIN tblInvoiceNo AS INo ON tblInvoice.AppendID = INo.AppendID " & _
" SET tblInvoice.InvoiceNo = INo.InvoiceNo " & _
" WHERE tblInvoice.AppendID = '" & strAppendID & "'"
' Optional: Update the invoice ID back to the invoice no table for later possible use
db.Execute "UPDATE tblInvoiceNo INNER JOIN tblInvoice AS I ON tblInvoiceNo.AppendID = I.AppendID " & _
" SET tblInvoiceNo.ID_Invoice = I.ID_Invoice" & _
" WHERE I.AppendID = '" & strAppendID & "'"
Set db = Nothing
Public Function GetRandomIdentifier() As String
Dim i As Byte
Dim strOut As String
For i = 1 To 100
strOut = strOut & Replace(Chr((Rnd() * 126) + 32), "'", "x")
GetRandomIdentifier = strOut
Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!
you will need a reservation/confirmation method wrapped in transactions. That would be overkill here
What to use and when depends on the circumstances
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.