Question 2 x Auto-Incrementing Fields

[gerryp]

Unsure as to which forum this issue should be located, so feel free to move it & Apologies in advance for the lack of proper terminology.

I have a simple PO database for our company’s I.T. purchases.

At the moment the Tbl_POrdershas PO_No as the primary key field and is set as Auto-number. The format is set to 19IT”000, and is set to increment as per this screenshot

Form :
After clicking new (blank) record, the P.O. Number text box changes to this

When a PO Date is entered, the PO Number text box displays the auto-incremented PO number, such as 19IT116.

(This is all good & it works perfectly)

Problem:
Our stores/procurement department want us to include another field (Stores_PO)
This should be a combination of their PO numbering format (650000, ) and our I.T. PO_No (PO_No) such as 6500001-19IT116

How do I facilitate the Stores_PO number??
That textbox must auto-populate , first with the auto-incremented number 65000001 etc and it has to contain our PO_number (19IT116) etc i.e 6500001-19IT116

 

[jdraw]

You should review this article on Autonumbers. Most consider an autonumber to be for use by the database management system, and not have relevance to the user. Not always true but applicable most of the time.
With database a fundamental concept is 1 fact is stored in 1 field.
You can always have atomic fields, and combine 2 or more for user consumption.

Good luck with your project.

 

[arnelgp]

create new table (stores_po), with one short text field (store_po).
add '650000' as first record.

modify your po table and add another po field (PO2, short text).
modify your form to show PO2, and hide PO (original).

on the Form's BeforeInsert event add some code to generate PO2 (see code behind the sample form).

copy Module1 to your db.

 

[Mark_]

As others have posted, your real autonumber should be used within your database, not be a "For display" value.

Your format, 19IT”000. looks to be the last to digits of the current year, a two digit department code, followed by three digits for a "PO" number. I'd suggest treating it as separate fields within your database and ONLY put it together when you need to.

For your second display value, what are the business rules for the first part of it? Who is expected to generate the "Store PO"? Is it something your users will need to enter or is it something you are expected to keep track of? Once more, it does look like simple concatenation.

 

[gerryp]

create new table (stores_po), with one short text field (store_po).
add '650000' as first record.

modify your po table and add another po field (PO2, short text).
modify your form to show PO2, and hide PO (original).

on the Form's BeforeInsert event add some code to generate PO2 (see code behind the sample form).

copy Module1 to your db.

Having done all of the above I get the attached errors ??

 

[arnelgp]

Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Form_BeforeInsert_Error
    Dim store_po As String, lngPO As Long
    ' get the next autonumber (see Module1)
    lngPO = NextAutonumber("po", "po") - 1

the first "po" is the table name (I think it will be "Tbl_POrders" on your part), the second "po" is the autonumber field ("PO_No").

Private Sub Form_BeforeUpdate(Cancel As Integer)
    'make double check for multi-user
    If Nz(DCount("1", "[COLOR="Blue"]Tbl_POrders[/COLOR]"), 0) > lngRecordCount Then
        ' if not same (somebody already saved new record,
        ' generate new PO2
        Call Form_BeforeInsert(0)
    End If
End Sub

 

[gerryp]

Down to one error MSG now....

 

[arnelgp]

I've already stated on my last post, you need to replace "po" with "tbl_POrders":

Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Form_BeforeInsert_Error
    Dim store_po As String, lngPO As Long
    ' get the next autonumber (see Module1)
    lngPO = NextAutonumber([COLOR="Blue"]"Tbl_POrders", "PO_No"[/COLOR]) - 1

 

[gerryp]

@gerryp,
You are getting conflicting advice from the people who have responded so far.

Please confirm that the "19" part of the PO number is actually the last two positions of year. Because if it is, arnel's solution will not work for you after the end of December.

"19" are the last 2 digits of the year.

The total yearly records (Purchase Orders) will never exceed 200 and I've been told, at years end, to make a copy of the the database, manually increment the year format for the next year's P.O's and compress it.

I'm sure it's possible to automate this but I'm afraid my coding / access knowledge is what it appears to be in this forum, very extremely limited to say the least , and that automation is for another post on this forum !!

 

[gerryp]

I've already stated on my last post, you need to replace "po" with "tbl_POrders":

Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Form_BeforeInsert_Error
    Dim store_po As String, lngPO As Long
    ' get the next autonumber (see Module1)
    lngPO = NextAutonumber([COLOR="Blue"]"Tbl_POrders", "PO_No"[/COLOR]) - 1

Indeed you did Arnel.

I looked at the code within Private Sub Form_BeforeInsert(Cancel As Integer) and found the code:

lngRecordCount = Nz(DCount("1", "PO"), 0)

I changed it to ;

lngRecordCount = Nz(DCount("1", "Tbl_POrders"), 0)

The error messages have stopped - brilliant

 

[gerryp]

Now that the error messages have been fixed, there's a problem with the combined PO numbers "P02" ( Exact & I.T. PO)

When a new record is entered the 1st part of the number (stores_po) does not increment but the IT PO number does -
as per the screenshot below of 2 new records;