Hello everyone,
I inherited this database that is used by my department. Condition IDs are numbers such as 1.00005 1.00006, each number means something to someone, but nothing to me. The users have asked me to create routine that will resequence these numbers from specific spot. For example, I need to add a condtion between condition 1.00005 and 1.00006, so let's have a new condition inherit the 1.00006 number and every number after, that begins with 1 needs to be renumbered.
Stop when you get to number 2.
This is what I have so far: My two big problems:
* Can not get variables to work for the beginning and ending numbers (Right now I have actual numbers in there as placeholders)
* Can not get the records to Increase by 0.000001 (The numbers are either replaced by 0.00001 or they switch to increasing from 0.00000 instead of whatever numbers I have set as the begin and end numbers.
Code:
Private Sub Test_Click()
Dim dbs As DAO.Database
Dim rstConditions As DAO.Recordset
Dim strSQL As String
Dim intI As Integer
Dim LConditionID As Double
Dim LRound As Integer
Dim CurrentNumber As Double
LConditionID = InputBox("Please enter the first condition number that has to be resequenced.", "Renumber condtion IDS")
LRound = Round(LConditionID) + 1
On Error GoTo ErrorHandler
Set dbs = CurrentDb
'Open a recordset on all records from the Condition table that have
'a value that is equal to the input box.
strSQL = "SELECT * FROM Condition WHERE (((Condition.ConditionID)>=7.000001 And (Condition.ConditionID)<=8.000001));"
Set rstConditions = dbs.OpenRecordset(strSQL, dbOpenDynaset)
'If the recordset is empty, exit.
If rstConditions.EOF Then Exit Sub
intI = 1
With rstConditions
Do Until .EOF
.Edit
![ConditionID] = [ConditionID] + 0.000001
.Update
.MoveNext
intI = intI + 1
Loop
End With
rstConditions.Close
dbs.Close
Thanks in advance for any help.
Kim