table column consiting Auto number and suffix based on another colum data

[cinci-hal]

I have a table that consists of plants and the information that goes along with each plant. One of the columns is a lookup for type of plant such as tree, shrub, grass, ground cover, and so on. I would like to create another column for abbreviations so that the beginning of the abbreviation would start with the abbreviation of the plant type (T for tree, S for shrub,...) and then an auto-number afterwords based on the number of that particular plant type that there are the plant table. In other words if there are three plants that are of the type Tree, the third of that type of plant's abbreviation in the abbreviation column would be T3 (even though it might be the 100th plant in the table with plant ID of 100). Is something like that possible and how would I set it up?

[June7]

It is possible. Generating custom unique identifier is a common topic. In your case, options are:

1) VBA to calculate new value and save into field - a common topic

2) only calculate this value on a report, use report Sorting & Grouping features and textbox RunningSum property

[twgonder]

From my short time with Access (I only started with version 1.1, and then took a long hiatus), I see at least two ways to tackle this.

1) Is using DMax, and to review this approach you can check out this link, that I myself had to review recently. The Extended cut video (you have to be a member to view it) does exactly what you are looking for, although you'll have to custom code to your particular situation (using the letter like "T" and where it's coming from).

Custom Sequential Numbers in Microsoft Access (599cd.com)

2) I use a table for to retrieve autonumbers in some situations where Dmax wouldn't be my preferred method (when a record could be deleted, and I don't want to reuse a DMax value.). It's not too complicated. I think my exhaustive VBA code for this, and various permutations for various number types, and a prefix like you want to do are surely possible depending on your coding experience. Here's the first few lines of code comments in the function I use. There are about 90 following lines to get it all done, but you could make a much simpler version for your needs. Reading the helps could give you an idea of the possible scope of your task.

Public Function fGetSeqNum(aUseTbl, aUseFld As String, aUpdtAppID, _
  aTyp As Byte, Optional aPrefx As String = "", _
  Optional aTestSeq As Boolean = True) As Variant
  'Gets the next sequential number to use for fields in tables, uses tblc_SeqId
  'If not found, added starting at 1001 to allow for seed
  ' aTyp: 1 = long, 2 = {proprietary}, 3 = string {proprietary} with prefix and with  {proprietary}
  ' best to have an index for any fields where autonumbers are being added and need to be non duplicate
  ' aPrefx allows for a text prefix
  ' aTestSeq set to false will skip the the test for records using the new Id
  ' aStr set to true will treat the target field as a string instead of a number
  Dim rs As DAO.Recordset, rsT As DAO.Recordset, bSql As String
  ...

[twgonder]

As a bonus for those that are wondering why the OP would want to do what he is doing:
Microsoft Access AutoNumbers Are NOT For You (599cd.com)