Results 1 to 12 of 12
  1. #1
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116

    Prefix autonumber based on combo

    Hi People/Gurus

    Can anyone guide me or provide an example using a form in access on how to generate a prefixed sequential auto-number based on a selection from a combobox:



    Ie:

    If Selected = "Cabinet Shop" then CAB_00001

    If Selected = "Paintshop" then PNT_00001

    If Selected ="MetalShop" then MTL_00001

    Etc!
    Etc!

    Many Thanks in advance for any suggestions

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Attached db is one way:
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Quote Originally Posted by Bob Fitz View Post
    Attached db is one way:
    Thanks for the example, but doesn't seem to work with my version(access 2010), when I go to new record numbering 5 in your example I can't select an workshop from the combo ,any suggestions? on how to rectify
    thanks

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Sorry about that. Hopefully this one will work as required
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Bob. Many thanks, the example works a treat and also thanks for replying so fast.

  6. #6
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Quote Originally Posted by Bob Fitz View Post
    Sorry about that. Hopefully this one will work as required
    Hi Bob,

    I have one more question(not sure if I should start another thread or not, please advise). I love the autonumber sample you provided and as I said earlier it works a treat, though I am now trying to figure out the best way to incorporate this into my existing database, As I have many tables the one that I was hoping to use this with is my Form "frm_Orders" and "tbl_orders" table, so should I use your existing table and add/carry all the fields over from my "tbl_orders" table to the sample table you supplied/created and also copy all my fields from my "frm_orders" form, then delete my "tbl_orders" and "frm_orders" ...or import the tables you created in the sample you posted and somehow also reference as well as the ones I already reference through some sort of additional join to my already existing queries, so as I can make/generate a new record with an prefix-autonumber, or is there a easier way through using the form you created calling it as a popup when a new record is selected/created just to create the autonumber and then close?...I hate being such a novice at this ....any advice would be great! thanks in advance

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I don't really understand what you need to do now. Can you post a copy of your db
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116

    Prefixed Autonumber issue with invalid control source

    Quote Originally Posted by Bob Fitz View Post
    I don't really understand what you need to do now. Can you post a copy of your db
    I was attempting to get your original sample integrated into my existing database (and without much success), Although, I think I am nearly there now with it, I just have one problem with an "invalid control source" on my Form-Application_ID and can't seem to figure out why or whats wrong as it appears to work regardless of this error
    ...see attached for any suggestions I am more than grateful and many thanks for responding to me
    Attached Files Attached Files

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I just have one problem with an "invalid control source" on my Form-Application_ID and can't seem to figure out why or whats wrong as it appears to work regardless of this error
    This seems to be because of the formatting that you have applied in the table. If you remove that and add the prefix in the forms query the error disappears. This approach will also allow you to make the AutoNumber field the Primary Key for the table which is something it is sorely lacking at the moment. See the attached db.
    I'm personally not keen on the use of more than one table as the record source for a form. If I have time I will look into that further.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #10
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Quote Originally Posted by Bob Fitz View Post
    This seems to be because of the formatting that you have applied in the table. If you remove that and add the prefix in the forms query the error disappears. This approach will also allow you to make the AutoNumber field the Primary Key for the table which is something it is sorely lacking at the moment. See the attached db.
    I'm personally not keen on the use of more than one table as the record source for a form. If I have time I will look into that further.
    Thanks again, I downloaded the Sample.db you supplied and sure enough the above points you made are all valid ,which I totally agree with, with regards to the sample the only issue with it is I now can't create a new record from the combobox, not to worry I am sure I'll figure it out.

    Re-"I'm personally not keen on the use of more than one table as the record source for a form".

    If you have the time could you be a little more specific as what would be the correct or best alternative way to do this. As I plan to also reference a few other tables on the same form, such as Customers Contact & address details.

  11. #11
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    the only issue with it is I now can't create a new record from the combobox, not to worry I am sure I'll figure it out.
    Not sure I understand but does the attached file work now as required.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  12. #12
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Quote Originally Posted by Bob Fitz View Post
    Not sure I understand but does the attached file work now as required.
    All good now ...many thanks

    Re-"I'm personally not keen on the use of more than one table as the record source for a form".

    If you have the time could you be a little more specific as what would be the correct or best alternative way to do this. As I plan to also reference a few other tables on the same form, such as Customers Contact & address details.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-06-2016, 04:49 AM
  2. Custom Autonumber based on lookup
    By npresadbukka in forum Access
    Replies: 2
    Last Post: 07-08-2013, 10:36 PM
  3. Autofill based on Autonumber Field?
    By W.Chan in forum Access
    Replies: 2
    Last Post: 12-06-2012, 10:51 PM
  4. Custom Autonumber based on lookup
    By luce725 in forum Access
    Replies: 7
    Last Post: 08-26-2011, 01:49 PM
  5. Add prefix to AutoNumber
    By sirmilt in forum Database Design
    Replies: 3
    Last Post: 07-09-2010, 01:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums