Autogenerate number depending on team selected 1

[silvami]

I have a database where each record needs to have an autogenerated number in sequential order depending on the value in the "team" field. I have gotten most of the code worked out except for the part when the database is closed the variables are cleared and the next person who enters a record upon opening starts at number one again. How can I store the ending value after the DB is closed so that the next user who opens the DB gets the correct value? I have included my code thus far any suggestions would be greatly appreciated

Dim pintBenefits As Long
Dim pintClaims As Long
Dim pintCrossFunct As Long
Dim pintMembership As Long
Dim pintProvider As Long

Sub frmRequirements()

If Form_frmRequirements.cmbTeam = "" Then
Call MsgBox("Please enter a team before
proceeding",vbOKOnly, "Required Field!&quot
Else
If Form_frmRequirements.cmbTeam = "Benefits" Then
Form_frmRequirements.RequireNo = pintBenefits + 1
pintBenefits = Val(Form_frmRequirements.RequireNo)
Else
If Form_frmRequirements.cmbTeam = "Claims" Then
Form_frmRequirements.RequireNo = pintClaims + 1
pintClaims =
Val(Form_frmRequirements.RequireNo)
Else
If Form_frmRequirements.cmbTeam =
"Cross-Functional" Then
Form_frmRequirements.RequireNo =
pintCrossFunct + 1
pintCrossFunct =
Val(Form_frmRequirements.RequireNo)
Else
If Form_frmRequirements.cmbTeam
= "Membership" Then
Form_frmRequirements.RequireNo =
pintMembership + 1
pintMembership =
Val(Form_frmRequirements.RequireNo)
Else
If Form_frmRequirements.cmbTeam
= "Provider" Then
Form_frmRequirements.RequireNo =
pintProvider + 1
pintProvider =
Val(Form_frmRequirements.RequireNo)
End If
End If
End If
End If
End If
End If

End Sub



Thanks
Mike

 

[Darrylles]

Hi,

You haven't even mentioned database tables used etc.

You could use DLookup to find the last 'sequential' number used (wherever that is).

I would also use Case rather than If..Then..Else in this case (pardon the pun):

[tt]
select case Form_frmRequirements.cmbTeam

case ""
Call MsgBox("Please enter a team before proceeding",vbOKOnly, "Required Field!&quot

case "Benefits"
Form_frmRequirements.RequireNo = pintBenefits + 1
pintBenefits = Val(Form_frmRequirements.RequireNo)

case "Claims"
Form_frmRequirements.RequireNo = pintClaims + 1
pintClaims = Val(Form_frmRequirements.RequireNo)

case "Cross-Functional"
Form_frmRequirements.RequireNo = pintCrossFunct + 1
pintCrossFunct = Val(Form_frmRequirements.RequireNo)

case "Membership"
Form_frmRequirements.RequireNo = pintMembership + 1
pintMembership = Val(Form_frmRequirements.RequireNo)

case "Provider"
Form_frmRequirements.RequireNo = pintProvider + 1
pintProvider = Val(Form_frmRequirements.RequireNo)

end select
[/tt]

It makes it neater and more 'readable'.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[SgtJarrow]

thread181-541863 brought same subject to light....I provided an answer there and have the sample database still available. Send me an email to my work address with Access version and I will send you the sample.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com