Generate Number To Automatically Assign To a Combo Box 4

[Happy2]

Hi Everyone,

Can anyone please help me out because I am in a rush to catch up with my deadline. Thanks.

I have a form that is adding new Managers.

Each manager has a unique ID number and I can only use the ID number
from 1 to 999. In order to keep historical record, after I use up to 999,
I have to go back to ID number 1 again. However, this time I assign 1.1, 2.1, 3.1,
4.1...until I reach to 999.1. Then at this point, I will assign the number starting
with 1.2, 2.2, 3.2,....999.2 and the pattern will continue with the next number so
I won't be out of ID number. I need to write a module to automatically assign to my
ID number combo box, so I can have a new ID number for the next Manager.

Thanks in advance for your help.

 

[Steve101]

Ouch! Can you please explain why you are stuck with the 999 limit. It would be much cleaner to remove this limitation probably by conditioning your existing system and data if possible. Is this a limitation over which you have no control. Please elaborate.

Thereafter, it will be possible to write a function to do what you want, but I really dont think its a good way to go unless absolutely necessary. Give us a bit more background.

Cheers,
Steve

 

[scriverb]

The way that I would do this is create a one record parameter table in the application. If this is a server based application with multiple users then this table would have to be located in the back-end database on the server and linked to the front-end applications. I have called it "tblNextID" in this example. Create two fields in this table as follows:

NextID Double
DecPortion double

Create one record and then enter the NextID in the NextID field(i.e. 345) and put the decimal portion in use for this set of ID's. 1 - 999 would be 0, 1.1 - 999.1 would be .1, 1.2 - 999.2 would be .2 , etc.

Paste the following function in the database module.

Function NextID()
Dim db As Database
Dim rs As Recordset
Dim vNextID As Double
Dim vIncrement As Double
Set db = CurrentDb
Set rs = db.OpenRecordset("tblNextID", dbOpenDynaset)
rs.MoveFirst
NextID = rs("NextID&quot
vNextID = rs("NextID&quot + 1
If vNextID >= 1000 Then
vNextID = 1 + (rs("DecPortion&quot + 0.1)
vIncrement = 0.1
End If
rs.Edit
rs("NextID&quot = vNextID
rs("DecIncrement&quot = rs("DecPortion&quot + vIncrement
rs.Update
rs.Close
db.Close
End Function

Now a call to this function will return the next ID and increment the counter in the table making it ready for the next user that adds a record.

me![MgrID] = NextID()

the me![MgrID] is just a fiction form object. Use whatever is appropriate for storing the new id.

I have tested this out and it works as requested. There may be a simpler way of doing this but right now this is the best that I can come up with. I will keep thinking about other possibilities.

Bob Scriver

 

[Happy2]

Hi Steve,

I am limited to 3-digit numbers because our accountants have to enter manager ID numbers to a program that accepts only 3-digit numbers. Even though my program doesn't link to the accounting program, the manager ID numbers are in common. I use 1.1 is same as 1 and it is saved for tracking back historical records.

Thanks a lot for having me to clearify my point, Steve!

 

[Happy2]

Hi Bob,

Thank you very much for your big help as I am working on it right now.

 

[Steve101]

Happy,
Thanks for the clarification. Seems like you have evolved a good solution given the constraints you have in keeping to three digits because of the accounting system. Bob's function seems like the way to go.
Good luck,
Steve

 

[Happy2]

Hi Bob and everyone,

Can you please help me out with this. I am just a beginer, so I don't know much about this. Thanks.
I am using ADODB connection and I don't understand why I have the following messages:

"rs.Edit" Method or data member not found.
"Dim db As Database" User-defined type not defined. It's a compiling error.
Operation is not allowed when the object is closed.

 

[scriverb]

Because your version of ACCESS(2000) the corresponding ADO library currently doesn't recognize the DAO syntax unless you make a specific declaration to DAO:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Update the code in the Function with the code changes above. This should take care of the problem.

Let me know if anything else doesn't work for you.

Bob Scriver

 

[Happy2]

Hi Steve,

When I changed to "db As DAO.Database", I still got the message "User-defined type not defined". However, I put everything together and now have the below function.
I still got an error message from the line of code "rs("NextID&quot = vNextID", which is under line "rs.Edit". It got highlighted and showed the value of "NextID" equals to 1 and value of "vNextID" equals to 2. Please help me. Thanks a lot.

Function NextID()

Dim rs As New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "Select * From tblNextID"
.Open
End With

Dim vNextID As Double
Dim vIncrement As Double
rs.MoveFirst
NextID = rs("NextID&quot
vNextID = rs("NextID&quot + 1
If vNextID >= 1000 Then
vNextID = 1 + (rs("DecPortion&quot + 0.1)
vIncrement = 0.1
End If
' rs.Edit
rs("NextID&quot = vNextID
rs("DecIncrement&quot = rs("DecPortion&quot + vIncrement
rs.Update
rs.Close
db.Close

End Function

 

[scriverb]

It looks like you have commented out the rs.edit statement with the apostrophe(') at the left. You cannot do this as you must declare that you are about to edit the recordset rs before you can attempt to change the data within the recordset. So, remove the apostrophe and let's see if that solves your problem.

Bob Scriver

 

[starsky51]

I think you might have to select a DAO Library in your references. Enter a code module, go to Tools->References.. and tick the latest Microsoft DAO Object Library.
If the DAO Library is already ticked then untick it, click OK, go back into the references and tick it again. This resolves problems where a database is moved between computers with different DAO Library versions.

Hope this helps,
If this is nothing to do with this conversation then i'm sorry for wasting your time

starsky51

 

[Happy2]

Hi Steve,

When I removed the apostrophe from the line of code "rs.Edit", I still have the message "Method or data member not found". Do you know why? Please help. Thanks.

 

[Happy2]

Hi Steve and everyone,

My connection and increment of NextID work already. I am trying to put everything together and I will update with you all later. Thank you so much again for your time and efforts. Thank you.