Form to assign next #

[Parker]

I'm trying to create a form that will automatically assign
the next available run number, but I have three possible
run numbers to choose from. On Open, I want to prompt the
user with the following question: "Run, C-Card or Pull
From Stock?" (Possibly a list box) and then select the
next available number based on the response. (C-Card = 1-
799, PFS = 800-999, Run = 1000-9999). I have three tables
for this info - one for each with the next # (NxtRunNo,
NxtCCardNo, NxtPFSNo) which would have to advance After
Update. The main table that I am updating has some basic
info about the run.

Is there a way to accomplish this? Do I need a different
Table setup?

I'm running on Access 2000. I have a decent knowledge of
Access, but I'm new to the programming side.

Thanks in advance for any help!

Parker

 

[Graham R Seach]

Parker,

I'd have all three numbers in the same table, albeit in different columns
named "LastC-CardNo", "LastPFSNo", and "LastRunNo". Only record the last of
each number used.

To return the next number, based on the user-selection:
Public Function GetNextNumber(sSelection As String) As Long
Me!txtRunNumber = Nz(DLookup("Last" & sSelection & "No",
"tblMyTable"), 0) + 1
'sSelection can be "C-Card", "PFS", or "Run"
End Function

Then in the form's AfterInsert event, add the following:
Dim sSQL As String

Select Case Val(Me!txtRunNumber)
Case 1 To 799 : sSQL = "LastC-CardNo"
Case 800 To 999 : sSQL = "LastPFSNo"
Case 1000 To 9999 : sSQL = "LastRunNo"
Case Else
DoCmd.Beep
MsgBox "This number is not catered for"
Exit Sub
End Select

sSQL = "UPDATE tblMyTable SET " & sSQL & " = " & sSQL & " + 1"
CurrentDb.Execute, dbFailOnError

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html