Generating a number...simple but

[sparks]

ok I was generating a combo of things into a unique identifier.

Private Sub generatenumber()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StudentNumber As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Max(qryParticipantids.gennumber) AS
MaxofGenNUMBER FROM qryParticipantids;")
If rs.RecordCount > 0 Then
rs.MoveFirst
Me.GenNUMBER = rs!maxofGenNumber + 1
Me.StudyID = "S" & Format(Me.GenNUMBER, "0000")
End If
rs.Close
Set db = Nothing
End Sub

so they get a studyID sequence
S0001
S0002
S0003

this worked fine but now they want to get a studyID
S0501
S0502
S0503

how can I get this with an empty recordset.
since the IF is not really needed since you call this on new record
the count will always be 1.

thanks for any ideas...this is weird
why do they always want something that to me makes no since.

Jerry

[Tom van Stiphout]

On Fri, 24 Mar 2006 14:13:30 GMT, sparks <js******@swbell.net> wrote:

How about:
Me.GenNUMBER = rs!maxofGenNumber + 1
If Me.GenNumber < 501 then Me.GenNumber = 501
Me.StudyID = "S" & Format(Me.GenNUMBER, "0000")

-Tom.

ok I was generating a combo of things into a unique identifier.

Private Sub generatenumber()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StudentNumber As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Max(qryParticipantids.gennumber) AS
MaxofGenNUMBER FROM qryParticipantids;")
If rs.RecordCount > 0 Then
rs.MoveFirst
Me.GenNUMBER = rs!maxofGenNumber + 1
Me.StudyID = "S" & Format(Me.GenNUMBER, "0000")
End If
rs.Close
Set db = Nothing
End Sub

so they get a studyID sequence
S0001
S0002
S0003

this worked fine but now they want to get a studyID
S0501
S0502
S0503

how can I get this with an empty recordset.
since the IF is not really needed since you call this on new record
the count will always be 1.

thanks for any ideas...this is weird
why do they always want something that to me makes no since.

Jerry

[sparks]

No thats too easy.
What else you got.
LOL

thanks for the help I could not see that to save my life.
I was thinking that if would always be 501.

thanks again

Jerry


On Fri, 24 Mar 2006 07:21:16 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On Fri, 24 Mar 2006 14:13:30 GMT, sparks <js******@swbell.net> wrote:

How about:
Me.GenNUMBER = rs!maxofGenNumber + 1
If Me.GenNumber < 501 then Me.GenNumber = 501
Me.StudyID = "S" & Format(Me.GenNUMBER, "0000")

-Tom.

ok I was generating a combo of things into a unique identifier.

Private Sub generatenumber()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StudentNumber As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Max(qryParticipantids.gennumber) AS
MaxofGenNUMBER FROM qryParticipantids;")
If rs.RecordCount > 0 Then
rs.MoveFirst
Me.GenNUMBER = rs!maxofGenNumber + 1
Me.StudyID = "S" & Format(Me.GenNUMBER, "0000")
End If
rs.Close
Set db = Nothing
End Sub

so they get a studyID sequence
S0001
S0002
S0003

this worked fine but now they want to get a studyID
S0501
S0502
S0503

how can I get this with an empty recordset.
since the IF is not really needed since you call this on new record
the count will always be 1.

thanks for any ideas...this is weird
why do they always want something that to me makes no since.

Jerry