renerating numbers BUT what about deleted

[sparks]

I was generating some numbers for a new database. Its based on some
entries the people do and it worked fine except for deleted records.

Private Sub generate()
Dim rst As Recordset
Dim maxof As Integer
Set rst = Me.RecordsetClone
If Not IsNull(Me.Level) And Not IsNull(Me.Control) Then
maxof = 0
If rst.RecordCount 0 Then
rst.MoveFirst
Do Until rst.EOF
If maxof < rst!GenNUMBER And rst!Level = Me.Level Then
maxof = rst!GenNUMBER
End If
rst.MoveNext
Loop
Me.GenNUMBER = maxof + 1
End If
If maxof = 0 Then Me.GenNUMBER = 1
Me.StudyID = "1" & [Level] & [Control] & Format(Me.GenNUMBER, "000")
rst.Close

End If
End Sub

this will go in find the MAX gennumber and just add 1.

but it you have 4 5 7 it will NOT go in and put in the 6 .

I said look you don't want that what if that number some how has to be
added BACK IN later, you assigned that number to a record then deleted
the record..

but they insist to fill in the list,, ok

I thought about in the loop adding something like

if isnull(rst!gennumber) then temp = maxof +1

and then using temp as the generate number but I am worried about one
thing...HOW can I test this to make sure in all instances?

does this sound like a good way to find missing numbers and generating
fillers into the missing ones (IF they exist)

yes the big problem in my mind is there are 3 different levels

1011 1012 1013
2011 2012 2013
3011 3012 3013

so I can't expect to end the loop on any certain value..

[paii, Ron]

"sparks" <js******@swbell.netwrote in message
news:bn********************************@4ax.com...

I was generating some numbers for a new database. Its based on some
entries the people do and it worked fine except for deleted records.

Private Sub generate()
Dim rst As Recordset
Dim maxof As Integer
Set rst = Me.RecordsetClone
If Not IsNull(Me.Level) And Not IsNull(Me.Control) Then
maxof = 0
If rst.RecordCount 0 Then
rst.MoveFirst
Do Until rst.EOF
If maxof < rst!GenNUMBER And rst!Level = Me.Level Then
maxof = rst!GenNUMBER
End If
rst.MoveNext
Loop
Me.GenNUMBER = maxof + 1
End If
If maxof = 0 Then Me.GenNUMBER = 1
Me.StudyID = "1" & [Level] & [Control] & Format(Me.GenNUMBER, "000")
rst.Close

End If
End Sub

this will go in find the MAX gennumber and just add 1.

but it you have 4 5 7 it will NOT go in and put in the 6 .

I said look you don't want that what if that number some how has to be
added BACK IN later, you assigned that number to a record then deleted
the record..

but they insist to fill in the list,, ok

I thought about in the loop adding something like

if isnull(rst!gennumber) then temp = maxof +1

and then using temp as the generate number but I am worried about one
thing...HOW can I test this to make sure in all instances?

does this sound like a good way to find missing numbers and generating
fillers into the missing ones (IF they exist)

yes the big problem in my mind is there are 3 different levels

1011 1012 1013
2011 2012 2013
3011 3012 3013

so I can't expect to end the loop on any certain value..

Maybe off the wall, but what if you have a 2nd table listing all the
assigned numbers. You could then use a unmatched query to find any
previously used numbers.

[sparks]

well I got the idea of making a small to the point list and sorting it
since the sort I hope will make this easier.
If you read thru a table like I did and have deletes and adds you will
have something like 001 004 002 003

so I got the idea of doing this
Dim strSQL As String
' Dim rst As Recordset
strSQL = "Select * From tblEnrollment Where rst!Level = Me.Level " &
"Order By rst!GenNUMBER"

Debug.Print strSQL
' 'open the results read-only
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount 0 Then
rst.MoveFirst
Do While Not rst.EOF
Debug.Print rst!GenNUMBER
rst.MoveNext
Loop
End If

but I get 2 few parameters on the set rst line.... requires 3 ???
it must be something in my strsql statement but not sure
On Mon, 25 Jun 2007 09:03:58 -0500, "paii, Ron" <pa**@pack.comwrote:

>
"sparks" <js******@swbell.netwrote in message
news:bn********************************@4ax.com.. .

>I was generating some numbers for a new database. Its based on some
entries the people do and it worked fine except for deleted records.

Private Sub generate()
Dim rst As Recordset
Dim maxof As Integer
Set rst = Me.RecordsetClone
If Not IsNull(Me.Level) And Not IsNull(Me.Control) Then
maxof = 0
If rst.RecordCount 0 Then
rst.MoveFirst
Do Until rst.EOF
If maxof < rst!GenNUMBER And rst!Level = Me.Level Then
maxof = rst!GenNUMBER
End If
rst.MoveNext
Loop
Me.GenNUMBER = maxof + 1
End If
If maxof = 0 Then Me.GenNUMBER = 1
Me.StudyID = "1" & [Level] & [Control] & Format(Me.GenNUMBER, "000")
rst.Close

End If
End Sub

this will go in find the MAX gennumber and just add 1.

but it you have 4 5 7 it will NOT go in and put in the 6 .

I said look you don't want that what if that number some how has to be
added BACK IN later, you assigned that number to a record then deleted
the record..

but they insist to fill in the list,, ok

I thought about in the loop adding something like

if isnull(rst!gennumber) then temp = maxof +1

and then using temp as the generate number but I am worried about one
thing...HOW can I test this to make sure in all instances?

does this sound like a good way to find missing numbers and generating
fillers into the missing ones (IF they exist)

yes the big problem in my mind is there are 3 different levels

1011 1012 1013
2011 2012 2013
3011 3012 3013

so I can't expect to end the loop on any certain value..

Maybe off the wall, but what if you have a 2nd table listing all the
assigned numbers. You could then use a unmatched query to find any
previously used numbers.

[DavidB]

On Jun 25, 8:10 am, sparks <[e-mail]:

I was generating some numbers for a new database. Its based on some
entries the people do and it worked fine except for deleted records.

Private Sub generate()
Dim rst As Recordset
Dim maxof As Integer
Set rst = Me.RecordsetClone
If Not IsNull(Me.Level) And Not IsNull(Me.Control) Then
maxof = 0
If rst.RecordCount 0 Then
rst.MoveFirst
Do Until rst.EOF
If maxof < rst!GenNUMBER And rst!Level = Me.Level Then
maxof = rst!GenNUMBER
End If
rst.MoveNext
Loop
Me.GenNUMBER = maxof + 1
End If
If maxof = 0 Then Me.GenNUMBER = 1
Me.StudyID = "1" & [Level] & [Control] & Format(Me.GenNUMBER, "000")
rst.Close

End If
End Sub

this will go in find the MAX gennumber and just add 1.

but it you have 4 5 7 it will NOT go in and put in the 6 .

I said look you don't want that what if that number some how has to be
added BACK IN later, you assigned that number to a record then deleted
the record..

but they insist to fill in the list,, ok

I thought about in the loop adding something like

if isnull(rst!gennumber) then temp = maxof +1

and then using temp as the generate number but I am worried about one
thing...HOW can I test this to make sure in all instances?

does this sound like a good way to find missing numbers and generating
fillers into the missing ones (IF they exist)

yes the big problem in my mind is there are 3 different levels

1011 1012 1013
2011 2012 2013
3011 3012 3013

so I can't expect to end the loop on any certain value..

Whats the purpose for these numebrs? What are they to be used for/as?