how to do sequential numbering for Purchase Order in Access 2003

[carau]

Im useing Office Professional 2003 and the access database program and am
trying to create my own Purchase Order database/form. I need to also be able
to print each Purchase Order from time to time. I would like it to
automatically assign each PO its own number each time a PO is written,
however each PO must began with the same 3 digits every time. (example:
240-###1 then the next would be 240-###2 and so on)

 

[Jeanette Cunningham]

Hi,
Have a separate table with one field only that is a long integer and one
record only.
Each time you want to create the PO number, you create the number by using
240-### and concatenating it with the next highest number from that table.
You use a DLookup on that table to get the last used number, then add 1 to
it to get the number you need.
When you have finished and saved that PO number in your form, then you will
update that table and set its one and only record to the number you just
used.
Next time you do the DLookup on that table, you will see that last number
used and can just add 1 to it.

Jeanette Cunningham

 

[Dale Fye]

If every PO contains the "240-", then I would just drop that from the data,
and display it as part of the label for the PO. Then, you could just use an
autonumber field for your actual PO number field.

If, however, the prefix of your PO numbers change based on the year or some
other value, then, I would recommend two fields. One to track the prefix and
another for the suffix. If you do it this way, then I would make my PO#
field a long integer (rather than an autonumber) and use a technique similar
to that described by Jeanette to get the value of that field each time you
create a new PO.

HTH
Dale

 

[BruceM]

I would avoid autonumber for this, since it will almost invariably create
gaps in the numbering. One way to simulate an autonumber, but without gaps,
is shown here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
There are other options, such as VBA, using the principle outlined in the
example.
If the 240 is constant, I agree with the response that suggested
concatenating the number as needed. If the 240 is changeable, you will need
to state the rule by which it changes.

 

[Arvin Meyer [MVP]]

Im useing Office Professional 2003 and the access database program and am
trying to create my own Purchase Order database/form. I need to also be
able
to print each Purchase Order from time to time. I would like it to
automatically assign each PO its own number each time a PO is written,
however each PO must began with the same 3 digits every time. (example:
240-###1 then the next would be 240-###2 and so on)

The following code will look at the value in your table then add 1 to the
maximum value. The PO Number is a real number but it's formatted the way you
want in the form. In the printed PO, you'll need to either use the value in
the form, or recreate the formatting from the underlying PO number.

Function GetPO() As Long
On Error GoTo Error_Handler

Dim rstID As DAO.Recordset
Dim lngMaxID As Long
Dim db As DAO.Database

Set db = CurrentDb

Set rstID = db.OpenRecordset("Select Max(PONumber) As MaxID FROM tblPO")
If IsNull(rstID!MaxID) Then
'no records yet, start with one
lngMaxID = 1
Else
lngMaxID = rstID!MaxID + 1
End If

GetPO = lngMaxID

Me.txtPONumber = "240-" & Format(lngMaxID, "0000")

Exit_Here:
rstID.Close
Set rstID = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Function