Question 2 x Auto-Incrementing Fields (1 Viewer)

gerryp · Oct 2, 2019

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 · Oct 2, 2019

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 · Oct 2, 2019

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_ · Oct 2, 2019

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 · Oct 14, 2019

arnelgp said:
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 · Oct 14, 2019

Code:

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").

Code:

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 · Oct 14, 2019

Down to one error MSG now....

arnelgp · Oct 14, 2019

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

Code:

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

Pat Hartman · Oct 14, 2019

@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.

gerryp · Oct 15, 2019

Pat Hartman said:
@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 · Oct 15, 2019

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

Code:

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 · Oct 15, 2019

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;

Pat Hartman · Oct 15, 2019

What you have settled on will NOT work if any records are deleted. Using DMax() to get the highest assigned value does work. That works regardless of whether or not deletes are allowed.

Embedding leading or trailing values in a field is not good practice. Also, using the autonumber will not let you restart the numbering each new year.

Here's an example that might be closer to what you need.

The form generates YearNum as soon as you type something into the form. It assumes the current year but you can type over the generated value.

It is not customized to your situation since it is intended to be useful to anyone with a similar problem, but it's close. The point is that the year and the sequence number should be separate fields. You can add another field that holds the concatenated number. Just add another line in the form's BeforeUpdate event to populate it. Just make sure it goes AFTER the code that generates the sequence number.

Me.FullID ="6500001-" & Right(Me.txtYearNum, 2) & "IT" &Format( Me.SeqNum, "000")

The sample uses the DeptName in place of "IT" since that is how it would work in the real world

Question 2 x Auto-Incrementing Fields (1 Viewer)

gerryp

Registered User.

jdraw

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

Attachments

Mark_

Longboard on the internet

gerryp

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

gerryp

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

Pat Hartman

Super Moderator

gerryp

Registered User.

gerryp

Registered User.

gerryp

Registered User.

Pat Hartman

Super Moderator

Attachments

Similar threads

Users who are viewing this thread