create a sequential number field

  • Thread starter Thread starter Jean
  • Start date Start date
J

Jean

How can I update a field in a table that is linked to an
excel spreadsheet with a sequential number? I need to take
this linked table and select only records with a quantity
0 and assign a sequential number to another field called
Click to expand...
Tag #. The only way I've been able to do this so far is to
do an append query to a new table that has Tag # defined
as autonumber. This works ok, except I have 45 different
tables I need to do this to. I was hoping there was an
easier way, without creating an additional 45 tables.
 
The easiest way is to write a little VBA procedure and run it from the
Immediate window. Start a new module and enter the following code:

Public Sub SequenceTag(strTableName As String)
Dim db As DAO.Database, rst As DAO.Recordset
Dim lngSeq As Long

On Error GoTo Bail
' Point to the current database
Set db = CurrentDb
' Open a recordset on the Excel linked table
' Use the table name passed to the procedure
Set rst = db.OpenRecordset("SELECT * FROM " & _
strTableName & " WHERE Quantity > 0")
Do Until rst.EOF
' Go into edit mode
rst.Edit
' Add 1 to the sequence
lngSeq = lngSeq + 1
' Update the field
rst![Tag #] = lngSeq
' Write the record back
rst.Update
' Get the next one and loop
rst.MoveNext
Loop
' Clean up
rst.Close
Set rst = Nothing
Set db = Nothin
MsgBox "Table " & strTableName & " updated successfully!"
Done:
Exit Sub
Bail:
MsgBox "Unexpected error: " & Err & ", " & Error
Resume Done
End Sub

Let's say your linked table is called ExcelTable1. Open the Immediate
window (Ctrl-G) and enter:
SequenceTag("ExcelTable1")

... and press enter.

You can run the same code for each different linked table.

Have fun...
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top