Sequential numberin 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
 
Andy,

Not very clear what you are trying to do here. Are you trying to return the
number in a query, or populate a table field? Neither is done correctly. Or
is it something else? Please clarify so you can be helped.

Nikos
 
I am trying to populate a table field. I will assign this function to a
query and then run it from within
 
From the way it looks, I think you want to do a MoveLast instead of the MoveFirst.

Otherwise, it all looks good*.


*There might be an issue with this generator if you have multiple users. EX. A user adds a record. #10001 is generated. A second User adds a record, too. Unfortunately, it is added before the first person saves its record. In this caes, they could create record 10001. And, if this field is set to 'no duplicate', you'll generate an error.
An alternative is to to create a complex key.
Year & Month & Day & Hour & Minute & Second combo keys are useful in many ways. It is a 14-character key that you can generate on the fly. Virtually guaranteed not to be a duplicate. (You can also use it do generate quick reports simply by parsing out a key - annual, hourly, etc.)
 
Andy,

I sort of understand what you are trying to do (not the specifics), though I
must say that to use a function in a query to store a calculated value in a
table through a recordset operation looks rather unusual!

So, which one is it? Are you trying to populate a field in the same table
the query is based on, or in another table? In the latter case, are the two
tables joined on emp_no? Is it a one-to-one or one-to-many relationship?
Does the query do some foltering o the original table, or do you just use it
to host the function? Please provide an explanation in plain english as well
as all the detail required (table ina involved field names); I feel you are
very close.

Regards,
Nikos
 
Back
Top