Number Sequence on Table

[David]

I'm creating a table where I need a sequential number range starting at 1 for
row 1- 2 for row 2 etc
Currently I am deleting the contents the table- then condencing the file-
then using the auto number- which give me the row sequence 1,2,3 etc- but
condencing the report each time is very cumbersome- is there a way that in
the make table query i could generate this sequence- rather than using the
above process?

 

[Dale Fye]

If you are using this in a report, create a textbox on the report, give it a
value of 1 (ControlSource: =1). Then set the RunningSum property to "Over
Group" or "Over All"

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[David]

No it's got to be table as I have to have a certain header then body then
trailer to append to each other and then create a text file for it- any idea?

 

[Dale Fye]

David,

I have a function (see below) I use for these purposes. Rather than using
an autonumber field in your temporary table, use a long integer as the
datatype for that field. In order to reset the counter to zero, you must call
the function with Reset = true prior to running your append query. In a
command button, it might look like:

Private sub cmd_Test_Click

Call fnSeqNo(0, true)
currentdb.execute "Delete * FROM yourTable", dbFailOnError
currentdb.querydefs("yourInsertQuery").execute dbFailOnError

End Sub

Then, in your insert query add a computed column that calls the function,
passes it a field from your table (doesn't matter what type, but I prefer to
use the ID field), and leave off the Reset value. It might look like:

INSERT INTO yourTable (Field1, Field2, Field3, SeqNo)
SELECT Field1, Field2, Field3, fnSeqNo([Field1]) as SeqNo
FROM yourTable


Public Function fnSeqNo(SomeValue As Variant, Optional Reset As Boolean =
False) As Long

Static mySeqNo As Long

If Reset = True Then
mySeqNo = 0
Else
mySeqNo = mySeqNo + 1
End If
fnSeqNo = mySeqNo

End Function

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.