Form to assign next #

  • Thread starter Thread starter Parker
  • Start date Start date
P

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
 
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
 
Back
Top