Loop through a field for PO numbers and create new if not exists in ACCESS2010

I have a form that users enter information for Purchase Orders.  The PO number is a combination of the Vendor ID and date, plus a number starting with 1 for each purchase order.  So, a PO could be VendD91520151, with the last number incremented during the day by one if another user creates another purchase order.  However, am not sure how to approach checking for the most recent number.  The first PO number is easy to create but say there us a PO of  VendD91520151, what would be the simplest to check for the most recent number?  I need to anticipate multiple POs for the same vendor on the same day and increment each by one.  Below is as far as I have gotten.,

Dim strPOID As String
Dim i As Integer
Dim iCount As Integer
Dim strSelect As String
Dim iMonth As Integer
Dim iDay As Integer
Dim iYear As Integer
Dim strDate As String

iMonth = DatePart("m", Date)
iDay = DatePart("d", Date)
iYear = DatePart("yyyy", Date)
strDate = CStr(iMonth & iDay & iYear)

i = 1  'Start count for day for first Purchase Order for the vendor

strPOID = strVenID & strDate + i
'Need to see if any prior POs exist for the day and get the next number
iCount = DCount("POID", "tblTEMPPurchaseOrderDetails", "VenID = '" & strVenID & "' and POID = '" & strPOID & "' ")
If iCount = 0 Then
    strPOID = strPOID
ElseIf iCount > 0 Then
Sandra SmithRetiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
post a copy of the db.
Usually, you would run a query to get the current max value, or use the DMax() domain function.  If you get a Null result, then you construct a new PO for the date.  Otherwise, add 1 to the returned date.

I would caution you to use two digits for month and day.
Robert ShermanOwnerCommented:
I think a good question is whether this is a system already deployed and in active use, or is this something that is still under development?

A good answer will depend on how much potential flexibility there is for some fundamental design change.

Off the top, however:

- Is VendorID a numeric autoincrementing key field or something else?  Your example "VendD91520151" suggests that "VendD" is the Vendor ID.  If that is correct, is there a legacy reason for such a string-based vendor identifier?

- rather than creating a string POID (or perhaps in addition to), I think it might be a good idea to store the values that would make up this string separately.  Particularly, the last digit that you are using to distinguish between multiple purchase orders for a single vendor on a single day, should probably be a numeric field stored in the table.  This would allow you to create an index based on the combination of vendor, date, and dailynumber that you could specify as being a unique combination to prevent duplicates.  

- this has been mentioned, but I second that in a stored value for POID, you should use leading zero, double digit components for month and day (e.g. 01012015 for 1/1/2015).  Further, I often find that if I'm going to store dates in a string, it pays in the long run to order the components in YYYYMMDD order in the event you ever need to sort based on that string represenation of the date.

To sum up, based on what you have told us thus far, my advise would be to add a field for the numeric indicator for a single vendor's multiple order within a day.   You can still store the entire PO ID number in a string as well, but having that last digit stored on its own will prove valuable.   If you follow along with this, then searching for the next value becomes a simpler matter of getting using DMax() to find the highest number for a vendor/date combination, incrementing that number by 1, and immediately saving the record to the database.   That last bit is important if you have multiple users working in the system, so that you don't end up having two users entering data at the same time with the same POID.  

There's a little more to a final solution based on these suggestions, but before I go into some other things (like how to handle records that a user starts but then cancels and how you would have gaps in the numbering as a result, etc.. ) I will wait until knowing whether you are willing/able to make the fundamental changes first..
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

I would only add one thing to Robert's comment.  Your current key system limits you to 9 or 10 unique values.  If you have more POs from the same vendor, then your numbering scheme will break.
Robert ShermanOwnerCommented:
...what aikimark said.   Again, if it's a system under-development and flexible for design changes, you should at least consider making the numeric portion leading-zero based for 2 or more digits (e.g. 01, 02... 10 ... 99  OR 001 ... 010 ... 999)  

Another reason to consider also storing the number as a separate number field.   At least if you did that and ended up backed into a corner based on not enough digits in the POID string, you would have a way out by having the actual number stored.
Sandra SmithRetiredAuthor Commented:
It is under development.  Actually, a client of my husband's is looking to replace their current product tracking system and this is my first attempt at creating a possible saleable application for small companies that cannot afford $100K+ for specialized software.   They all have MS Office and thus already have ACCESS.  There won't be a limit for the number of characters for the PO as it is always a combination of the vendor's letter code, the current date and then a number of 1 to how many PO's they submit for that day.  Usually it is only one, but I want to be prepared in case there are multiple PO's on one day.  I like the idea of storing the single digit for checking, did not think of that.  Let me work on getting it into code. Frankly, any suggestions are going to be welcome as I have always created applications for internal departments, but this, once it is out there, will have to stand on its own and I want to make it the best that I can.  There will be future questions once this is resolved.  Be back tomorrow, thanks all.
Sandra SmithRetiredAuthor Commented:
Yes, there is a legacy reason to keep the Vendor id as it is.  Forgot to address that question,.
Robert ShermanOwnerCommented:
Ok, since we now know the system is still in development and that the underlying foundation is flexible, I’ll elaborate on what I began describing earlier.

