generating seq numbers ok till they decided to change it

[sparks]

we are generating numbers for the data collection people.

this worked fine for a while..

Private Sub generatenumber( )
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StudentNumber As Integer
Dim gennum As Integer
Dim finalnum As Integer
Set db = CurrentDb
Set rs = db.OpenRecordse t("SELECT Max(qryParticip antids.gennumbe r) AS
MaxofGenNUMBER FROM qryParticipanti ds;")
If Not IsNull(Me.Level ) And Not IsNull(Me.Contr ol) Then
If rs.RecordCount 0 Then
rs.MoveFirst
Me.GenNUMBER = rs!maxofGenNumb er + 1
'gennum = rs!maxofGenNumb er + 1
Debug.Print " level = " & Level
Debug.Print " control = " & Control
Debug.Print gennum
'finalnum = [Level] & [Control] & Format(gennum, "000")
'Me.StudyID = [Level] & [Control] & Format(Me.GenNU MBER, "000")
Me.StudyID = "1" & [Level] & [Control] & Format(Me.GenNU MBER, "000")
Debug.Print "finalnum = " & finalnum; ""
' Me.StudyID = "C" & Format(Me.GenNU MBER, "0000")
Debug.Print "StudyID = " & Me.StudyID
Debug.Print "genNumber = " & Me.GenNUMBER
rs.Close
End If
Set db = Nothing
End If
End Sub

=============== =============== =============== =============== =============== ==
qryParticipanti ds is a union query so I could get a number starting at
000
This is retarded the more I look at it

SELECT gennumber from tblEnrollment UNION select 0 as gennumber
FROM tlkprace;
=============== =============== =============== =============== =============== ==
Now they want to make it so that the numbers can be 1001 2001 3001
based on the level which is the first number then 001-0150 so 2001
would be level 2 001

so I thought about this for the selection

SELECT Max(tblEnrollme nt.GenNUMBER) AS MaxOfGenNUMBER FROM
tblEnrollment GROUP BY tblEnrollment.L evel HAVING
(((tblEnrollmen t.Level)=[me].[level]));

[me].[level] is from the forms.

but if I do Set rs = ("SELECT Max(tblEnrollme nt.GenNUMBER) AS
MaxOfGenNUMBER FROM tblEnrollment GROUP BY tblEnrollment.L evel HAVING
(((tblEnrollmen t.Level)=[me].[level]));")

i get too few parameters was expecting 1

I can't come up with a way to generate 001-150 by taking the max
number of the number with the level.

any help or pointers would be greatly appreciated.

thansk big time

[sparks]

well I thru all that stuff away and tried something simple

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
End If

Me.GenNUMBER = maxof + 1
Me.StudyID = "1" & [Level] & [Control] & Format(Me.GenNU MBER, "000")
this seems to work and I get good number generating.

I got 111001 and 111002 121001 121002 so I feel confident it will
work for a while

well I am sure someone will find a way to mess it up but so far so
good and its simple

On Thu, 14 Jun 2007 14:44:02 GMT, sparks <js******@swbel l.netwrote:

>we are generating numbers for the data collection people.

this worked fine for a while..

Private Sub generatenumber( )
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StudentNumber As Integer
Dim gennum As Integer
Dim finalnum As Integer
Set db = CurrentDb
Set rs = db.OpenRecordse t("SELECT Max(qryParticip antids.gennumbe r) AS
MaxofGenNUMB ER FROM qryParticipanti ds;")
If Not IsNull(Me.Level ) And Not IsNull(Me.Contr ol) Then
If rs.RecordCount 0 Then
rs.MoveFirst
Me.GenNUMBER = rs!maxofGenNumb er + 1
'gennum = rs!maxofGenNumb er + 1
Debug.Print " level = " & Level
Debug.Print " control = " & Control
Debug.Print gennum
'finalnum = [Level] & [Control] & Format(gennum, "000")
'Me.StudyID = [Level] & [Control] & Format(Me.GenNU MBER, "000")
Me.StudyID = "1" & [Level] & [Control] & Format(Me.GenNU MBER, "000")
Debug.Print "finalnum = " & finalnum; ""
' Me.StudyID = "C" & Format(Me.GenNU MBER, "0000")
Debug.Print "StudyID = " & Me.StudyID
Debug.Print "genNumber = " & Me.GenNUMBER
rs.Close
End If
Set db = Nothing
End If
End Sub

============== =============== =============== =============== =============== ===
qryParticipant ids is a union query so I could get a number starting at
000
This is retarded the more I look at it

SELECT gennumber from tblEnrollment UNION select 0 as gennumber
FROM tlkprace;
============== =============== =============== =============== =============== ===
Now they want to make it so that the numbers can be 1001 2001 3001
based on the level which is the first number then 001-0150 so 2001
would be level 2 001

so I thought about this for the selection

SELECT Max(tblEnrollme nt.GenNUMBER) AS MaxOfGenNUMBER FROM
tblEnrollmen t GROUP BY tblEnrollment.L evel HAVING
(((tblEnrollme nt.Level)=[me].[level]));

[me].[level] is from the forms.

but if I do Set rs = ("SELECT Max(tblEnrollme nt.GenNUMBER) AS
MaxOfGenNUMB ER FROM tblEnrollment GROUP BY tblEnrollment.L evel HAVING
(((tblEnrollme nt.Level)=[me].[level]));")

i get too few parameters was expecting 1

I can't come up with a way to generate 001-150 by taking the max
number of the number with the level.

any help or pointers would be greatly appreciated.

thansk big time