Update number field

[TinaCa]

I am migrating data from an old dbase IV database. I have updated all my dates etc. My issue is this that the existing invoice data does not have an invoice Id. The new table in Access does have an invoice ID. I am looking for some suggestions for updating the InvoiceId field (think 1001, 1002, etc). Can I do this with an update query or??? there are about 57,000 rows in the table so i don't want to do it manually

[jzwp11]

What is the datatype of the invoiceID field in your Access table?

What field in your old dataset uniquely identifies the invoice?

[TinaCa]

The InvoiceId is a number field and the old database was not normalized so the only unique combination might be the invoice date and customer id.

Tina

[jzwp11]

The best way that I can think of is to create a recordset based on the records that have no InvoiceID number and loop through them in code and update them with sequential invoice numbers. A straight update query will not work because there is no way to increment the InvoiceID # as the update query runs.

I have attached an example database. If you look in tblInvoice, 2 records have an invoiceID and 2 records do not. (close the table when done viewing the records). Open the form frmRunUpdate and click the button. After the code runs, reopen the table and you will see that the two records that did not have an invoice now have an invoice ID.

The code behind the button on the form is as follows:

Private Sub cmdRunUpdate_Click()
On Error GoTo Err_cmdRunUpdate_Click

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

'set up the recordset
Dim myRS As New ADODB.Recordset
myRS.ActiveConnection = cnn1

'set up the query to get the records that need to be updated
Dim mySQL As String
mySQL = "SELECT * FROM tblInvoice WHERE InvoiceID is Null"

'set up a variable for incrementing the invoice ID

Dim myInvoice As Long
'set the variable to the highest invoice ID # in the invoice table and then add 1 to it
myInvoice = DMax("invoiceID", "tblInvoice") + 1

Debug.Print myInvoice

'open the recordset
myRS.Open mySQL, , adOpenDynamic, adLockOptimistic


'loop through the records and update each with a new invoice ID number
Do Until myRS.EOF
    
    With myRS
        !InvoiceID = myInvoice
        .Update
    End With
    
    'increment myInvoice for the next invoice record
    myInvoice = myInvoice + 1
    
    'move to the next record
    myRS.MoveNext
Loop

MsgBox "update complete"

myRS.Close
Set myRS = Nothing



Exit_cmdRunUpdate_Click:
    Exit Sub

Err_cmdRunUpdate_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunUpdate_Click
    
End Sub