assigning incremental numbers - or getting new numbers

[Guest]

I'm developing a rather simple database but I want to give a unique
identifier to each "employee" and each "case". When entering a "new"
employee in the database, it doesn't show the autonumber field. So, I put a
field in and tried to write a module that loops through the database and
returns the next higher number. It's not working... help?

Here's what I wrote:

Function FindMax(empID)

Dim db As DAO.Database
Dim mx As Integer
Dim rs As DAO.Recordset
Dim rsVal As String

Set db = CurrentDb (employee_database)
Set rs = db.OpenRecordset ("increment", dbOpenDynaset)

rs.MoveFirst

rsVal = rs.Fields("[Employee_ID_number]").Value
' Set mx equal to the numeric portion of the field.
mx = Right(rsVal, Len(rsVal) - 3)

'Loop to make sure you have the maximum number.
Do While Not rs.EOF
rsVal = rs.Fields("[Employee_ID_number]").Value
If Richt(rsVal, Len(rsVal) - 3) > mx Then
mx = Right(rsVal, Len(rsVal) - 3)
End If
rs.MoveNext
Loop

'Increment the maxium value by one and
'combine the text with the maxium number.
FindMax = "EM-" & (mx + 1)

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function

 

[Nikos Yannacopoulos]

Carolyn,

You can do this in a simpler, and probably much faster way, especially
if your employees table grows big. Start by saving the following query:

SELECT Val(Right([empID],Len([empID])-3)) AS EmpNo
FROM tblEmployees

where I have assumed the table to be called tblEmployees, and the ID
field to be called Employee_ID; modify as required to match the actual
names, and save as qry_Emp_No.

Then your VBA function just needs to be:

Function FindMax()

Dim mx As Integer

mx = DMax("[EmpNo]", "qry_Emp_No")
FindMax = "EM-" & (mx + 1)

End Function

Note: not sure why you have used empID as a function parameter, I do not
think you need it.

HTH,
Nikos

 

[Dan Artuso]

Hi,
The usual way is to use DMax

DMax("yourField","yourTable","empId =" & empId) + 1

 

[Guest]

I'm sorry to be such a ditz... but the query has me confused... if I go into
the query function in design, it has boxes and such... I'm not sure how to
save your query... I really do appreciate your help...

 

[Dan Artuso]

Hi,
If you want to use the SQL posted (you'll surely have to change the table and
field names though), go into the query designer and select SQL View from the
SQL menu at the top left.

--
HTH
-------
Dan Artuso, MVP

I'm sorry to be such a ditz... but the query has me confused... if I go into
the query function in design, it has boxes and such... I'm not sure how to
save your query... I really do appreciate your help...

I'm developing a rather simple database but I want to give a unique
identifier to each "employee" and each "case". When entering a "new"
employee in the database, it doesn't show the autonumber field. So, I put a
field in and tried to write a module that loops through the database and
returns the next higher number. It's not working... help?

Here's what I wrote:

Function FindMax(empID)

Dim db As DAO.Database
Dim mx As Integer
Dim rs As DAO.Recordset
Dim rsVal As String

Set db = CurrentDb (employee_database)
Set rs = db.OpenRecordset ("increment", dbOpenDynaset)

rs.MoveFirst

rsVal = rs.Fields("[Employee_ID_number]").Value
' Set mx equal to the numeric portion of the field.
mx = Right(rsVal, Len(rsVal) - 3)

'Loop to make sure you have the maximum number.
Do While Not rs.EOF
rsVal = rs.Fields("[Employee_ID_number]").Value
If Richt(rsVal, Len(rsVal) - 3) > mx Then
mx = Right(rsVal, Len(rsVal) - 3)
End If
rs.MoveNext
Loop

'Increment the maxium value by one and
'combine the text with the maxium number.
FindMax = "EM-" & (mx + 1)

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function

 

[Guest]

Well, I tried...still not working...... did it in SQL... tried the other
method too.. gives me errors.

 

[Nikos Yannacopoulos]

Carolyn,

Where/what error do you get? Does the saved query work correctly, to
begin with? If it does it should return just the numeric part of the ID
field for each record, when run manually.

Nikos

Well, I tried...still not working...... did it in SQL... tried the other
method too.. gives me errors.

:

I'm developing a rather simple database but I want to give a unique
identifier to each "employee" and each "case". When entering a "new"
employee in the database, it doesn't show the autonumber field. So, I put a
field in and tried to write a module that loops through the database and
returns the next higher number. It's not working... help?

Here's what I wrote:

Function FindMax(empID)

Dim db As DAO.Database
Dim mx As Integer
Dim rs As DAO.Recordset
Dim rsVal As String

Set db = CurrentDb (employee_database)
Set rs = db.OpenRecordset ("increment", dbOpenDynaset)

