sequential numbering in a query

[Anderson]

I have a table which has employee number. I have attempted to creat a
function whic will derive a unique number for each record how ever the
code below only returns 10,000 for all records. What I am doing wrong? I
am I right in saying that In assuming that I dont to loop since I am
returnign this for a every record in query.
Your help will be greatly appreciated.

Function Generate_Number(emp_no As Variant) As Variant
Dim strSQL As Variant
Dim rst As Recordset
Dim Counter As Variant
counter = 10000

strSQL = "Select emp_no From AA_SAP_Numbers Order By emp_no"

'open the results
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rst.MoveFirst
Counter = counter + 1
rst.MoveNext
rst.Close
Generate_Number = Counter
End Function
Andy

[Tim Marshall]

To really understand what your code is doing, just walk through this
with a pencil or pointing your finger. You have to realize that this
function STARTS from scratch and STOPS for each and every record in your
table/query.

Thus when the query containing this function is opened, for every
record resulting from the query this function is run and des the following:

1)counter is set to 10000;

2)it opens a record set of table AA_SAP_Numbers sorted by the emp_no;

3)It goes to the first record of that recordset;

4)It increases counter by 1;

5)It moves to the next record of the recordset;

6)It closes the recordset; and

7)The function returns the value of counter, ie, 10001.

If, for example, you have 500 records in the query, the above is
performed without variation 500 times, returning 10001 every single time.

In other words, the recordset you've opened serves no purpose. This
begs the question, why bother bogging down memory by opening a recordset
at all? It further begs the question, why even write a function? Just
type in 10001 as a field in your query builder for the query where
you're using this function.

Of course, that's not what you want. But it's important that you
examine your code and understand why it is what you've written performs
the seven steps I've outlined above.

Anderson wrote:

I have a table which has employee number. I have attempted to creat a
function whic will derive a unique number for each record how ever the
code below only returns 10,000 for all records. What I am doing wrong? I
am I right in saying that In assuming that I dont to loop since I am
returnign this for a every record in query.
Your help will be greatly appreciated.

Function Generate_Number(emp_no As Variant) As Variant
Dim strSQL As Variant
Dim rst As Recordset
Dim Counter As Variant
counter = 10000

strSQL = "Select emp_no From AA_SAP_Numbers Order By emp_no"

'open the results
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rst.MoveFirst
Counter = counter + 1
rst.MoveNext
rst.Close
Generate_Number = Counter
End Function
Andy

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[mar10a]

Will creating a new field in the table that is a datatype of
AUTONUMBER - which will create a new unique number for each record,
work?

Anderson <a.********@londonmet.ac.uk> wrote in message news:<co**********@canard.ulcc.ac.uk>...

I have a table which has employee number. I have attempted to creat a
function whic will derive a unique number for each record how ever the
code below only returns 10,000 for all records. What I am doing wrong? I
am I right in saying that In assuming that I dont to loop since I am
returnign this for a every record in query.
Your help will be greatly appreciated.

Function Generate_Number(emp_no As Variant) As Variant
Dim strSQL As Variant
Dim rst As Recordset
Dim Counter As Variant
counter = 10000

strSQL = "Select emp_no From AA_SAP_Numbers Order By emp_no"

'open the results
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rst.MoveFirst
Counter = counter + 1
rst.MoveNext
rst.Close
Generate_Number = Counter
End Function
Andy