Generate Numbers To Automatically Assign To Combo Box 1

[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.

 

[tlbroadbent]

Do you want this "module" in SQL Server or in the client program that displays the combo box? If in SQL Server, which version are you running? If in the client, what is the client software?

What is the data type of the ID column? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Happy2]

Hi tlbroadbent,

My application is running on SQL Server. I have tables, views and stored procedures in the back-end. A form is a front-end application that links to the SQL Server.

I am running version 7.0
The data type of the ID column int or double, the one that will work for the case.

Thank you so much for your concerns and help.

 

[tlbroadbent]

So you have a front-end. And it is written in what? Is it Access? If Access, which version?

Do you want to write a SQL stored proc or VB code?

You'll probably want to use a decimal data type because int just won't do for 999.1.

Here is some code that should give you a starting point if you determine to use a SQL Stored Proc to create the new ID. You will need to modify it for your use. Let me know if you have any questions about the code.

--This code assumes that the table has
--a column named ID that will hold the
--calculated ID and another column named
--RecID that is an identity column.

Create Procedure uspGetNewID
--The ID wil be returned in the output
--vraible @id.
@id output As

--Insert a new row in the table
--with only the ID column
Insert Table_Name(ID)
Select
--Use the case function to help
--calcualte the next ID value
Case
--If the ID is integer and less than 999, add 1
When ID=Convert(Int, ID) And ID<999 Then ID+1
--If whole number part of ID=999 then
--add 1.1 to the decimal part
When Convert(Int, ID)=999 Then 1.1+ID-Convert(Int, ID)
--For any other values add 1 to the ID
Else ID+1
End
From Table_Name
--Select row with max RecID value
Where RecID=(Select Max(RecID) From Table_Name)

--Get New value from table
Select @id=ID
From Table_Name
Where RecID=(Select Max(RecID) From Table_Name)

go

Declare @id decimal(6,2)

--Execute the stored proc
Exec uspGetNewID @id output

--Display the new ID
Select ID=@id
Go Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[mgriffith]

is you are assigning it to a combo, i'd assume it has to be an integer...why don't you just take 10 * the column value? mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu