RE: Number Sequence on Table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.



"David" wrote:

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?
--
Thanks for your help


"Dale Fye" wrote:

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" wrote:

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?
--
Thanks for your help
.

Relevant Pages

  • Re: Can foreground and background colors be queried?
    ... query the currently set colors ... When exiting, reset the colors back to what they were ... set so I can read without eyestrain and I'm very comfortable with ...
    (comp.lang.rexx)
  • RE: Sequential Record Numbering in Queries
    ... You can add the function as a computed field in a query, ... scroll down, and then click the First Record button, ... about sequential numbering in a report), I have found you can base the report ... Static mySeqNo As Long ...
    (microsoft.public.access.queries)
  • Re: Rolling Differences
    ... is one in what I wrote) which references the outer query (which I did have ... "Previous Inventory" value: ... I mentioned the question of "groups" which may reset your totals. ...
    (microsoft.public.access.queries)
  • Re: Reset subtotal within a query
    ... I have a query that uses a subquery to subtotal one of its fields. ... So, it's possible that out of 100 records, I might reset the ... I can do this in a report but I need it in query form so I can run ... it automatically when users log in to let them know if the ...
    (comp.databases.ms-access)
  • Re: Creating an autonumber ID field in a make table query?
    ... I created a query to delete all the records then ran it then the other queries in a macro. ... "Jeff Boyce" wrote in message ... That said, if you absolutely, positively must reset autonumbers, do a search on "reset autonumber"... ... I link access to our oracle database through ODBC and download the data for a particular station and then crunch the data. ...
    (microsoft.public.access.queries)