sequential numbering in a query

  • Thread starter Thread starter Anderson
  • Start date Start date
A

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
 
Your code should be returning 10,001, not 10,000. The reason you're not
getting a higher number is that you're not looping through the recordset:

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
rst.MoveFirst
Do While rst.EOF = False
Counter = counter + 1
rst.MoveNext
Loop
rst.Close

However, if what you're trying to do is get a number that's 10,000 more than
the total number of existing employees, try:

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

strSQL = "Select Count(*) As TotalEmployees From AA_SAP_Numbers"

'open the results
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.EOF = False Then
Generate_Number = Counter + rst!TotalEmployees
Else
Generate_Number = Counter
End If
End Function

or, simpler,

Function Generate_Number() As Variant
Dim Counter As Variant
counter = 10000

Generate_Number = DCount("*", "AA_SAP_Numbers") + Counter

End Function

And since you know you're always going to be dealing with numbers, the
function and Counter should both be declared as Long, not Variant. There's
no reason to pass emp_no as a parameter to the function since you don't use
its value anywhere in the function.
 
Back
Top