By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

generating seq numbers ok till they decided to change it

P: n/a
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.OpenRecordset("SELECT Max(qryParticipantids.gennumber) AS
MaxofGenNUMBER FROM qryParticipantids;")
If Not IsNull(Me.Level) And Not IsNull(Me.Control) Then
If rs.RecordCount 0 Then
rs.MoveFirst
Me.GenNUMBER = rs!maxofGenNumber + 1
'gennum = rs!maxofGenNumber + 1
Debug.Print " level = " & Level
Debug.Print " control = " & Control
Debug.Print gennum
'finalnum = [Level] & [Control] & Format(gennum, "000")
'Me.StudyID = [Level] & [Control] & Format(Me.GenNUMBER, "000")
Me.StudyID = "1" & [Level] & [Control] & Format(Me.GenNUMBER, "000")
Debug.Print "finalnum = " & finalnum; ""
' Me.StudyID = "C" & Format(Me.GenNUMBER, "0000")
Debug.Print "StudyID = " & Me.StudyID
Debug.Print "genNumber = " & Me.GenNUMBER
rs.Close
End If
Set db = Nothing
End If
End Sub

================================================== ===========================
qryParticipantids 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(tblEnrollment.GenNUMBER) AS MaxOfGenNUMBER FROM
tblEnrollment GROUP BY tblEnrollment.Level HAVING
(((tblEnrollment.Level)=[me].[level]));

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

but if I do Set rs = ("SELECT Max(tblEnrollment.GenNUMBER) AS
MaxOfGenNUMBER FROM tblEnrollment GROUP BY tblEnrollment.Level HAVING
(((tblEnrollment.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

Jun 14 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.GenNUMBER, "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******@swbell.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.OpenRecordset("SELECT Max(qryParticipantids.gennumber) AS
MaxofGenNUMBER FROM qryParticipantids;")
If Not IsNull(Me.Level) And Not IsNull(Me.Control) Then
If rs.RecordCount 0 Then
rs.MoveFirst
Me.GenNUMBER = rs!maxofGenNumber + 1
'gennum = rs!maxofGenNumber + 1
Debug.Print " level = " & Level
Debug.Print " control = " & Control
Debug.Print gennum
'finalnum = [Level] & [Control] & Format(gennum, "000")
'Me.StudyID = [Level] & [Control] & Format(Me.GenNUMBER, "000")
Me.StudyID = "1" & [Level] & [Control] & Format(Me.GenNUMBER, "000")
Debug.Print "finalnum = " & finalnum; ""
' Me.StudyID = "C" & Format(Me.GenNUMBER, "0000")
Debug.Print "StudyID = " & Me.StudyID
Debug.Print "genNumber = " & Me.GenNUMBER
rs.Close
End If
Set db = Nothing
End If
End Sub

================================================= ============================
qryParticipantids 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(tblEnrollment.GenNUMBER) AS MaxOfGenNUMBER FROM
tblEnrollment GROUP BY tblEnrollment.Level HAVING
(((tblEnrollment.Level)=[me].[level]));

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

but if I do Set rs = ("SELECT Max(tblEnrollment.GenNUMBER) AS
MaxOfGenNUMBER FROM tblEnrollment GROUP BY tblEnrollment.Level HAVING
(((tblEnrollment.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

Jun 14 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.