By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

How to set a field with a fixed character followed by running increase number

P: 48
I am using Access 2000. I have few tables with primary keys, for example, StaffID, OrgID, CourseID and BookingID. In order for me to recognize my IDs easily later, I want to set a fixed character in front of the auto running number, for example, S for Staff, O for Org, C for course and B for Booking followed by the running increase number everytime I enter a record. How and what should I do to get the result I want?
Sep 14 '07 #1
Share this Question
Share on Google+
5 Replies


JConsulting
Expert 100+
P: 603
I am using Access 2000. I have few tables with primary keys, for example, StaffID, OrgID, CourseID and BookingID. In order for me to recognize my IDs easily later, I want to set a fixed character in front of the auto running number, for example, S for Staff, O for Org, C for course and B for Booking followed by the running increase number everytime I enter a record. How and what should I do to get the result I want?

a new text field next to your autonumber field to hold the value gathered from using a function like this one.

Expand|Select|Wrap|Line Numbers
  1. Function UpdateCounter() As String
  2. Dim myVal
  3. myVal = DMax("ID", "tblTest")
  4. UpdateCounter = "Test" & myVal
  5. End Function
  6.  
You can set this function as the default value for this field in any form or query.
another option would be what MS suggests
http://support.microsoft.com/kb/q140908/

J
Sep 14 '07 #2

P: 48
Hi J,

Thanks for the code. I had created the function in module as you suggested, however, I do not know how to call the function to my table, forms or queries. I tried the link that you gave but it is not clear enough for me to use the function. Would appreciate if you can show me (or any other link?) how to call the function I created. Thanks again.
Sep 14 '07 #3

JConsulting
Expert 100+
P: 603
Hi J,

Thanks for the code. I had created the function in module as you suggested, however, I do not know how to call the function to my table, forms or queries. I tried the link that you gave but it is not clear enough for me to use the function. Would appreciate if you can show me (or any other link?) how to call the function I created. Thanks again.

you won't be able to do it directly from the table. you can however use it as the default value on a form. If you have that "extra" field in your table already,

in the default value property you put =Functionname()

If you already have values in the table, and you need to backfill, you need to run an update query where you can update the field to "Text" & [ID] and it will put the value in the older records. The default will then automatically put the new value into the field when you add a new record.
J
Sep 14 '07 #4

P: 48
Hi J,

I shall find out more about function and then use it in tables, forms and query. It looks complicated for me as I am not familiar with module.
However,found one easier solution from someone. In table, set data type to AutoNumber, then go to Format property, type \S#. In my case, S stand for StaffID and it will auto increase number with a S in front. I am now trying to find out how to make it two digit, so that it will add S01, S02 instead of S1, S2..etc. Any idea?
Sep 17 '07 #5

P: 48
Hi J,

I shall find out more about function and then use it in tables, forms and query. It looks complicated for me as I am not familiar with module.
However,found one easier solution from someone. In table, set data type to AutoNumber, then go to Format property, type \S#. In my case, S stand for StaffID and it will auto increase number with a S in front. I am now trying to find out how to make it two digit, so that it will add S01, S02 instead of S1, S2..etc. Any idea?
Case closed. I was thinking of using single digit and double digit to differentiate the ID, eg CourseID and ContactID, however, now I figure out that I can add more than one character in front of the auto number, eg,
Expand|Select|Wrap|Line Numbers
  1.  \CS#  
and
Expand|Select|Wrap|Line Numbers
  1. \CT#
. Thanks.
Sep 19 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.