rs.MoveFirst

rsVal = rs.Fields("[Employee_ID_number]").Value
' Set mx equal to the numeric portion of the field.
mx = Right(rsVal, Len(rsVal) - 3)

'Loop to make sure you have the maximum number.
Do While Not rs.EOF
rsVal = rs.Fields("[Employee_ID_number]").Value
If Richt(rsVal, Len(rsVal) - 3) > mx Then
mx = Right(rsVal, Len(rsVal) - 3)
End If
rs.MoveNext
Loop

'Increment the maxium value by one and
'combine the text with the maxium number.
FindMax = "EM-" & (mx + 1)

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function

 

[Guest]

It says it's an invalid use of null. I have the field set as a text...should
I set it to number? Also, I put in the properties of the field "findmax()"
in the control source - would this cause an error as well? Thank you so much
for your help, I do appreciate it... I'm almost done with this thing... and
I've tried to diligently follow examples in the books... this just has me
stymied.

Carolyn,

Where/what error do you get? Does the saved query work correctly, to
begin with? If it does it should return just the numeric part of the ID
field for each record, when run manually.

Nikos

Well, I tried...still not working...... did it in SQL... tried the other
method too.. gives me errors.

:

I'm developing a rather simple database but I want to give a unique
identifier to each "employee" and each "case". When entering a "new"
employee in the database, it doesn't show the autonumber field. So, I put a
field in and tried to write a module that loops through the database and
returns the next higher number. It's not working... help?

Here's what I wrote:

Function FindMax(empID)

Dim db As DAO.Database
Dim mx As Integer
Dim rs As DAO.Recordset
Dim rsVal As String

Set db = CurrentDb (employee_database)
Set rs = db.OpenRecordset ("increment", dbOpenDynaset)

rs.MoveFirst

rsVal = rs.Fields("[Employee_ID_number]").Value
' Set mx equal to the numeric portion of the field.
mx = Right(rsVal, Len(rsVal) - 3)

'Loop to make sure you have the maximum number.
Do While Not rs.EOF
rsVal = rs.Fields("[Employee_ID_number]").Value
If Richt(rsVal, Len(rsVal) - 3) > mx Then
mx = Right(rsVal, Len(rsVal) - 3)
End If
rs.MoveNext
Loop

'Increment the maxium value by one and
'combine the text with the maxium number.
FindMax = "EM-" & (mx + 1)

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function

 

[Nikos Yannacopoulos]

Carolyn,

You have set the field as text correct;y, or it would not accept
alphanumeric entries like you are using.

"Invalid use of Null" makes me suspect that either (a) the saved query
returns no results for some reason, or (b) a table or field name in the
VB function is not correct.

Don't understand what you mean by "I put in the properties of the field
"findmax()" in the control source". Can you clarify? Where are you using
the function? In a calculated field in a query? A control on a form? Or
what?

It will help if you post back:
1. The exact name of the table and the ID field,
2. The SQL code of the saved query, and
3. Your code

so I can check for inconsistencies.

HTH,
Nikos

It says it's an invalid use of null. I have the field set as a text...should
I set it to number? Also, I put in the properties of the field "findmax()"
in the control source - would this cause an error as well? Thank you so much
for your help, I do appreciate it... I'm almost done with this thing... and
I've tried to diligently follow examples in the books... this just has me
stymied.

:

Carolyn,

Where/what error do you get? Does the saved query work correctly, to
begin with? If it does it should return just the numeric part of the ID
field for each record, when run manually.

Nikos

Well, I tried...still not working...... did it in SQL... tried the other
method too.. gives me errors.

:



I'm developing a rather simple database but I want to give a unique
identifier to each "employee" and each "case". When entering a "new"
employee in the database, it doesn't show the autonumber field. So, I put a
field in and tried to write a module that loops through the database and
returns the next higher number. It's not working... help?

Here's what I wrote:

Function FindMax(empID)

Dim db As DAO.Database
Dim mx As Integer
Dim rs As DAO.Recordset
Dim rsVal As String

Set db = CurrentDb (employee_database)
Set rs = db.OpenRecordset ("increment", dbOpenDynaset)

rs.MoveFirst

rsVal = rs.Fields("[Employee_ID_number]").Value
' Set mx equal to the numeric portion of the field.
mx = Right(rsVal, Len(rsVal) - 3)

'Loop to make sure you have the maximum number.
Do While Not rs.EOF
rsVal = rs.Fields("[Employee_ID_number]").Value
If Richt(rsVal, Len(rsVal) - 3) > mx Then
mx = Right(rsVal, Len(rsVal) - 3)
End If
rs.MoveNext
Loop

'Increment the maxium value by one and
'combine the text with the maxium number.
FindMax = "EM-" & (mx + 1)

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function