Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-02-2019, 02:09 AM   #1
gerryp
Newly Registered User
 
Join Date: May 2007
Posts: 32
Thanks: 28
Thanked 0 Times in 0 Posts
gerryp is on a distinguished road
2 x Auto-Incrementing Fields

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








gerryp is offline   Reply With Quote
Old 10-02-2019, 02:51 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,242
Thanks: 94
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: 2 x Auto-Incrementing Fields

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
gerryp (10-02-2019)
Old 10-02-2019, 07:14 AM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: 2 x Auto-Incrementing Fields

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.
Attached Files
File Type: zip po.zip (56.5 KB, 8 views)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
gerryp (10-02-2019)
Old 10-02-2019, 07:35 AM   #4
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,054
Thanks: 20
Thanked 382 Times in 375 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: 2 x Auto-Incrementing Fields

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.
Mark_ is online now   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
gerryp (10-02-2019)
Old 10-14-2019, 06:14 AM   #5
gerryp
Newly Registered User
 
Join Date: May 2007
Posts: 32
Thanks: 28
Thanked 0 Times in 0 Posts
gerryp is on a distinguished road
Re: 2 x Auto-Incrementing Fields

Quote:
Originally Posted by arnelgp View Post
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 ??
gerryp is offline   Reply With Quote
Old 10-14-2019, 06:22 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: 2 x Auto-Incrementing Fields

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", "Tbl_POrders"), 0) > lngRecordCount Then
        ' if not same (somebody already saved new record,
        ' generate new PO2
        Call Form_BeforeInsert(0)
    End If
End Sub
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
gerryp (10-14-2019)
Old 10-14-2019, 06:40 AM   #7
gerryp
Newly Registered User
 
Join Date: May 2007
Posts: 32
Thanks: 28
Thanked 0 Times in 0 Posts
gerryp is on a distinguished road
Re: 2 x Auto-Incrementing Fields

Down to one error MSG now....
Attached Files
File Type: docx One Error MSG.docx (74.5 KB, 10 views)

gerryp is offline   Reply With Quote
Old 10-14-2019, 07:22 AM   #8
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: 2 x Auto-Incrementing Fields

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("Tbl_POrders", "PO_No") - 1
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
gerryp (10-15-2019)
Old 10-14-2019, 10:41 AM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,272
Thanks: 15
Thanked 1,595 Times in 1,515 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: 2 x Auto-Incrementing Fields

@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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
gerryp (10-15-2019)
Old 10-15-2019, 12:54 AM   #10
gerryp
Newly Registered User
 
Join Date: May 2007
Posts: 32
Thanks: 28
Thanked 0 Times in 0 Posts
gerryp is on a distinguished road
Re: 2 x Auto-Incrementing Fields

Quote:
Originally Posted by Pat Hartman View Post
@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 is offline   Reply With Quote
Old 10-15-2019, 02:37 AM   #11
gerryp
Newly Registered User
 
Join Date: May 2007
Posts: 32
Thanks: 28
Thanked 0 Times in 0 Posts
gerryp is on a distinguished road
Re: 2 x Auto-Incrementing Fields

Quote:
Originally Posted by arnelgp View Post
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("Tbl_POrders", "PO_No") - 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 is offline   Reply With Quote
Old 10-15-2019, 02:59 AM   #12
gerryp
Newly Registered User
 
Join Date: May 2007
Posts: 32
Thanks: 28
Thanked 0 Times in 0 Posts
gerryp is on a distinguished road
Re: 2 x Auto-Incrementing Fields

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;


Last edited by gerryp; 10-15-2019 at 05:07 AM.
gerryp is offline   Reply With Quote
Old 10-15-2019, 01:36 PM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,272
Thanks: 15
Thanked 1,595 Times in 1,515 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: 2 x Auto-Incrementing Fields

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
Attached Files
File Type: zip CustomSequenceNumber.zip (66.0 KB, 5 views)

__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 10-15-2019 at 01:50 PM.
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
gerryp (10-15-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Incrementing Numbers! vdanelia Forms 12 03-07-2011 11:55 AM
Auto-incrementing field dmctiernan04 General 7 10-26-2007 02:36 AM
Auto Incrementing a field without using Autonumber MrSmith Tables 28 09-01-2006 06:03 PM
auto incrementing numbers? BFprout General 3 05-20-2005 10:08 AM
Auto Number no incrementing TJBernard Tables 2 06-27-2003 11:45 AM




All times are GMT -8. The time now is 06:52 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World