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

[HowHow]

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?

[JConsulting]

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.

Function UpdateCounter() As String
Dim myVal
myVal = DMax("ID", "tblTest")
UpdateCounter = "Test" & myVal
End Function
 

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

[HowHow]

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.

[JConsulting]

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

[HowHow]

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?

[HowHow]

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,

 \CS#  

and

\CT#

. Thanks.