You are here:

Using MS Access/text in automunber determined by value in another control

Advertisement


Question
Hi there,
Just wondering if I can add text to an autonumber field kind of like a default value based on criteria.  For example, If I purchase something from province A, I would like the the field to show: A-0001.  (The format of the autonumber field has been set to 0000 for the purpose of showing 4 digits.)  I know I can simply type in "A"-0001 to show a format in the field, but if I choose to purchase something from province B, then I would like that field to show: B-0001.  I was thinking I could have a control on the form that would allow the user to choose the province, then the autonumber field would show the letter that represents the province that has been chosen.  Is this possible?  If so, how do I achieve this?  Thanks so much for your help.  It is appreciated as always.  Have a great day!! Carla  :)

Answer
Each field in a table should be a separate piece of info. This is part of the first law of normalization. So yes, you would add a field for province and you could display the combination using an expression like:

=Province & "-" & Format(autonumberfield,"0000")


However, if you want to number each province separately this will not work, since you cannot have duplicate autonumbers. So if you want one record to show B-0001 and another to show A-0001, you cannot use an autonumber.

In my blog I have an article on Sequential Numbering which discusses just how to do numbering within a grouping. Please review the blog for details.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.