Unique field/primary key for Access

[R.McCarty9793]

I need to create a field in Access that included both text and the autonumber feature. We are a non-profit hospital organization and we have multiple locations so we want to create an ID field that is unique by location and still uses the auto numbering feature to generate the ID field. I think we need to create this field on the form versus on the table, and we'd make a change in the preceding letters for each location form.

While I'm certain one can accomplish this using a VB formula, but I don't know how to do this. I need the results of the field to look something like the following:

SJ000001
SJ000002
SJ000003 ...
or
SJ000099
SJ000100
SJ000101 ...
etc. etc.

If someone could please assist in how to do this within Access...I'd me most grateful.

[extofer]

This can be accomplioshed with VBA. Basically, if the numbers are numerical, you can use VBA to count the records, add one to the new record, and concantinate (connect) the text string. If you need more help, I'd be glad to assit a non-profit orginization. www.gabrielvilla.com I do pro-bono

[Yomet]

There is another way of doing this without code but it assumes one thing:
- The ID numbers cannot be repeated between locations, i.e. SJ000001 and LA000001 cannot exist.

What you do is use the Access AutoNumber to create the unique ID for each record and for display purposes only you concatenate the location with the ID. The code in the Control source property of the field would look simething like this:
=[Location] & Format([ID], "000000")

However, if you need to have similar numbers across locations, SJ000001 AND LA000001, then you need to run a query to find out how many records there are for the specified location before using the technique posted above to add one to the last number.

If, on top of that, you can delete records for a location and therefore have gaps in the sequence you need to find out what the value of the last number stored is. If that is the case I can send you some code but it will only take up space here if that's not the case.

Hope this helps

Yomet