Since you’re creating a complex identifier that needs to be unique for each record entered, you have to consider how to handle the assignment of the POID information in a multi-user environment.  (I’m assuming the application will be used by multiple users at the same time)
Since the POID needs to be created in code, you’ll have to decide at what point this happens.  You can either establish a POID at the beginning of the process (as the first thing that happens when a new purchase order form is opened for data entry) or at the end (as one of the last steps, after data has been entered and the user clicks a “save” button or moves to a new record).  

Before I go into my thoughts on which way to proceed, let’s review the table design and some code for generating a POID.  

My suggestion regarding the table design is that for each Purchase Order record, you store in the table:

VendorID (numeric long int)
Date (datetime)
VendorDateIndex (numeric int)

You’ll notice that for VendorID I am suggesting a numeric field, as I think you should consider having in your Vendors table:

VendorID (numeric long int Key Field)
VendorShortName (string)

What I’m suggesting here is to relate the tables by a numeric key field ‘behind the scenes’ and on user forms and reports you can then use the VendorShortName (which could probably use a better name) as the legacy identifier.   In the vendors table, you can create an index on that field so that it will be indexed and not allow duplicates.  

You COULD just use the legacy scheme as your identifier and key fields, and not introduce a new numeric ID into the scheme, but having numeric keys that don’t necessarily have any meaning outside of the database is the more standard design approach and will perform better as the database grows in size.

Your function for generating a new POID was pretty close to being a workable solution.  Here’s an updated version based on the table structures above, with the new numeric added to track multiple POs from the same vendor on a given date:

Dim strPOID As String
Dim iMax As Integer
Dim strDate As String
Dim VendorDateIndex as Integer

strDate = Format(Date, “yyyymmdd”) ‘ note that I changed the order here, which may not work based on your requirements

‘ strDate = CStr(iMonth & iDay & iYear)

‘ get max VendorDateIndex 
iMax = DMax("VendorDateIndex", "tblTEMPPurchaseOrderDetails", "VendorID = " & VendorID & " and PODate = '" & PODate & "' ")
If isNull(iMax) Then
    VendorDateIndex =1
   VendorDateIndex = iMax + 1
End If

strPOID = VendorShortName & strDate & Format(VendorDateIndex, “00”)

‘ TODO: set the values on the form for VendorDateIndex and POID

Open in new window

NOTE: The above code hasn't been tested, and may contain some syntax errors.  I think it should provide enough of an idea to get you going in the right direction.

Lastly, getting back to the issue of when to generate the POID, the potential problem is that while a user is working on entering the data for a PO, you don't want another user creating a record for the same vendor because, until the record being created by User 1 is saved, User 2 will get the same POID number since the DMax function will still not be aware of the User 1 record that is in progress.  The best approach may be to generate the POID (and VendorDateIndex) at the point where the user has already filled in the data and is finished editing the record, so probably in the BeforeUpdate event on the Form and you will need to add a test to the code that checks to see if the POID field is empty before the above code.  

Hopefully, all of the above makes sense.  It ended up being a lengthy post and it's late, so if something doesn't add up, please let me know and I'll follow up with a clearer head tomorrow. :D

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I didn't read all of Robert's response so I apologize if I repeat him.  I would suggest storing all the constituent pieces as separate columns.  First Normal form says that all fields must be atomic.  You will find it easy enough to concatenate columns to display as a single value but separating them is next to impossible if the fields are variable in length unless you use separators.  

If you store the fields separately, you are less likely to get into a bind due to field length changes.
Keeping the separate values makes joins possible.  It also makes it a simple matter to find the highest sequence number for issued POID's so you can increment it for the next one.
Sandra SmithRetiredAuthor Commented:
Wow, thanks for the advice.  I will need to redesign some things, but fortunately it can still easily be done.  Let me go through the suggestions, make sure I understand them and see how I can apply them.  I really do appreciate all the help as this is the first time I have ever attempted to create a saleable application, there will definitely be more questions as this goes along.
Sandra SmithRetiredAuthor Commented:
Thanks for the advice, it is more flexible than what I was doing.  The POID is assigned once the salesman has selected the scenario he likes, so it is created when he hits the save button for that particular set of data.  Fortunately, vendors are assigned to only one salesman each, but I think this will also work in case more than one salesman is working for a  vendor.  since this is aimed at small companies with limited staff, this should not be an issue, but just is case, better to be prepared.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.