none
Auto Gen Purchase Order Number RRS feed

  • Question

  • Im working on a scheme to auto generate my own PO Numbers based on criteria specific to inputted info into certain text boxes in my form. "WHAT?!?!?!"

    ok so I have a text box that that accepts your Project Number, manually inputted.

    another txt box off to the side steals that info and trims it ...the PONumber is the first "x" amount of characters of the proj number...

    ..EASY

    then I have a vendor combo txt box, choose your vendor from the dropdown so on so on...

    each vendor has an associated three letter abbreviation in the parent table that is displayed in another txt box off to the side... choose the vendor and the abbrev displays (will be turned not visible in the future)...

    ...EASY

    The issue(s) that im having is ...

    1.smartly concatenating or combining both the trimmed project number and the vendor abbreviation to efficiently populate my tables

    2.the last part of the PO Number is a two digit integer that is just the "2nd or 3rd or 4th PO for that vendor for that day" im thinking of a case statement something like...

    select case Supplier

       case is [supplier control]

            if count [number of occurances] = 0 then

               [PO NUmber Suffix] = [number of occurances] +1

            else

               [PO NUmber Suffix] = [number of occurances] +1

    Would any one have any experience with auto generating numbers. Or if you have any hints or inputs id love to hear it.

    As a disclaimer, the above scratch is not legit code. PLEASE don't tell me its wrong because I know. I hope it makes the point of which Im trying to accomplish.

    Tuesday, July 31, 2018 5:43 PM

Answers

  • Ken, so if I have what you said correctly, I am only populating fields (in forms) linked to tables then turning around and concatenating them via query. (Thinking my problem HERE is concatenating in the form) Im getting mostly circular reference errors.

    Whether you concatenate the values in a computed column in a query or in a computed control in a form is immaterial.  The important point is that you only store the sequential number per vendor per project, not the whole of the structured purchase order number.  With the sequential number stored in a column of integer number data type you can concatenate the substring from the project number and the vendor abbreviation with the sequential number formatted as "00".  Storing the resulting structured PO number is therefore unnecessary, and more importantly, unsafe, as it would open the door to update anomalies.

    In my CustomNumber demo the sequential number is on the basis of one column only, Gender, so is generated in the gender control's AfterUpdate event procedure.  In your case, as it's on the basis of two values it would be better to do so in the form's BeforeUpdate event procedure.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, August 6, 2018 5:51 PM Typo corrected.
    • Marked as answer by jshot Thursday, August 9, 2018 6:41 PM
    Monday, August 6, 2018 5:50 PM

All replies

  • You should simply be able to use DMax() to get the current maximum value for that project and vendor and then add 1 to get the next one in the sequence.

    NZ(DMax(), 0) + 1



    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, July 31, 2018 6:03 PM
  • That makes a lot of sense... I will def give this a shot. thanks Daniel.
    Tuesday, July 31, 2018 6:10 PM
  • In a multi-user environment you would need to protect against conflicts when two or more users are inserting a row simultaneously.  You'll find an example in CustomNumber.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the option for 'Sequential Numbering by Group' is the appropriate model.  The group in the demo is gender, in your case it would be project and vendor.  If there is a conflict in getting the next number, code in the form's module increments the number until no conflict occurs.  A conflict is detected by the violation of the table's composite primary key, which is made up of the Gender column and the NameID column which holds the sequential numbers per gender.

    Even if you are operating the database in a single user environment, and thus do not need to trap the error in this way, your table should have a composite key, which can either be the primary key, or, if it already has a 'surrogate' primary key, the inclusion of the project, vendor and sequential number columns in a single unique index as a candidate key.

    Note that you should not store the truncated project and vendor values as part of the purchase order number as that introduces redundancy and the consequent risk of update anomalies.  Those values should be concatenated to the sequential number in a computed column in a query, or a computed control in a form or report.

    Ken Sheridan, Stafford, England

    Wednesday, August 1, 2018 4:22 PM
  • Thank you  Mr Ken. I will follow your advise and get back to you as soon as possible.

    Thank you Mr Daniel for your input. It did not work but I am appreciative of your input, sincerely.

    jshot

    Wednesday, August 1, 2018 5:36 PM
  • "It did not work" is kind of vague.  You'd need to give more details of what problem you encountered for us to be able to guide you.  I use this very technique in my own databases, so it does work.  The trick is to force a save as soon as you generate the number to avoid another person generating the same number.  You can also perform validations using DLookUp ...

    I'm also sure that Ken's sample database is another excellent source to learn from!  Anything coming from Ken is top notch.


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, August 1, 2018 9:58 PM
  • sorry guys. Been out of the office.

    Daniel, Youre right it is vague. Im still new at this whole access thing and try to humble myself whenever possible. Thank you. The issue that im getting is when I run the "Max +1" code to grab max value of column "PONumber" it always returns a "1" suffix. Which tell me the code DOES work... so I misspoke, apologies. So the error is in how it searches the table???? Maybe a table?????

    no offense intended anywhere. And I hold any and all recommendations in high regard. No exceptions.

    So ive been using dcount and or dmax interchangeably tweaking as I can to see what im doing wrong. As of recently I am close with the Dmax as said above.

    Ken, so if I have what you said correctly, I am only populating fields (in forms) linked to tables then turning around and concatenating them via query. (Thinking my problem HERE is concatenating in the form) Im getting mostly circular reference errors.

    Monday, August 6, 2018 5:12 PM
  • Ken, so if I have what you said correctly, I am only populating fields (in forms) linked to tables then turning around and concatenating them via query. (Thinking my problem HERE is concatenating in the form) Im getting mostly circular reference errors.

    Whether you concatenate the values in a computed column in a query or in a computed control in a form is immaterial.  The important point is that you only store the sequential number per vendor per project, not the whole of the structured purchase order number.  With the sequential number stored in a column of integer number data type you can concatenate the substring from the project number and the vendor abbreviation with the sequential number formatted as "00".  Storing the resulting structured PO number is therefore unnecessary, and more importantly, unsafe, as it would open the door to update anomalies.

    In my CustomNumber demo the sequential number is on the basis of one column only, Gender, so is generated in the gender control's AfterUpdate event procedure.  In your case, as it's on the basis of two values it would be better to do so in the form's BeforeUpdate event procedure.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, August 6, 2018 5:51 PM Typo corrected.
    • Marked as answer by jshot Thursday, August 9, 2018 6:41 PM
    Monday, August 6, 2018 5:50 PM
  • Well, success! The end result isnt exactly like what you had (Ken). I did adopt some code and methods and I will share below. I used DMax because it rox. and works like a champ.  So again from my previous post, naïve and inexperienced I still am sooooooo.......

    so any way heres the code and explantation. quite simple

    tbl.suppliers has a field, SuppAbbr, with a three digit supplier code ("SuffixA"of PO#)

    tbl.project has a field, ProjNumAbbr, with the trimmed project number ("Prefix" of of PO#, calculated format)

    tbl.Purchase orders has fields

    in the form... manual inputs for Project number and Supplier (auto populates arrowed controls) auto concatenates via custom field in record source (PONumAbbr and shoves it to a dedicated cell (PONumAbbrev>>saves to table))

    [Code] for PONumSeq saves to table.

    Table concatenates PONumAbbrev & PONumSeq and spits it out as my fully auto generated PO Number (title block).

     

    Thursday, August 9, 2018 6:39 PM
  • Thank you a million times over for yalls help and suggestions. Sincerely appreciated.
    Thursday, August 9, 2018 6:41 PM